🏆 Рейтинг ІТ-работодателей 2019: уже собрано более 5000 анкет. Оцените свою компанию!
×Закрыть

Что может подстерегать новичков при работе с SQL Server. Часть 2

В отличие от первой статьи, никаких Жозефин, уточек, пьяных альпинистов с комплексом «трёхлитровки» и прочих веселостей тут уже не будет. И не потому, что количество историй вдруг решило уменьшить свое поголовье. Причина кроется в другом: работа с базой данных — это не всегда сырок моцарелла и смузи после 8-часового дня. Есть много аспектов, на которые нужно обращать внимание при написании запросов на T-SQL.

Views

Кто-то любит представления, кто-то нет. Навязывать мнение не использовать view — себе дороже, но знать про несколько особенностей при работе с ними нужно обязательно.

Создаем тестовую таблицу и view на основе нее:

USE tempdb
GO

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO

CREATE TABLE dbo.tbl (a INT, b INT)
GO
INSERT INTO dbo.tbl VALUES (0, 1)
GO

IF OBJECT_ID('dbo.vw_tbl', 'V') IS NOT NULL
    DROP VIEW dbo.vw_tbl
GO

CREATE VIEW dbo.vw_tbl
AS
    SELECT * FROM dbo.tbl
GO

SELECT * FROM dbo.vw_tbl
GO

Значения возвращаются правильно:

a           b
----------- -----------
0           1

Теперь добавим новый столбец в таблицу и пробуем опять вычитать данные из view:

ALTER TABLE dbo.tbl
    ADD c INT NOT NULL DEFAULT 2
GO

SELECT * FROM dbo.vw_tbl
GO

Получим тот же результат:

a           b
----------- -----------
0           1

А все потому, что нужно либо явно задавать столбцы, либо рекомпилировать скриптовый объект:

EXEC sys.sp_refreshview @viewname = N'dbo.vw_tbl'
GO

SELECT * FROM dbo.vw_tbl
GO

Чтобы получить правильный результат:

a           b           c
----------- ----------- -----------
0           1           2

При прямом обращении к таблице подобного прикола не будет. Что ж, идем дальше.

Есть любители в одном запросе соединить все данные и обернуть это все в одном view. За примером далеко ходить не будем и посмотрим на «хороший паттерн» из AdventureWorks:

ALTER VIEW HumanResources.vEmployee
AS
    SELECT e.BusinessEntityID
         , p.Title
         , p.FirstName
         , p.MiddleName
         , p.LastName
         , p.Suffix
         , e.JobTitle
         , pp.PhoneNumber
         , pnt.[Name] AS PhoneNumberType
         , ea.EmailAddress
         , p.EmailPromotion
         , a.AddressLine1
         , a.AddressLine2
         , a.City
         , sp.[Name] AS StateProvinceName
         , a.PostalCode
         , cr.[Name] AS CountryRegionName
         , p.AdditionalContactInfo
    FROM HumanResources.Employee e
    JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
    JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = e.BusinessEntityID
    JOIN Person.[Address] a ON a.AddressID = bea.AddressID
    JOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID
    JOIN Person.CountryRegion cr ON cr.CountryRegionCode = sp.CountryRegionCode
    LEFT JOIN Person.PersonPhone pp ON pp.BusinessEntityID = p.BusinessEntityID
    LEFT JOIN Person.PhoneNumberType pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID
    LEFT JOIN Person.EmailAddress ea ON p.BusinessEntityID = ea.BusinessEntityID

А теперь вопрос: что, если мне нужно получить не всю информацию, а только ее часть? Например, вернуть имя и фамилию работников:

SELECT BusinessEntityID
     , FirstName
     , LastName
FROM HumanResources.vEmployee

SELECT p.BusinessEntityID
     , p.FirstName
     , p.LastName
FROM Person.Person p
WHERE p.BusinessEntityID IN (
        SELECT e.BusinessEntityID
        FROM HumanResources.Employee e
    )

Посмотрим на план выполнения в случае использования view:

Table 'EmailAddress'. Scan count 290, logical reads 640, ...
Table 'PersonPhone'. Scan count 290, logical reads 636, ...
Table 'BusinessEntityAddress'. Scan count 290, logical reads 636, ...
Table 'Person'. Scan count 0, logical reads 897, ...
Table 'Employee'. Scan count 1, logical reads 2, ...

И сравним с запросом, который мы осмысленно написали ручками:

Table 'Person'. Scan count 0, logical reads 897, ...
Table 'Employee'. Scan count 1, logical reads 2, ...

Оптимизатор в SQL Server сделали весьма умным и на этапе simplification, при построении плана выполнения он умеет отбрасывать неиспользуемые соединения. Однако эффективно делать он может это не всегда. Иногда ему мешает отсутствие валидного внешнего ключа между таблицами, когда нет возможности проверить, повлияет ли соединение на результат выборки. Или, например, когда соединение идет по более чем одному полю. Ну не умеет некоторых вещей оптимизатор, но это же не повод нагружать его лишней работой.

