Statistik waktu

Meningkatkan Kinerja Kueri di SQL Server

Dean Smith

Founder, Atamai Analytics

SQL Server Management Studio

Layar pembuka SQL Server Management Studio

STATISTICS TIME di 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 - perintah yang melaporkan waktu milidetik yang diperlukan untuk parse, compile, dan mengeksekusi kueri.

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: waktu yang digunakan prosesor server untuk memproses kueri
Elapsed time: durasi total kueri

Contoh: kueri 1

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

Contoh: kueri 1

SET STATISTICS TIME ON

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

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

Contoh: kueri 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.

Contoh: kueri 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.

Membandingkan kueri

Kueri pertama dengan dua subkueri

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.

Kueri kedua menggunakan 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

Dapat bervariasi saat menganalisis statistik waktu kueri
Ukuran waktu terbaik untuk kueri tercepat

CPU time

Seharusnya sedikit berubah saat menganalisis statistik waktu kueri
Mungkin kurang berguna jika prosesor server berjalan paralel

Mengambil rata-rata

Jangan bergantung pada satu pengukuran; ambil rata-rata.

elapsed time = 2032 ms.

elapsed time = 2060 ms.

elapsed time = 1915 ms.

elapsed time = 4009 ms.

elapsed time = 3511 ms.

Rata-rata elapsed time = 2705 ms.

Ayo berlatih!

Meningkatkan Kinerja Kueri di SQL Server