Sayfa okuma istatistikleri
SQL Server'da Sorgu Performansını İyileştirme
Dean Smith
Founder, Atamai Analytics
Tablo veri sayfaları
- Tüm veriler, bellek veya diskte, 8 KB boyutunda “sayfalarda” saklanır
- Bir sayfa birçok satır tutabilir; bir değer birden çok sayfaya yayılabilir
- Bir sayfa yalnızca bir tabloya ait olabilir
- SQL Server, belleğe önbelleklenen sayfalarla çalışır
- Bellekte yoksa sayfa diskten okunur ve belleğe alınır
Müşteriler: veri sayfaları
Sayfa 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 | …
Sayfa 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 | …
Sayfa 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 | …
Sayfa 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 | …
SSMS'te STATISTICS IO
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, ...
Mantıksal okumalar
(88 rows affected)
Table 'Cities'. ... , logical reads 548, ... , ...
Table 'Nations'. ... , logical reads 3, ... , ...
logical reads: tablo başına okunan 8 kilobaytlık sayfa sayısı
Örnek: sorgu 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);
Örnek: sorgu 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);
Örnek: sorgu 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);
Örnek: sorgu 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);
Örnek: sorgu 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, ...
Örnek: sorgu 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
Örnek: sorgu 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, ...
Sorguların karşılaştırılması
- Üç alt sorgu içeren ilk sorgu
Table 'Earthquakes'. ..., logical reads 54, ...
'Nations'. ..., logical reads 3, ...
SQL Server parse and compile time:
CPU time = 29 ms, elapsed time = 29 ms.
INNER JOINkullanan ikinci sorgu
Table 'Earthquakes'. ..., logical reads 18, ...
'Nations'. ..., logical reads 3, ...
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
Haydi pratik yapalım!
SQL Server'da Sorgu Performansını İyileştirme
