Thống kê đọc trang

Cải thiện hiệu năng truy vấn trong SQL Server

Dean Smith

Founder, Atamai Analytics

Trang dữ liệu bảng

Mọi dữ liệu, trong RAM hoặc trên đĩa, được lưu trên các "trang" kích thước 8 KB
Một trang có thể chứa nhiều hàng hoặc một giá trị có thể trải qua nhiều trang
Một trang chỉ thuộc về một bảng
SQL Server làm việc với các trang được đệm trong bộ nhớ
Nếu trang chưa có trong bộ nhớ, nó sẽ được đọc từ đĩa và đệm vào bộ nhớ

Khách hàng: trang dữ liệu

Trang 1

| ALFKI | Alfreds Futterkiste | Maria Anders | Sales Representative | … 
| ANATR | Ana Trujillo Emparedados y helados | Ana Trujillo | Owner | … 
| ANTON | Antonio Moreno Taqueria | Antonio Moreno | Owner | … 
| AROUT | Around the Horn | Thomas Hardy | Sales Representative | … 
| BERGS | Berglunds snabbkop | Christina Berglund | Order Administrator | … 
| BLAUS | Blauer See Delikatessen | Hanna Moos | Sales Representative | … 
| BLONP | Blondesddsl pere et fils | Frederique Citeaux | Marketing Manager | …

Trang 2

| BOLID | Bolido Comidas preparadas | Martin Sommer | Owner | … 
| BONAP | Bon app  | Laurence Lebihan | Owner | … 
| BOTTM | Bottom-Dollar Markets | Elizabeth Lincoln | Accounting Manager | … 
| BSBEV | B s Beverages | Victoria Ashworth | Sales Representative | … 
| CACTU | Cactus Comidas para llevar | Patricio Simpson | Sales Agent | … 
| CENTC | Centro comercial Moctezuma | Francisco Chang | Marketing Manager | … 
| CHOPS | Chop-suey Chinese | Yang Wang | Owner | …

Trang 3

| COMMI | Comercio Mineiro | Pedro Afonso | Sales Associate | … 
| CONSH | Consolidated Holdings | Elizabeth Brown | Sales Representative | … 
| DRACD | Drachenblut Delikatessen | Sven Ottlieb | Order Administrator | … 
| DUMON | Du monde entier | Janine Labrune | Owner | … 
| EASTC | Eastern Connection | Ann Devon | Sales Agent | … 
| ERNSH | Ernst Handel | Roland Mendel | Sales Manager | … 
| FAMIA | Familia Arquibaldo | Aria Cruz | Marketing Assistant | …

Trang 4

| FISSA | FISSA Fabrica Inter. Salchichas S.A. | Diego Roel | Accounting Manager | … 
| FOLIG | Folies gourmandes | Martine Rance | Assistant Sales Agent | … 
| FOLKO | Folk och fa HB | Maria Larsson | Owner | … 
| FRANK | Frankenversand | Peter Franken | Marketing Manager | … 
| FRANR | France restauration | Carine Schmitt | Marketing Manager | … 
| FRANS | Franchi S.p.A. | Paolo Accorti | Sales Representative | … 
| FURIB | Furia Bacalhau e Frutos do Mar | Lino Rodriguez | Sales Manager | …

STATISTICS IO trong SSMS

SET STATISTICS IO ON

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

(88 hàng bị ảnh hưởng)
Table 'Worktable'. Scan count 1, logical reads 104568, physical reads 0, read-ahead reads 0, lob logical reads 0, ...
Table 'Cities'. Scan count 2, logical reads 548, physical reads 0, read-ahead reads 281, lob logical reads 0, lob physical reads 0, ...
Table 'Nations'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, ...

Logical reads

(88 hàng bị ảnh hưởng)
Table 'Cities'. ... , logical reads 548, ... , ...
Table 'Nations'. ... , logical reads 3, ... , ...

logical reads: số trang 8 kilobyte được đọc theo từng bảng

Ví dụ: truy vấn 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) 
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop 
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Ví dụ: truy vấn 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop 
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Ví dụ: truy vấn 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop -- 2nd sub-query
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Ví dụ: truy vấn 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop -- 2nd sub-query
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop -- 3rd sub-query
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Ví dụ: truy vấn 1

SET STATISTICS IO ON

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop -- 2nd sub-query
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop -- 3rd sub-query
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Table 'Earthquakes'. ..., logical reads 54, ...
'Nations'. ..., logical reads 3, ...

Ví dụ: truy vấn 2

SELECT n.UNStatisticalRegion, 
       n.CountryName,
       n.Capital,
       MAX(e.Magnitude)
FROM Nations n
INNER JOIN Earthquakes e
     ON n.Capital = e.NearestPop 
         AND n.Code2 = e.Country
WHERE e.Magnitude >=7.5
GROUP BY n.UNStatisticalRegion, 
        n.CountryName,
        n.Capital

Ví dụ: truy vấn 2

SELECT n.UNStatisticalRegion, 
       n.CountryName,
       n.Capital,
       MAX(e.Magnitude)
FROM Nations n
INNER JOIN Earthquakes e
     ON n.Capital = e.NearestPop 
         AND n.Code2 = e.Country
WHERE e.Magnitude >=7.5
GROUP BY n.UNStatisticalRegion, 
        n.CountryName,
        n.Capital

SET STATISTICS IO OFF

Table 'Earthquakes'. ..., logical reads 18, ...
'Nations'. ..., logical reads 3, ...

So sánh truy vấn

Truy vấn đầu có ba truy vấn con

Table 'Earthquakes'. ..., logical reads 54, ...
'Nations'. ..., logical reads 3, ...

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

Truy vấn thứ hai dùng INNER JOIN

Table 'Earthquakes'. ..., logical reads 18, ...
'Nations'. ..., logical reads 3, ...

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

Ayo berlatih!

Cải thiện hiệu năng truy vấn trong SQL Server