Tijdstatistieken

De queryprestaties verbeteren in SQL Server

Dean Smith

Founder, Atamai Analytics

SQL Server Management Studio

Opstartscherm van SQL Server Management Studio

STATISTICS TIME in SSMS

SELECT UNStatisticalRegion, 
        CountryName,
        Capital
FROM Nations
WHERE Capital IN 
        (SELECT CityName 
        FROM Cities)
            AND Capital IN 
                (SELECT CityName 
                    FROM Cities 
                    WHERE Pop2017 > 1000000);

STATISTICS TIME - commando dat het aantal milliseconden rapporteert dat nodig is om een query te parsen, compileren en uitvoeren.

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 21 ms.

(88 rows affected)

 SQL Server Execution Times:
   CPU time = 390 ms,  elapsed time = 382 ms.

SQL Server Execution Times

 SQL Server Execution Times:
   CPU time = 390 ms,  elapsed time = 382 ms.

CPU time: tijd die CPU's nodig hebben om de query te verwerken
Elapsed time: totale duur van de query

Voorbeeld: query 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital
FROM Nations
WHERE Capital IN 
        (SELECT CityName -- 1st sub-query
        FROM Cities) 
            AND Capital IN 
                (SELECT CityName -- 2nd sub-query
                 FROM Cities 
                 WHERE Pop2017 > 1000000);

Voorbeeld: query 1

SET STATISTICS TIME ON

SELECT UNStatisticalRegion, 
       CountryName,
       Capital
FROM Nations
WHERE Capital IN 
        (SELECT CityName -- 1st sub-query
        FROM Cities) 
            AND Capital IN 
                (SELECT CityName -- 2nd sub-query
                 FROM Cities 
                 WHERE Pop2017 > 1000000);

SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 381 ms.

Voorbeeld: query 2

SELECT UNStatisticalRegion, 
       CountryName,
       Capital 
FROM Nations n
WHERE EXISTS 
    (SELECT 1 
     FROM  Cities c 
     WHERE n.Capital = c.CityName
        AND Pop2017 > 1000000);

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

Voorbeeld: query 2

SELECT UNStatisticalRegion, 
       CountryName,
       Capital 
FROM Nations n
WHERE EXISTS 
    (SELECT 1 
     FROM  Cities c 
     WHERE n.Capital = c.CityName
        AND Pop2017 > 1000000);

SET STATISTICS TIME OFF

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

Queries vergelijken

Eerste query met twee subquery's

SELECT UNStatisticalRegion, 
       CountryName,
       Capital
FROM Nations
WHERE Capital IN 
        (SELECT CityName 
        FROM Cities)
            AND Capital IN 
                (SELECT CityName 
                 FROM Cities 
                 WHERE Pop2017 > 1000000);

SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 381 ms.

Tweede query met EXISTS

SELECT UNStatisticalRegion, 
       CountryName,
       Capital 
FROM Nations n
WHERE EXISTS 
    (SELECT 1 
     FROM  Cities c 
     WHERE n.Capital = c.CityName
        AND Pop2017 > 1000000);

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

Elapsed time vs. CPU time

Elapsed time

Kan variëren bij het analyseren van querystatistieken
Beste maat voor de snelst draaiende query

CPU time

Zou weinig moeten variëren bij het analyseren van querystatistieken
Mogelijk onbruikbaar als processors parallel draaien

Een gemiddelde nemen

Vertrouw niet op één meting; neem een gemiddelde.

elapsed time = 2032 ms.

elapsed time = 2060 ms.

elapsed time = 1915 ms.

elapsed time = 4009 ms.

elapsed time = 3511 ms.

Average elapsed time = 2705 ms.

Laten we oefenen!

De queryprestaties verbeteren in SQL Server