Statistici de timp

Îmbunătățirea performanței interogărilor în SQL Server

Dean Smith

Founder, Atamai Analytics

SQL Server Management Studio

Ecranul de pornire SQL Server Management Studio

STATISTICS TIME în 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 - raportează numărul de milisecunde necesare pentru parsarea, compilarea și executarea unei interogări.

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.

Timpii de execuție SQL Server

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

Timp CPU: durata de procesare a interogării de către procesoare
Timp scurs: durata totală a interogării

Exemplu: interogarea 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);

Exemplu: interogarea 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.

Exemplu: interogarea 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.

Exemplu: interogarea 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.

Compararea interogărilor

Prima interogare conține două subinterogări

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.

A doua interogare utilizează 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.

Timp scurs vs. timp CPU

Timpul scurs

Poate varia la analiza statisticilor de timp
Cea mai bună măsură pentru interogarea cu execuție mai rapidă

Timpul CPU

Ar trebui să varieze puțin la analiza statisticilor de timp
Poate fi o măsură irelevantă dacă procesoarele rulează în paralel

Calcularea mediei

Nu vă bazați pe o singură măsurătoare; calculați media.

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.

Să exersăm!

Îmbunătățirea performanței interogărilor în SQL Server