Code style

Стиль написания кода — это строго индивидуальное, но, чтобы не вносить хаос в разработку, все уже давно придерживаются тех или иных правил. Самое парадоксальное, что за все время работы я не видел ни одного вменяемого свода правил при написании запросов. Все их пишут по принципу: «главное, чтобы работало». Хотя потом рискуют хорошо хлебнуть при разворачивании базы на сервере клиента.

Давайте создадим отдельную базу и таблицу в ней:

USE [master]
GO

IF DB_ID('test') IS NOT NULL BEGIN
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE test
END
GO

CREATE DATABASE test COLLATE Latin1_General_CI_AS
GO

USE test
GO

CREATE TABLE dbo.Employee (EmployeeID INT PRIMARY KEY)
GO

На время наденем погоны Junior Developer и напишем такой запрос:

select employeeid from employee

Работает? Бесспорно, ведь все на уровне букваря. А теперь попробуйте поменять COLLATE на какой-нибудь регистрозависимый:

ALTER DATABASE test COLLATE Latin1_General_CS_AI

И попробуем повторно проверить нашу удачу:

Msg 208, Level 16, State 1, Line 19
Invalid object name 'employee'.

Оптимизатор использует правила текущего COLLATE при построении плана выполнения. Точнее, на этапе связывания, когда производится проверка на существование таблиц, колонок и других объектов и сопоставление каждого объекта синтаксического дерева с реальным объектом системного каталога.

Если хочется писать запросы, которые будут везде работать, то нужно всегда придерживаться правильного регистра в именах объектов, которые используются в запросе.

Column order

На чем еще спотыкаются — задание в ORDER BY порядкового номера столбца. Эта штука весьма коварная, но не такая хитрая, как предыдущий пример:

USE tempdb
GO

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO

CREATE TABLE dbo.tbl (a INT, b INT)
GO
INSERT INTO dbo.tbl VALUES (1, 99), (3, 4)
GO

IF OBJECT_ID('dbo.GetLastRecord') IS NOT NULL
    DROP PROCEDURE dbo.GetLastRecord
GO

CREATE PROCEDURE dbo.GetLastRecord
AS
    SELECT TOP(1) *
    FROM dbo.tbl
    ORDER BY 1 DESC
GO

EXEC dbo.GetLastRecord
GO
RecordID    Value
----------- -----------
3           4

В данном коде несколько проблем: явно не указываются столбцы, которые должен возвращать запрос, и сортировка происходит по порядковому номеру.

Вся эта логика может накрыться медным тазом при простой пересоздании таблицы, когда кто-то захочет поменять порядок столбцов:

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO

CREATE TABLE dbo.tbl (b INT, a INT)
GO
INSERT INTO dbo.tbl VALUES (99, 1), (4, 3)
GO

EXEC dbo.GetLastRecord
GO
Value       RecordID
----------- -----------
99          1

Потому что сортировка будет идти уже по другому столбцу. Однако это не самое страшное.

Тяжелее всего отловить ошибку, если меняется порядок столбцов в таблице, при этом вставка в нее происходит без явного указания колонок:

USE AdventureWorks2014
GO

