Paginagebaseerde leesstatistieken

De queryprestaties verbeteren in SQL Server

Dean Smith

Founder, Atamai Analytics

Tabeldatapagina’s

Alle data, in geheugen of op schijf, staat in pagina’s van 8 kilobyte
Eén pagina kan veel rijen bevatten, of één waarde kan meerdere pagina’s beslaan
Een pagina hoort bij maar één tabel
SQL Server werkt met pagina’s in cache in het geheugen
Staat een pagina niet in cache, dan wordt die van schijf gelezen en gecachet

Klanten: datapagina’s

Pagina 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 | …

Pagina 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 | …

Pagina 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 | …

Pagina 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 in 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 rows affected)
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 rows affected)
Table 'Cities'. ... , logical reads 548, ... , ...
Table 'Nations'. ... , logical reads 3, ... , ...

logical reads: aantal gelezen pagina’s van 8 kilobyte per tabel

Voorbeeld: query 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);

Voorbeeld: query 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);

Voorbeeld: query 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);

Voorbeeld: query 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);

Voorbeeld: query 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, ...

Voorbeeld: query 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

Voorbeeld: query 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, ...

Queries vergelijken

Eerste query met drie subquery’s

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

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

Tweede query met een INNER JOIN

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

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

Laten we oefenen!

De queryprestaties verbeteren in SQL Server