czwartek, 9 stycznia 2014

Dynamiczny SQL, rekompilacja zapytań i liczność zbioru

Tym razem napiszę… znowu o optymalizacji zapytań do baz danych. Ulubiony temat, szczególnie jak dochodzą do tego jezcze plany wykonania zapytań.

Na początek próbka danych. 2000 rekordów odnoszących się do lat poprzednich i jeden rekord odnoszący sie do roku bieżącego. A wszystko to ma na celu pokazanie w jaki sposób liczność zbioru dancyh może wpływać na to w jaki sposób zapytanie będzie wykonane.
--Clear cache
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC FREESYSTEMCACHE ('All') WITH NO_INFOMSGS
GO
 
USE master
GO
 
SET NOCOUNT ON
GO
 
SET STATISTICS IO ON
GO
 
--Create SQLTest database
IF NOT EXISTS(SELECT * FROM sys.databases WHERE NAME = 'SQLTest')
CREATE DATABASE SQLTest
GO
 
USE SQLTest
GO
 
--Create test table
IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME = 'DynamicSQL')
DROP TABLE DynamicSQL
GO
 
CREATE TABLE DynamicSQL (
    ID INT IDENTITY,
    [DATE] DATE,
    ORDER_NUMBERS INT
)
GO
 
--Declare and initialize dates variables
DECLARE @LASTYEARDATE DATETIME
DECLARE @THISYEARDATE DATETIME
SET @LASTYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE())-1 AS CHAR(4))+'1231' AS DATETIME)
SET @THISYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE()) AS CHAR(4))+'0101' AS DATETIME)
 
--Insert 2000 records for the previous year
INSERT INTO DynamicSQL ([DATE], ORDER_NUMBERS)
SELECT DATEADD(DAY, N.NUMBER * -1, @LASTYEARDATE), ABS(CHECKSUM(NEWID())) % 100 FROM (
    SELECT TOP(2000) NUMBER = ROW_NUMBER() OVER(ORDER BY NUMBER) FROM [master]..spt_values
) N
ORDER BY N.NUMBER DESC
 
--Insert one record for this year
INSERT INTO DynamicSQL ([DATE], ORDER_NUMBERS)
SELECT DATEADD(DAY, N.NUMBER, @THISYEARDATE), ABS(CHECKSUM(NEWID())) % 100 FROM (
    SELECT TOP(1) NUMBER = ROW_NUMBER() OVER(ORDER BY NUMBER) FROM [master]..spt_values
) N
ORDER BY N.NUMBER
 
 --Create indexes
CREATE CLUSTERED INDEX IDX_DynamicSQL ON DynamicSql(ID)
GO
CREATE NONCLUSTERED INDEX IDX_DATE ON DynamicSQL([DATE])
GO

Z wygenerowanego zbioru danych potrzeba wyciągnąć wszystkie rekordy (2000) odnoszące się do lat poprzednich oraz innym zapytaniem rekord z roku bieżącego.

Najpierw ponowna inicjalizacja zmiennych
--Declare and initialize dates variables
DECLARE @LASTYEARDATE DATETIME
DECLARE @THISYEARDATE DATETIME
SET @LASTYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE())-1 AS CHAR(4))+'1231' AS DATETIME)
SET @THISYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE()) AS CHAR(4))+'0101' AS DATETIME)
 
DECLARE @MINDATE DATETIME
DECLARE @MAXDATE DATETIME
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SELECT @MINDATE = MIN([DATE]), @MAXDATE = MAX([DATE]) FROM DYNAMICSQL
W zapytaniach tych nie ma nic skomplikowanego
PRINT 'NO RECOMPILE'
SET @STARTDATE = @MINDATE
SET @ENDDATE = DATEADD(DAY, -1, @THISYEARDATE)
SELECT [DATE], ORDER_NUMBERS FROM DYNAMICSQL WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE
 
SET @STARTDATE = @THISYEARDATE
SET @ENDDATE = @MAXDATE
SELECT [DATE], ORDER_NUMBERS FROM DYNAMICSQL WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE
Po wykonaniu zapytania spoglądając na plan wykonania okazuje się, że oba zostały wykonane dokładnie w taki sam sposób. A dzieje się tak dlatego, że SQL Server stara się wykorzystywać wielokrotnie te same plany wykonania.


Niestety w obu przypadkach skanowany był indeks klastrowy. Nie jest to rezultat oczekiwany, bo na kolumnie DATE jest indeks nieklastrowy, który miał pomóc w wyszukiwaniu rekordów.