IF OBJECT_ID('dbo.AWBuildVersion2', 'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion2
GO
CREATE TABLE dbo.AWBuildVersion2 (
      InfoID TINYINT PRIMARY KEY
    , VersionDate DATE NOT NULL
    , ModifiedDate DATE NOT NULL
)
GO

INSERT INTO dbo.AWBuildVersion2
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo.AWBuildVersion
GO

Предположим, кто-то опять сделал диверсию и пересоздал таблицу с новым порядком:

IF OBJECT_ID('dbo.AWBuildVersion2', 'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion2
GO
CREATE TABLE dbo.AWBuildVersion2 (
      InfoID TINYINT PRIMARY KEY
    , ModifiedDate DATE NOT NULL
    , VersionDate DATE NOT NULL
)
GO

INSERT INTO dbo.AWBuildVersion2
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo.AWBuildVersion
GO

В этом случае у нас данные будут записываться уже некорректные:

InfoID VersionDate ModifiedDate
------ ----------- ------------
1      2014-02-20  2014-07-08
InfoID VersionDate ModifiedDate
------ ----------- ------------
1      2014-07-08  2014-02-20

Поэтому считается хорошим тоном не лениться и всегда явно указывать столбцы в конструкции INSERT:

INSERT INTO dbo.AWBuildVersion2 (InfoID, VersionDate, ModifiedDate)
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo.AWBuildVersion

Data length

Нужно всегда указывать размерность типа, чтобы не натыкаться на подобного рода грабли:

DECLARE @a DECIMAL
      , @b VARCHAR(10) = '0.1'
      , @c SQL_VARIANT

SELECT @a = @b
     , @c = @a

SELECT @a
     , @c
     , SQL_VARIANT_PROPERTY(@c,'BaseType')
     , SQL_VARIANT_PROPERTY(@c,'Precision')
     , SQL_VARIANT_PROPERTY(@c,'Scale')

В чем суть данной проблемы? Явно не указали размерность типа и вместо дробного значения получаем «вроде целое»:

---- ---- ---------- ----- -----
0    0    decimal    18    0

Со строками все еще веселее:

DECLARE @t1 VARCHAR(MAX) = '123456789_123456789_123456789_123456789_'
DECLARE @t2 VARCHAR = @t1

SELECT LEN(@t1)
     , @t1
     , LEN(@t2)
     , @t2
     , LEN(CONVERT(VARCHAR, @t1))
     , LEN(CAST(@t1 AS VARCHAR))

Если явно не указывается размерность, то у строки длина будет 1 символ:

----- ------------------------------------------ ---- ---- ---- ----
40    123456789_123456789_123456789_123456789_   1    1    30   30

При этом поведение преобразовании типов имеет свою особенность: не указали размерность в CAST/CONVERT, то браться будут первые 30 символов.

Однако это далеко не верх того идиотизма, с которым можно столкнуться при работе со строками. Пробовали склеивать несколько строк в одну?

STRING_CONCAT

Я бы мог посоветовать использовать функцию STRING_CONCAT, если бы она была... На дворе 2016 год, а отдельной функции, чтобы склеивать строки, в SQL Server так и не добавили. Нужно же как-то выходить из положения?

Рассмотрим пару наиболее популярных вариантов, но вначале создадим тестовую таблицу:

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t (i CHAR(1))
INSERT INTO #t
VALUES ('1'), ('2'), ('3')

И начнем с моего «любимца» — конкатенация строк через присваивание значений в переменную:

DECLARE @txt VARCHAR(50) = ''
SELECT @txt += i
FROM #t
--ORDER BY i

SELECT @txt
--------
123

Все работает, но сам MS намекает, что данные способ недокументированный, и никто не застрахован от такого результата:

DECLARE @txt VARCHAR(50) = ''
SELECT @txt += i
FROM #t
ORDER BY LEN(i)

SELECT @txt
--------
3

Скажу честно, сам в первый раз долго разбирался, почему у меня отчет по бухгалтерской проводке только последнюю строку показывает. После этого прикола было много еще чего: CLR, UPDATE, временные таблицы, рекурсия, циклы... И это все чтобы склеить строки.

На практике, в 90% случаев достаточно использовать XML:

SELECT [text()] = i
FROM #t
FOR XML PATH('')
--------
123

Однако и тут нас может поджидать пара нюансов. Во-первых, очень часто необходимо склеить строки в разрезе каких-то данных, а не все в одно:

SELECT t.name
     , STUFF((
            SELECT ', ' + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'
------------------------ ------------------------------------
ScrapReason              ScrapReasonID, Name, ModifiedDate
Shift                    ShiftID, Name, StartTime, EndTime

При этом крайне желательно избегать использования XML метода для парсинга — они очень ресурсоемкие:

Теперь пробуем не использовать value:

SELECT t.name
     , STUFF((
            SELECT ', ' + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH('')), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

И такой вариант будет работать хорошо и быстро, если не одно «но». Попробуйте выполнить вот такой запрос:

SELECT t.name
     , STUFF((
            SELECT ', ' + CHAR(13) + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH('')), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

Если в строках встречаются спецсимволы, вроде табуляции, перевода строки и прочее, то мы будем получать не совсем корректные результаты:

В итоге у нас два варианта: если спецсимволов нет, то использовать вариант запроса без метода value, в противном случае обратить внимание на более ресурсоемкий план.

Subquery

Без лишних прелюдий выполним запрос:

USE AdventureWorks2014
GO

SELECT p.BusinessEntityID
     , (
        SELECT s.SalesQuota
        FROM Sales.SalesPersonQuotaHistory s
        WHERE s.BusinessEntityID = p.BusinessEntityID
      )
FROM Person.Person p

и получим ошибку:

Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Как решаются подобные проблемы? Элементарно — добавляется TOP(1), и проблема ушла. Однако не все так просто, как может показаться. Использование операции TOP(...) заставляет оптимизатор форсировать использование IndexSeek. К таким же последствиям приводит использованием OUTER/CROSS APPLY вместе с TOP.

К примеру, есть запросы:

SELECT p.BusinessEntityID
     , (
        SELECT TOP(1) s.SalesQuota
        FROM Sales.SalesPersonQuotaHistory s
        WHERE s.BusinessEntityID = p.BusinessEntityID
        ORDER BY s.QuotaDate DESC
      )
FROM Person.Person p

SELECT p.BusinessEntityID
     , t.SalesQuota
FROM Person.Person p
OUTER APPLY (
    SELECT TOP(1) s.SalesQuota
    FROM Sales.SalesPersonQuotaHistory s
    WHERE s.BusinessEntityID = p.BusinessEntityID
    ORDER BY s.QuotaDate DESC
) t

У них одна и та же проблема на плане выполнения:

Table 'SalesPersonQuotaHistory'. Scan count 19972, logical reads 39944, ...
Table 'Person'. Scan count 1, logical reads 67, ...

Вооружившись оконной функцией, перепишем запрос :

SELECT p.BusinessEntityID
     , t.SalesQuota
FROM Person.Person p
LEFT JOIN (
    SELECT s.BusinessEntityID
         , s.SalesQuota
         , RowNum = ROW_NUMBER() OVER (PARTITION BY s.BusinessEntityID ORDER BY s.QuotaDate DESC)
    FROM Sales.SalesPersonQuotaHistory s
) t ON p.BusinessEntityID = t.BusinessEntityID AND t.RowNum = 1

И посмотрим что изменилось:

Table 'Person'. Scan count 1, logical reads 67, ...
Table 'SalesPersonQuotaHistory'. Scan count 1, logical reads 4, ...

Думаю, что комментарии излишни. Поэтому перейдем к десерту.

@TableVariable vs #TempTable

Существует извечный холивар относительно того, что лучше использовать: табличные переменные или временные таблицы. Говорят, что первые живут исключительно в оперативной памяти, а вторые хранятся на диске. И поэтому табличные переменные лучше использовать для хранения небольших наборов данных, а временные таблицы — для всего остального. Так ли это?

На самом деле табличные переменные и временные таблицы физически хранятся одинаково — как таблицы в базе tempdb. Исключение составляют InMemory табличные типы.

USE [master]
GO

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'
GO

DECLARE @t TABLE (id INT)

SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'

CREATE TABLE #t (id INT)

SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'
-----------
0

-----------
1

-----------
2

На этом основное сходство заканчивается, и начинаются грабли. Сперва мои самые любимые, которые часто попадаются на собеседованиях Middle/Senior DB Developer:

USE [master]
GO

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

DECLARE @t TABLE (id INT)
CREATE TABLE #t (id INT)

BEGIN TRANSACTION

INSERT INTO @t VALUES (2)
INSERT INTO #t VALUES (2)

ROLLBACK

SELECT COUNT(*) FROM @t
SELECT COUNT(*) FROM #t
-----------
1
-----------
0

Временные таблицы работают по аналогии с обычными таблицами и отражают все изменения в логе, которые мы и откатили с помощью ROLLBACK.

Табличные переменные работают по-другому: можно осторожно сказать, что они не поддерживают пользовательских транзакций и отражают в логе лишь те операции, которые позволяют серверу откатывать данные на этапе модификации данных. Например, в ситуациях нарушается уникальность первичного ключа:

DECLARE @t TABLE (id INT PRIMARY KEY)
INSERT INTO @t VALUES (1)

INSERT INTO @t VALUES (1), (2)

SELECT * FROM @t

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK__#AD25707__3213E83F1AADB62D'. Cannot insert duplicate key in object 'dbo.@t'. The duplicate key value is (1).

id
-----------
1

В чем еще разница? Перечислять много, но мы остановимся на самом главном. Во временных таблицах все по аналогии с обычными таблицами — используется статистика и показывается кардинальность, более или менее адекватная:

USE AdventureWorks2014
GO

IF OBJECT_ID('tempdb.dbo.#SalesOrderDetail') IS NOT NULL
    DROP TABLE #SalesOrderDetail

CREATE TABLE #SalesOrderDetail (ProductID INT PRIMARY KEY)

INSERT INTO #SalesOrderDetail (ProductID)
SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail

SET STATISTICS IO ON

SELECT *
FROM Production.Product p
WHERE p.ProductID IN (SELECT s.ProductID FROM #SalesOrderDetail s)

SET STATISTICS IO OFF

Table 'Product'. Scan count 1, logical reads 15, ...
Table '#SalesOrderDetail_000000000030'. Scan count 1, logical reads 2, ...

У табличных переменных нет статистики, и кардинальность всегда равна единице:

USE AdventureWorks2014
GO

DECLARE @SalesOrderDetail TABLE (ProductID INT PRIMARY KEY)

INSERT INTO @SalesOrderDetail (ProductID)
SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail

SET STATISTICS IO ON

SELECT *
FROM Production.Product p
WHERE p.ProductID IN (SELECT s.ProductID FROM @SalesOrderDetail s)

SET STATISTICS IO OFF
GO

Table 'Product'. Scan count 0, logical reads 532, ...
Table '#A28D5CEC'. Scan count 1, logical reads 2, ...

Именно по этой причине при использовании табличной переменной могут строиться не оптимальные планы выполнения:

Есть еще куча всего по мелочи. Например, использование табличных переменных мешает оптимизатору выбирать параллельные планы выполнения. Также использование временных таблиц приводит к рекомпиляции плана, в то же время табличная переменная таким недостатком не обладает. Можете поверить на слово — это все мелочи по сравнению с неверной оценкой кардинальности при использовании табличных переменных.

А вот лечить данную проблему можно по-разному: использовать временные таблицы либо избирательно добавлять OPTION(RECOMPILE) к проблемным запросам. Хотя постойте, еще стоит упомянуть про trace flag 2453, который можно использовать, начиная с SQL Server 2012 SP2 и SQL Server 2014 CU3.


На этом пока все. Знаю, конечно, что за бортом осталось еще много чего интересного, но все же надеюсь, что этот поток мыслей кому-то будет полезным.

LinkedIn

42 комментария

Подписаться на комментарииОтписаться от комментариев Комментарии могут оставлять только пользователи с подтвержденными аккаунтами.

Типовых цели использовать вьюхи (в теории) — две.
1) Присобачить вьюху к определённому индексу, и подшаманить её под конкретный часто используемый запрос. Но ПРОБЛЕМА в типичных базах состоит в том, что
— нельзя натравить вьюху на один индекс, чаще вообще нельзя даже на все. Используя вьюхи, шансы что оптимизатор промахнётся по индексам растут катастрофически, так ещё и зависят от запрошенных значений данных. Да, сюрпрайз — один и тот же запрос при разных значениях в полях поиска может пойти по разным индексам.
2) Присобачить вьюху к очень малому набору полей, с целью ограничить пользователя вьюхой, не дав ему прав на таблицу. Прекрасно работает! Пока не столкнётся с необходимостью специфического индекса.

Проблема вьюшек в том, что они позволяют ограничить данные, но не взять в себя вместе с запросом его оптимизацию. Так, например, нельзя построить индекс по вьюхе (вернее по таблице, на основе запроса вьюхи). Хотя казалось бы, этой потребности столько же лет, сколько самим вьюхам.

Технически мне непонятна сама разница между представлением и индексом. Ведь по сути-то делают одно и то же. Но представления крайне удобны в запросах тем что они похожи на таблицы. А индексы удобны по скорости работы, и что их оптимизация заложена в них самих по природе. Совмещение того и другого было бы идеальным вариантом.

Хорошая статья! Даёшь 3 часть?)

Увы, но пока материала для продолжения нет. Думал про SQL Server и JSON в ближайшее время написать.

по поводу Subquery.Зачем использовать оконную функцию? Запрос громоздкий получается- вот боллеe компактное, простое и быстрое решение (subquery cost 0.203617 vs 0.215268 ) SELECT p.BusinessEntityID,MAX(s.SalesQuota) AS SalesQuota
FROM Person.Person p
LEFT JOIN Sales.SalesPersonQuotaHistory s ON s.BusinessEntityID = p.BusinessEntityID
GROUP BY p.BusinessEntityID

Но там же нужно вернуть последнее... а это не всегда максимальное значение. Хотя если абстрагироваться от данного примера, то при выборки из большой таблицы описанный Вами подход будет выгодным.

В статье в оконной функции указана сортировка DESK -это означает что запсь с индексом 1 (которую мы в последствии и выбираем) будет максимальной. Поэтому мой вариант и вариант автора статьи выдадут один и тот же результат

Последней.... не максимальной. Вот принципиальная разница между тем что у меня написано и у Вас:

DECLARE @t TABLE (
    BusinessEntityID INT,
    QuotaDate DATE,
    Value INT
)
INSERT INTO @t
VALUES (1, '20160101', 123), (1, '20160202', 23)

SELECT BusinessEntityID, Value
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (
                        PARTITION BY BusinessEntityID
                        ORDER BY QuotaDate DESC
                    )
    FROM @t
) t
WHERE rn = 1

SELECT BusinessEntityID, MAX(Value)
FROM @t
GROUP BY BusinessEntityID

Если данных много, то ROW_NUMBER уже не такой выигрышный вариант и иногда подобный подход будет быстрее работать (опять же при условии правильных индексов):

SELECT t2.BusinessEntityID, t2.[Value]
FROM (
    SELECT BusinessEntityID, QuotaDate = MAX(QuotaDate)
    FROM @t
    GROUP BY BusinessEntityID
) t
JOIN @t t2 ON t.BusinessEntityID = t2.BusinessEntityID
          AND t.QuotaDate = t2.QuotaDate

Ну вообщем согласен- я не внимательно почитал условие- сортируем по одному полю QuotaDate, а возвращаем другое SalesQuota. Поэтому мое первое решение неправильно. То что предложили вы как исправление по плану запроса работает значительно медленнее почти в два раза (это видно только на живой базе, но не видно на табл. переменной) SELECT m.BusinessEntityID,s2.SalesQuota AS SalesQuota
FROM
(SELECT p.BusinessEntityID,MAX(s.QuotaDate) AS QuotaDate
FROM Person.Person p
LEFT JOIN Sales.SalesPersonQuotaHistory s ON s.BusinessEntityID = p.BusinessEntityID
GROUP BY p.BusinessEntityID) AS m
LEFT JOIN Sales.SalesPersonQuotaHistory s2 ON s2.BusinessEntityID = m.BusinessEntityID AND S2.QuotaDate = m.QuotaDate. И индексы Тюнинг эдвазер никакие делать для ускорения не хочет — так что даже не представляю как такой вариант может работать быстрее предложенного в статье Однако(!), даже в таком случаи то что предложил автор статьи можно ускорить(хоть и не намного) но с помощью Агрегированной оконной функции. а не ранжирующей. Вот он: SELECT p.BusinessEntityID
, t.SalesQuota
FROM Person.Person p
LEFT JOIN (
SELECT s.BusinessEntityID
,s.SalesQuota
,MAX(s.QuotaDate) OVER(PARTITION BY s.BusinessEntityID ) AS ’Maxx’
FROM Sales.SalesPersonQuotaHistory s
) t ON p.BusinessEntityID = t.BusinessEntityID AND t.SalesQuota = Maxx разница в выполнении по плану запроса составляет 0.198904 vs 0.209342 тоесть прирост в производительности около 5%

Subquery

Проблема в том что это Correlated SubQuery (от TOP итератора не зависит)
оптимизатор запросов не имеет других путей путей построения плана запроса кроме как через NESTED LOOP JOIN. При этом какой будет для внутреней таблицы seek/scan/spool дело уже второе.
Как результат при большом кол-ве строк во внешнем/управляющем запрос будем иметь деградацию и запрос не масштабируется.
Типичная ошибка нежелающих мыслить множествами.

STRING_CONCAT

Вотчина MSSQL равно как и других RDMS не работа со строками. MS неспешит с этим и впринципе правильно.

Насчет альтернатив то я б добавил
CLR (UDF or even Aggregate)
Quirky update method

Имплементация на .Net простая.
Я например юзаю regex though CLR

Чем вам CONCAT в T-SQL не подходит?

CONCAT не более чем компактный хендлер для конкатенации атрибутов/выражений в одной строке без учета NULL.
В топике же поднимается вопроc о конкатенации значений атрибута/выражения из разных строк.

типа mysql’ного group_concat?
а описание кастомного агрегата не устраивает по производительности?

а описание кастомного агрегата не устраивает по производительности?
это не оно ?
Насчет альтернатив то я б добавил
CLR (UDF or even Aggregate)

По производительности сериализация десериализация строк убивает весь эффект от кастомной имплементации. Нет оно работает быстро, но XML FOR PATH(’’) практически также !
Вот имплементация
groupconcat.codeplex.com/...oupConcat/GROUP_CONCAT.cs
используемая в сравнении sqlperformance.com/...ver-grouped-concatenation

типа mysql’ного group_concat?
Да это оно !
это не оно ?
угу. так как я даже не пересекался с особенностями MSSQL, шо кастомные аггрегаты пишутся на .NET узнал 30 минут назад, то, что раньше писали насчет CLR даже не понял. вот, решил поднять вопрос и понять написанное :)

отдельное спасибище на вторую ссылку, много нового для себя открыл.
PS все ж нативный агрегат помощнее был бы: DISTINCT там, ORDER BY прописать произвольный... Эх.

@TableVariable vs #TempTable
Мой любимый топик ;)

Сергей несмотря на хорошое описание разницы я прокоментирую

1.

Например, использование табличных переменных мешает оптимизатору выбирать параллельные планы выполнения.
Для SELECT DML паралелльный план возможен

2 .

А вот лечить данную проблему можно по-разному: использовать временные таблицы либо избирательно добавлять OPTION(RECOMPILE) к проблемным запросам
Не нужно лечить таким путем. Табличные переменные и временные таблицы намеренно создали такими. Зачем же переделывать то что работает бай дизайн ? Может «свой» совсем не дизайн ?

OPTION RECOMPILE для табличных переменных все равно не даст правильной оценки «Estimated Rows». Например для запросов c предикатом по неравенству вида SELECT <cols> FROM @tbl WHERE Cols кардиналити будет всегда 30% от общего числа строк в таблице. Для предиката икволити также бардак: берется распределение (density) во внимание но без учета значения параметра (читай OPTIMIZE FOR UNKNOWN)
После такой оценки говорить о хорошем Plan candidate не приходится вообще.

Поэтому не цепляете костыли. Дизайнте правильно или рефактор :)

