SqlServer 2000 vs. 2005, hastighetsforskjeller

[hightow]

Opplever at det er betraktelig forskjell på hastighet ved kjøring av en query som har funksjonen MIN()

 

Her er spørringen.

SELECT MIN(t1.number) + 1
FROM mytable t1
WHERE number >= 1000
AND NOT EXISTS (SELECT t2.number FROM mytable t2 WHERE t2.number = t1.number + 1)

Hensikten med spørringen er å finne neste ledige nummer som ikke er i bruk fra et vist startnummer (i dette tilfellet 1000).

 

Denne spørringen gir betraktelig bedre hastighet på 2005 enn på 2000.

Dersom jeg endrer spørringen til å bruke TOP 1 med en ORDER BY (som skal gi samme resultat som MIN i dette tilfeller) får jeg like god hastighet på 2000 og 2005.

 

Noen som vet hvorfor denne hastighetsforskjellen ved bruk av MIN() på 2000 og 2005 ?

Og hvorfor forskjell på hastighet på MIN og TOP 1 på 2000?

[blackbrrd]

Optimizeren i 2000 og 2005 er tydligvis ikke like (ikke så veldig overraskende). Spørringen blir derfor utført forskjellig, noe som gir forskjellig hastighet.

[hightow]

Takk for (generelt) svar

 

Siste spørsmål består.

[blackbrrd]

Mest sannsynlig fordi SQL Server 2000 ikke klarer å ta i bruk en indeks som den klarer å bruke på SQL Server 2005.

[kaffenils]

Opplever at det er betraktelig forskjell på hastighet ved kjøring av en query som har funksjonen MIN()

 

Her er spørringen.

SELECT MIN(t1.number) + 1
FROM mytable t1
WHERE number >= 1000
AND NOT EXISTS (SELECT t2.number FROM mytable t2 WHERE t2.number = t1.number + 1)

Hensikten med spørringen er å finne neste ledige nummer som ikke er i bruk fra et vist startnummer (i dette tilfellet 1000).

 

Denne spørringen gir betraktelig bedre hastighet på 2005 enn på 2000.

Dersom jeg endrer spørringen til å bruke TOP 1 med en ORDER BY (som skal gi samme resultat som MIN i dette tilfeller) får jeg like god hastighet på 2000 og 2005.

 

Noen som vet hvorfor denne hastighetsforskjellen ved bruk av MIN() på 2000 og 2005 ?

Og hvorfor forskjell på hastighet på MIN og TOP 1 på 2000?

Tror ikke dette har noe med MIN funksjonen å gjøre. Fjerner du MIN og lar t1.number være igjen så vil spørringen ta like lang tid. MIN() trenger tross alt hele datasettet for å kalkulere verdien. Å bruke TOP vil jo ikke gi deg riktig resultat. Mulig det gjør det i ditt tilfelle fordi kolonnen number er en clustered index (data er fysisk sortert basert på en clustered index).

 

Det jeg antar, som blackbrrd sier, er at query optimizeren er kraftig forbedret i 2005.

Jeg gjetter at 2000 vil utføre en RBAR (row by agonizing row, det motsatte av set based) kjøring av spørring pga t2.number = t1.number + 1 i sub-spørringen. Dvs at for hver rad i den ytre spørring så kjøres den indre spørringen. SQL Server 2005 vil sannsynligvis klare å løse dette på en set basert måte.

 

Sjekk execution plans, og prøv å kjør SET STATISTION IO ON før du kjører spørringen.

[blackbrrd]

Kaffenils: han sa han kjører TOP 1 med en ORDER BY, som vil hente ut riktig verdi, samme hva slags indekser som finnes/hvordan dataene ligger på disken.

 

Så litt på spørringen nå, skal den hente ut den minste verdien i tabellen som ikke har en verdi som er en større enn seg selv? Hva skal du med de dataene?

[hightow]

Hva jeg skal med dataene er irrelevant.

Jeg skal hente det neste ledige nummeret i tabellen, med utgangspunkt i et referansenummer. Jeg skal mao. ikke hente MAX, ettersom nummerserien i tabellen ikke er sammenhengende.

 