Jedno jak i drugie zapytanie skutkowało także 7 logicznymi operacjami odczytu.

Jak w takim razie wymusić by SQL Server korzystał z indeksu nieklastrowego, który potęcjalnie powinien być bardziej wydajny?

Opcja nr 1 (wymuszenie rekompilacji):
PRINT 'RECOMPILE'
SET @STARTDATE = @MINDATE
SET @ENDDATE = DATEADD(DAY, -1, @THISYEARDATE)
SELECT [DATE], ORDER_NUMBERS FROM DYNAMICSQL WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE OPTION(RECOMPILE)
 
SET @STARTDATE = @THISYEARDATE
SET @ENDDATE = @MAXDATE
SELECT [DATE], ORDER_NUMBERS FROM DYNAMICSQL WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE OPTION(RECOMPILE) 
Popatrzmy teraz na plan wykonania


Jupi! Coprawda, plan wygląda na trochę bardziej rozbudowany dla drugiego zapytania, ale zapytanie to wymagało tylko 4 logicznych operacji odczytu.

Opcja nr 2 (Dynamiczny SQL - konkatenacja)
PRINT 'DYNAMIC SQL'
SET @STARTDATE = @MINDATE
SET @ENDDATE = DATEADD(DAY, -1, @THISYEARDATE)
DECLARE @SQL NVARCHAR(200)
SET @SQL = N'SELECT [DATE], ORDER_NUMBERS FROM DynamicSQL WHERE [DATE] BETWEEN ''' + CONVERT(VARCHAR(8), @STARTDATE, 112) + ''' AND ''' + CONVERT(VARCHAR(8), @ENDDATE, 112) + ''''
EXEC (@SQL)
 
SET @STARTDATE = @THISYEARDATE
SET @ENDDATE = @MAXDATE
SET @SQL = N'SELECT [DATE], ORDER_NUMBERS FROM DynamicSQL WHERE [DATE] BETWEEN ''' + CONVERT(VARCHAR(8), @STARTDATE, 112) + ''' AND ''' + CONVERT(VARCHAR(8), @ENDDATE, 112) + ''''
EXEC (@SQL)


Ponownie wykorzystany został indeks nieklastrowy w drugim zapytaniu. Statystyki IO tak jak w poprzednim przypadku.

Opcja nr 3 (Dynamiczny SQL - parametryzowany)
PRINT 'DYNAMIC SQL 2'
SET @STARTDATE = @MINDATE
SET @ENDDATE = DATEADD(DAY, -1, @THISYEARDATE)
SET @SQL = N'SELECT [DATE], ORDER_NUMBERS FROM DynamicSQL WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE'
EXEC SP_EXECUTESQL @SQL, N'@STARTDATE DATETIME, @ENDDATE DATETIME', @STARTDATE = @STARTDATE, @ENDDATE = @ENDDATE
 
SET @STARTDATE = @THISYEARDATE
SET @ENDDATE = @MAXDATE
SET @SQL = N'SELECT [DATE], ORDER_NUMBERS FROM DynamicSQL WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE'
EXEC SP_EXECUTESQL @SQL, N'@STARTDATE DATETIME, @ENDDATE DATETIME', @STARTDATE = @STARTDATE, @ENDDATE = @ENDDATE


Rezultat taki sam jak na samym poczatku. Dzieję się tak dlatego, że plan oba zapytania są wykonane przy uzyciu tego samego planu, który został wcześniej dodany do cachu.

Opcja nr 4 (Manualne wymuszenie wykorzystania indeksu nieklastrowego)
PRINT 'INDEX'
SET @STARTDATE = @MINDATE
SET @ENDDATE = DATEADD(DAY, -1, @THISYEARDATE)
SELECT [DATE], ORDER_NUMBERS FROM DYNAMICSQL WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE
 
SET @STARTDATE = @THISYEARDATE
SET @ENDDATE = @MAXDATE
SELECT [DATE], ORDER_NUMBERS FROM DYNAMICSQL WITH (INDEX (IDX_DATE)) WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE

Rezultat taki sam jak w przypadku rekompilacji zapytania.

Osobiście opcji 5 bym w tym przypadku nie wykorzystywał. W końcu nigdy nie wiadomo jaka długa będzie żywotność tego indeksu. Jeżeli w jakiś sposób byłby on usunięty to wtedy zapytanie zwróci błąd w czasie wykonywania.

Pozostałe przypadki ciągle będą działać.

Prześlij komentarz