Вотчина табличных переменных
— лог действий в транзакции
— процедуры/функции где рекомпиляция не нужна и набор строк десятки/пара страниц и NESTED LOOP JOIN в плане это ожидаемый итератор

Для всего остального — временные таблицы.

4. DDL TRUNCATE для табличных переменных не поддерживается. Почему это важно ? Недавно рефакторил код, где по ошибке в табличную переменную грузили 1.5 миллиарда строк, ладно бы на этом и все. В конце батча был стейтмент DELETE FROM @tbl .......
Тому скоуп визибилити у них тоже разный, следует указать бы.

1. Я не отрицал что параллельный план невозможен :) поэтому так аккуратно и написал. Где мы точно не получим параллельный план так это при вставке в табличную переменную — этого SQL Server сейчас не умеет (поправьте меня если я не прав, но подобного поведения я нигде еще не видел). В то же время для временных таблиц параллельную вставку он научился делать еще с 2014 RTM, а уже потом это все добавили и в 2012 версии начиная с 2012 SP1 CU10 и 2012 SP2 CU1.

2. Ну скажем так... любое выражение где есть неравенство будет скатываться в 30% при оценке. Поэтому это общий случай, нежели проблема OPTION(RECOMPILE). Относительного того, пихать или нет RECOMPILE — это уже от ситуации зависит.