Gitt en tabell som har poster med number i f.eks intervallene 1000-1400, 1450-1631, 1742-1835. Dersom aktiv post har number 1467, så skal jeg finne det neste ledige som da er 1632.

 

(number-feltet i tabellen er indeksert, men har som sagt ingen betydning av resultatet)

[kaffenils]

Kaffenils: han sa han kjører TOP 1 med en ORDER BY, som vil hente ut riktig verdi, samme hva slags indekser som finnes/hvordan dataene ligger på disken.

 

Så litt på spørringen nå, skal den hente ut den minste verdien i tabellen som ikke har en verdi som er en større enn seg selv? Hva skal du med de dataene?

Så ikke at han skrev ORDER BY

 

Har testet begge spørringene selv på en SS2005 installasjon, hvor number er indexert (non-clustered). Som jeg sa så vil bruk av MIN medføre at hele datasettet må genereres før MIN-aggregering kan kalkuleres. Avhengig av størrelsen på datasettet så vil SQL2005 enten generere in-memory hash tabeller av input og gjøre en hash match på disse, eller så vil input tabellene sorteres og det kjøres en merge join.

 

Ved bruk av TOP 1 så vil SQL2005 velge en nested loop. Dvs at for hver rad i ytre spørring så vil det gjøres en index seek i den indre. Dette er effektivt siden antall rader i ytre spørring er relativt få.

 

Konklusjonen er altså at det er stor forskjell på disse spørringene på SQL 2005. Bruk av MIN medfører index scans. Hvorfor SQL 2005 gjør dette så mye mer effektivt enn SQL 2000 er vanskelig å svare på. Det beste vil være om du poster execution plan for 2000 og 2005 for MIN(). Kjør SET SHOWPLAN_XML ON og post xml-strenger du får som output når du kjører spørringene.

[blackbrrd]

Testet de to spørringene i Postgresql:

MIN()

Result  (cost=8.72..8.73 rows=1 width=0)
 InitPlan
   ->  Limit  (cost=0.00..8.72 rows=1 width=4)
         ->  Index Scan using tcontact_pkey on tcontact t1  (cost=0.00..3846255.06 rows=441337 width=4)
               Index Cond: (contactid >= 1000)
               Filter: ((contactid IS NOT NULL) AND (NOT (subplan)))
               SubPlan
                 ->  Index Scan using tcontact_pkey on tcontact t2  (cost=0.00..4.32 rows=1 width=4)
                       Index Cond: (contactid = ($0 + 1))

 

LIMIT 1, ORDER BY

Limit  (cost=0.00..8.72 rows=1 width=4)
 ->  Index Scan using tcontact_pkey on tcontact t1  (cost=0.00..3847358.40 rows=441337 width=4)
       Index Cond: (contactid >= 1000)
       Filter: (NOT (subplan))
       SubPlan
         ->  Index Scan using tcontact_pkey on tcontact t2  (cost=0.00..4.32 rows=1 width=4)
               Index Cond: (contactid = ($0 + 1))

 

Eneste forskjellen er disse to linjene:

Filter: (NOT (subplan))

Filter: ((contactid IS NOT NULL) AND (NOT (subplan)))

 

Hvor den har hevet på en ekstra contactid IS NOT NULL på MIN() spørringen. Ellers har Postgresql kjørt de to spørringene prikk likt, noe som er riktig, resultatet skal jo bli det samme og det finnes nok bare en "kjappeste" måte å kjøre spørringen på.

Endret 28. januar 2009 av blackbrrd

[kaffenils]

Det sa meg lite. For meg ser de helt like ut med unntak av

Result (cost=8.72..8.73 rows=1 width=0)
InitPlan

i første code-block

[blackbrrd]

Det er sikkert fordi jeg ikke klarte å kopiere med første linje.

 

Poenget mitt er som du sa at de er prikk like..

[kaffenils]

Det er sikkert fordi jeg ikke klarte å kopiere med første linje.

 

Poenget mitt er som du sa at de er prikk like..

Og i dette tilfellet burde de være det også. SQL Server 2005 sin query optimizer gjør en dårlig jobb i dette tilfellet