Neem contact op
Bel mij terug

Hoeveel verschil kan 1 rij maken in je query?

Door Reitse Eskens, Support Specialist BI

Onlangs werd ik door een blog getriggerd met deze vraag. Ik vraag 1 rij meer op in een query en ineens duurt het veel en veel langer voordat ik resultaat krijg. Is daar een reden voor? Natuurlijk is daar een reden voor, anders zou dit geen leuk blog worden om te lezen.

Startpunt

We beginnen met de volgende query:

select top 100 *
from [STACKOVERFLOW.COM].Posts p
order by p.Id desc

We kijken naar de laatste 100 rijen in een tabel om te zien wat er als laatste is toegevoegd. Geen probleem, doorlooptijd is wat aan de hoge kant met bijna een minuut maar we hebben resultaat:

Dan voeren we een bijna identieke query uit:

select top 101 *
from [STACKOVERFLOW.COM].Posts p
order by p.Id desc

Dat duurt iets langer:

Maar waarom? 1 Record meer in de query en deze heeft 2,5 keer meer tijd nodig.

Het hint naar het antwoord zit verscholen in het zogeheten execution plan. De eerste variant van de top 100:

En het Execution plan van de top 101:

In de variant met de top 101 zie je een andere geel icoontje boven de Sort staan, een uitroepteken.

Daar zit de pijn. Ga je met je muis boven die Sort staan, dan komt er een helptekst naar voren.

Waar zit het verschil?

Bij de top 100 gebeurt dit:

Bij de top 101:

Voor 1 rij wordt er ineens een enorme berg informatie opgehaald en verwerkt. En omdat het teveel is voor de server die er staat kan dat niet meer in-memory afgehandeld worden maar wordt de tempDB erbij gehaald. Dat wil zeggen dat alle data naar disk geschreven wordt, daar ook weer uitgelezen moet worden om daarna getoond te kunnen worden.

Een stapje verder kunnen we kijken naar het select blokje van het query execution plan. Daarin staat het volgende:

En voor de top 101:

Dus voor 1 rij verwacht SQL Server 1.780.792 kb werkgeheugen méér nodig te hebben.

Reden

Wat is nu de reden hiervan? Bij de ontwikkeling van SQL Server is een verschil gemaakt in het sorteren van 100 rijen en meer dan 100 rijen. Voor de 101 worden alle rijen opgehaald, gesorteerd en daarna worden de eerste 101 (of meer) getoond. Bij 100 rijen of minder wordt een alternatief algoritme gebruikt welke veel sneller gaat en minder rijen ophaalt.

Meer informatie

http://sqlblog.com/blogs/paul_white/archive/2010/08/27/sorting-row-goals-and-the-top-100-problem.aspx

https://www.brentozar.com/archive/2017/09/much-can-one-row-change-query-plan-part-1/

(zie commentaar van Geoff Patterson).

Met dank aan Erik Darling voor de inspiratie voor dit blog.