3. А вот за TRUNCATE и область видимости спасибо. Действительно забыл это включить в примеры.

По поводу п2.

Ну скажем так... любое выражение где есть неравенство будет скатываться в 30% при оценке.
Прошу прощения Сергей. Неожидал. А как же статистика/гистограммы (даже при отсутсвии индексов) ???
Для примера

select TOP 1000 name into #tmp from sys.columns
select count(*) from #tmp where name >’aa’

[url=radikal.ru][img]s020.radikal.ru/.../1610/b1/a2236f2635a9.png[/img][/url]
очень точный план.

Если поменять на TV то estimated rows = 300. Как по книжке.

Относительного того, пихать или нет RECOMPILE — это уже от ситуации зависит.
Вы правда так считаете ?

На какой версии сиквела смотрите? И к слову Вы правы... я не совсем корректно выразился. Имею ввиду такую ситуацию:

IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
    DROP TABLE #tmp
GO

SELECT TOP 1000 name
INTO #tmp
FROM sys.columns

DECLARE @a VARCHAR(10) = 'aa'
SELECT COUNT(*)
FROM #tmp
WHERE name > @a
Вы правда так считаете ?
Если это не OLTP, то почему нет? Конечно, согласен, что прекрасному предела нет... да и к тому же зачем RECOMPILE использовать, когда уже появился TF 2453.

2012 SP3, 2014SP1.

А с переменной объявленной отдельно — имеем тривиальный план. Вот ему то RECOMPILE и поможет ;) План будет такой же как и с явно указанным литералом, чего совсем не будет в случае с TVP.

А вообще тема классная. Спасибо !
Больше комментировать не буду ;)

Вам спасибо :) комменты весьма ценные

В абсолютном большинстве, новички используют PhpMyAdmin

Как бы мы тут про T-SQL ведем повествование... а где он, там и SSMS. Ну может dbForge Studio. Но никак PhpMyAdmin :)

Скорее так. dbForge Studio до тех пор, пока не надо посмотреть планы запросов. А дальше SSMS.

А вот это Вы зря :))) в новой версии dbForge которая скоро должна выйти наконец-то переделали этот функционал. Чёрную тему добавили, много вкусняшек при анализе, когда идёт переоценка или недооценка ожидаемого количества строк. Раньше нужно было на каждый оператор смотреть, а теперь строки разукрашиваются и можно быстро увидеть где проблема.

Да я не спорю. И всеми руками за dbForge. Особенно если сравнивать с SSMS. Но я пользуюсь Express версией. И потому планы запросов только в SSMS.
Пока у меня нет большого объёма сложных задач DBA, 700$ тратить не вижу смысла.

Вообще-то он для украинских девелоперов бесплатный. У самого халявная лицензия на SQL Complete + dbForge Studio. Все это в обмен на периодический фитбек с моей стороны. Вот линк — devart.com/ru/xchange/

А вот за ссылочку спасибо. Пошёл регистрироваться.
У нас есть что сказать. Теперь, надеюсь, будет и в куда.

А словосочетание SQL Server в заголовке вас не насторожило? ;)

Вот такие вот “Web Developer, Team Lead в Pro Code” )))))))))))))))

STRING_CONCAT
...

XML vs Dirty Hack...

А ведь можно было бы использовать рекурсивную CTE. SQL Management Studio пока качается, так что план запроса глянуть не могу. Но, думаю, работать будет быстрее, чем XML. Явно можно ещё допилить, но лень.

WITH
  UColumns
AS
  (SELECT
    Row_number() OVER (PARTITION BY c.object_id ORDER BY c.column_id) as ColumnNum,
    c.object_id,
    c.column_id,
    c.name
  FROM
    sys.columns c
    INNER JOIN
      sys.objects o
      ON
        o.object_id = c.object_id
  WHERE
    o.type = 'U'),
  GrouppedColumns
  (ColumnNum,
  object_id,
  column_id,
  name,
  GrouppedNames)
AS
  (SELECT
    uc.*,
    CAST(uc.name as NVARCHAR(MAX))
  FROM
    UColumns uc
  WHERE
    uc.ColumnNum = 1
  UNION ALL
  SELECT
    ucc.*,
    gc.GrouppedNames + ', ' + ucc.name
  FROM
    UColumns ucc
    INNER JOIN
      GrouppedColumns gc
      ON
        gc.ColumnNum + 1 = ucc.ColumnNum
        AND
        gc.object_id = ucc.object_id),
  SingleGrouppedColumns
AS
  (SELECT
    Row_number() over (PARTITION BY gc.object_id ORDER BY gc.ColumnNum DESC) as Rank,
     gc.*
  FROM
    GrouppedColumns gc)
SELECT
  o.name as Object,
  gc.GrouppedNames as Columns
FROM
  SingleGrouppedColumns gc
  INNER JOIN
    sys.objects o
    ON
      o.object_id = gc.object_id
WHERE
  gc.Rank = 1;

Да и вообще, не знание про существование CTE, HierarchyId и так далее, приводит к появлению таких курсорных монстров, что хочется закрыть ноут, уткнуться в подушку и плакать.

План запроса с CTE будет явно громостким. Увы не могу поддержать Вашего мнения, что этот подход быстрее будет работать. Тут все очень зависит от размера данных.

Хотя... Подобный подход, как Вы привели, когда-то использовал давным давно (посмотрите на самый последний пример... речь о нем): habrahabr.ru/post/200120/

Читав колись порівння різни способів то XML найшвидший. Крім UDF на .NET і то грязний XML без value швидший ніж UDF на .NET якось так для себе запамятав.

Элементарно — добавляется TOP(1), и проблема ушла.
а не уйдет ли вместе с проблемой и часть данных? ведь, насколько я понимаю из ошибки, к каждому BusinessEntityID ставится в соответствие несколько значений SalesQuota и при select TOP(1) вернется только одно значение. Интересно будет переписать этот запрос через WITH и сравнить план с вашим через inline view.
использование * плохо не только во view, но и в хранимых процедурах, функциях — читая такой код не сразу ясно что куда вставляется, но и INSERT INTO tableA SELECT * FROM tableB может работать до тех пор, пока в tableB не добавят еще одну колонку. Или добавят в tableA personID (number(20)), а в tableB deptID (number(20)). код и дальше будет работать, только потом сложнее будет разобраться в чем ошибка (по этой же причине и order by 2,4,1 лучше не использовать)
и при select TOP(1) вернется только одно значение
а как иначе?
select 1, (select 1 union all select 2 from )
какие варианты?

Конечно же часть данных может уйти. В данном примере я преследовал другую цель. Показать что код нужно писать с учётом на возможность изменения зависимостей между таблицами. Например, раньше было один к одному, а потом стало один ко многим.

наверное я не правильно понял, что он имеет в виду. В каком случае подзапрос может возвращать несколько записей, находясь в SELECT-секции? Ну, не гипотетически возвращать, а чтоб в этом был смысл.

ну, тогда у вас ошибка будет в данных (которая проявляется позже и труднее искать причину) — вместо
BusinessEntityID1 SalesQuota1
BusinessEntityID1 SalesQuota2
у вас будет просто BusinessEntityID1 SalesQuota1.
опять же, почему не писать данный запрос через JOIN (c WITH, если надо) в рамках

Показать что код нужно писать с учётом на возможность изменения зависимостей между таблицами.
?

Поддерживаю Ваше мнение, конечно же, проще все сделать через JOIN. Смысл был просто обратить внимание на проблему. Что делать так не нужно... а если и делать то потом не лепить бездумно TOP(1) чтобы заткнуть проблему.

Полностью согласен с граблями со * во вьюхах.
Понравился пример подзапроса с ROW_NUMBER(), надо будет проверить

Табличные переменные работают по-другому: можно осторожно сказать, что они не поддерживают пользовательских транзакций
Это можно использовать как положительное свойство, если самостоятельно логировать выполнение запроса в табличную переменную.

Подписаться на комментарии