[MSSQL] Index voor "order by"?

[mOrPhie]

Stel:

Ik heb een query. Die query is nogal uitgebreid. Nu wil ik die query gaan order'en op 2 integer velden. Bijvoorbeeld:

code:

order by veld1+veld2 desc

Nu is het zo dat je query daardoor een stuk langzamer wordt als je behoorlijk wat rijen in je table hebt staan. Kan ik nu met behulp van een index de order by sneller krijgen? Zo ja, hoe? Met welke instellingen zou dat het beste gaan?

Ik heb voorheen indexes alleen gebruikt voor mijn where-statements. Ik gebruik mssql server 2000.

[raptorix]

Je zou es op die kolommen een index kunnen aanmaken, maar beter is vaak nog om het aantal rijen terug te brengen door een TOP select.

[mOrPhie]

Het indexeren van de kolommen die in de "order by" worden gebruikt heeft geen zin zoals ik het nu zie. Iets order'en maakt namelijk niet dat er en "table scan" uitgevoerd wordt. Maar een "sort". Dit als ik kijk in mijn Execution plan. Daarom vroeg ik me dus af of ik een bepaald _soort_ index moet maken?

[PjotrP]

Indexeren kan zeker wel zin hebben:
als je indexeert op de significantste integer (idg dus de integer die gemiddeld de hoogste waarde heeft), kan MSSQL al heel wat voorsorteerwerk verrichten, iets wat je zeker terug zult zien in snellere sorteertijden.

[whoami]

Op woensdag 20 februari 2002 15:16 schreef mOrPhie het volgende:
Stel:

Kan ik nu met behulp van een index de order by sneller krijgen? Zo ja, hoe? Met welke instellingen zou dat het beste gaan?

Ja, CREATE INDEX ... ON ...

[bigtree]

Aangezien je telkens 2 waarden uit hetzelfde record optelt, zou ik absoluut een index maken waar (alleen) die 2 velden inzitten. Zeker bij grote aantallen records gaat je database hier echt van *vlammen*.

[Verwijderd]

Wat je zou kunnen doen is een extra veld in je tabel opnemen en dat een z.g. computed field maken, waarbij de waarde van dat veld wordt berekend uit andere velden in het record. Die waarde is dan al beschikbaar bij het lezen van de tabel. Daar dan nog een index op definieren kan sneller werken, dus je order by is dan zeker sneller.

[mOrPhie]

Otis en PjotrP zijn de enige die mij snappen.

Alleen Otis zijn oplossing is een hele creatieve oplossing en was ik zelf ook al mee aan het klooien geweest, maar is niet dé manier.

En PjotrP. Hoe gaat mijn index in T-SQL eruit zien dan als ik de significantste eruit wil trekken?

Voor de rest:
Ik weet wel hoe ik indexes maak en weet ook hoe ik ermee om moet gaan. Ik weet alleen niet of een index een ORDER BY sneller zal maken

[The - DDD]

Op woensdag 20 februari 2002 22:15 schreef mOrPhie het volgende:

Voor de rest:
Ik weet wel hoe ik indexes maak en weet ook hoe ik ermee om moet gaan. Ik weet alleen niet of een index een ORDER BY sneller zal maken

Feit dat je niet eens een opmerking maakt over clustered en nonclustered indexes (is SQL2K specifiek) zegt al wel genoeg over je kennis wat betreft indexes onder MSSQL. Ga vooral een clustered index gebruiken als je ook nonclustered indexes hebt. (je kan maar 1 clustered index hebben, indien niet gedefinieerd is dit altijd je primary key)

Verder is het totale onzin om te proberen je data te sorteren op twee integer waarden middels een index. Zeker als je ze in 1 tabel krijgt middels een (voor zover ik begrijp) table join.

Order by is een ordening van je gegevens. Mocht je 1 of meer waarden uit 1!! tabel willen orderen, dan zou je eventueel op die gegevens een index kunnen zetten om de ordening te versnellen. Vraag is alleen of het echt netto effect zal hebben. SQL2K laat trouwens behoorlijk snel een index zitten en valt terug naar table scans.

[whoami]

Op woensdag 20 februari 2002 22:15 schreef mOrPhie het volgende:
Ik weet wel hoe ik indexes maak en weet ook hoe ik ermee om moet gaan. Ik weet alleen niet of een index een ORDER BY sneller zal maken

Ja.

Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses. Without an index, each of these operations will require a table scan of your table, potentially hurting performance.

Keep in mind the word "considered". An index created to support the speed of a particular query may not be the best index for another query on the same table. Sometimes you have to balance indexes to attain acceptable performance on all the various queries that are run against a table. [6.5, 7.0, 2000] Updated 12-7-2001

_{www.sql-server-performance.com}

[The - DDD]

Bij order by's op gejoinde gegevens boeien indexes geen ruk. SQL2K laat ze fijn links liggen.

Beste kun je kijken in de query analyzer en dan het estimated query plan bekijken, als je die goed interpreteerd zie je zo wat het knelpunt is van je query.

Punt met een order by is dat er gesorteerd moet worden, en sorteren is dus 1 van de duurste operaties die je kan uitvoeren op een computer. Tevens neemt de complexiteit exponentieel toe met het aantal te sorteren waarden. (weet niet de exacte vergelijking hiervoor)

[mOrPhie]

Op woensdag 20 februari 2002 22:32 schreef The - DDD het volgende:

Feit dat je niet eens een opmerking maakt over clustered en nonclustered indexes (is SQL2K specifiek) zegt al wel genoeg over je kennis wat betreft indexes onder MSSQL.

Bedankt voor je heldere input. Ben blij dat er mensen zijn die beter zijn in het over mijn kennis ordelen dan ikzelf, aan de hand van 2 berichtjes in een forum

Verder is het totale onzin om te proberen je data te sorteren op twee integer waarden middels een index. Zeker als je ze in 1 tabel krijgt middels een (voor zover ik begrijp) table join.

Totale onzin? Dit kan in sommige gevallen juist nodig zijn. In een aantal queries die ik maak is het weldegelijk nodig. Stel dat je een tabel hebt met een veld "omzet" en een veld "afzet". En je wilt sorteren op de totale winst. Je raad het al.

Verder moet ik erbij zeggen dat ik geen joins of iets gebruik. Het is een recht toe recht an query.

Order by is een ordening van je gegevens. Mocht je 1 of meer waarden uit 1!! tabel willen orderen, dan zou je eventueel op die gegevens een index kunnen zetten om de ordening te versnellen. Vraag is alleen of het echt netto effect zal hebben. SQL2K laat trouwens behoorlijk snel een index zitten en valt terug naar table scans.

Ik heb een descade gesorteerde index gemaakt op die 2 kolommen en ik kreeg een snelheidswinst van 0 komma o. Zelf heb ik nog geen problemen gehad met SQL2K en het laten zitten van indexes. Of SQL2K een index neemt of niet komt puur door het goed indelen van je indexes en de juiste indexes gebruiken.

Ik begrijp dat je me wilt helpen, maar kan dit de volgende keer op een leukere manier? Over kennis van SQL valt te twisten he, zeker aan de hand van 2 berichten!

Ik ben nu wel overtuigd dat een order by niet erg veel sneller te maken is met een index, het is jammer, maar het zij zo.

tnx for the replies in ieder geval.

[Verwijderd]

Oh, natuurlijk, wanneer je een query hebt met joins, dan heb je niets aan computed fields wanneer je waarden van meerdere tables bij elkaar optelt (maar anders wel degelijk, ze zijn nl. daarvoor speciaal gemaakt)

Wat DDD al zegt: bij joins zijn indices niet zo belangrijk, want indices zijn alleen belangrijk voor filtering. Wat je kunt doen is kijken naar het execution plan in Query Analyzer en kijken wat er allemaal wordt gedaan door SQLserver en dus waar de bottlenecks zitten. Profiler er op los laten en kijken waar de meeste tijd in gaat zitten en daar actie op ondernemen.

Nonclustered indices zijn trouwens niet specifiek sqlserver2000.

Verder zou je eventueel naar Views kunnen gaan kijken. SQLServer is geoptimaliseerd voor views (een soort pre-calculated queries), dus je join query kun je als view storen, en dan met een simpele "select * from view order by field" kun je de waarden verkrijgen. field is dan uiteraard je berekening die je in je view stopt als veld.

[mOrPhie]

Op donderdag 21 februari 2002 09:29 schreef Otis het volgende:
...en dan met een simpele "select * from view order by field" kun je de waarden verkrijgen. field is dan uiteraard je berekening die je in je view stopt als veld.

Dat heb ik ook geprobeerd. Maar het schijnt voor de berekening niet zoveel uit te maken of je die doet in je order by of dat je die in je select zet. Wellicht zal dit in een view anders zijn? Kvraag het me af, maar ik ga het vol goede moed proberen

[raptorix]

Op woensdag 20 februari 2002 22:32 schreef The - DDD het volgende:

[..]

Feit dat je niet eens een opmerking maakt over clustered en nonclustered indexes (is SQL2K specifiek) zegt al wel genoeg over je kennis wat betreft indexes onder MSSQL. Ga vooral een clustered index gebruiken als je ook nonclustered indexes hebt. (je kan maar 1 clustered index hebben, indien niet gedefinieerd is dit altijd je primary key)

Uhm dat had je ook al in sqlserver 7

[raptorix]

Dat van die Views van Otis is idd denk ik de beste manier, volgens mij kan je ook indices op views maken in sqlserver 2k.

[mOrPhie]

Op donderdag 21 februari 2002 10:10 schreef raptorix het volgende:
Dat van die Views van Otis is idd denk ik de beste manier, volgens mij kan je ook indices op views maken in sqlserver 2k.

Nope. Kan niet...

[Verwijderd]

Op donderdag 21 februari 2002 10:25 schreef mOrPhie het volgende:

[Indexes op views]

Nope. Kan niet...

Err echt wel. Books online, tik in de index: 'Views'
open 'Indexes on' en voila.

[mOrPhie]

Op donderdag 21 februari 2002 10:59 schreef Otis het volgende:

[..]

Err echt wel. Books online, tik in de index: 'Views'
open 'Indexes on' en voila.

Ow? twas mij nog nooit gelukt ga er ff naar kijken. Tnx voor de verbetering.

[Verwijderd]

Op donderdag 21 februari 2002 11:20 schreef mOrPhie het volgende:

[..]

Ow? twas mij nog nooit gelukt ga er ff naar kijken. Tnx voor de verbetering.

Hoge TPC benchmark scores worden bereikt door o.a. partitioned views. Daar geen indices op definieren levert je nooit een hoge TPC benchmark op. SQLserver staat overal bovenaan, dus dieheeft die dingen echt wel . Die benchmarks zijn ook een reden waarom partitioned views zo snel zijn (of uberhaupt views). Ze zijn retehandig en leveren veel winst op: in dataretrieval maar ook in stored proc/query executiontijd, omdat views pre-compiled zijn. datastorage is wat trager, ivm de view update.

[Killemov]

Hey Otis, zo'n computed field opnemen in je DB is 80% van de gevallen een database nono. (Waarom 80%? Ik ben aanhanger van de 80/20 regel!)

[Verwijderd]

Op donderdag 21 februari 2002 12:11 schreef Killemov het volgende:
Hey Otis, zo'n computed field opnemen in je DB is 80% van de gevallen een database nono. (Waarom 80%? Ik ben aanhanger van de 80/20 regel!)

blabla, en waarom dan wel? Het is zeker aan te bevelen als je veel meer retrievals doet van rows dan je insert op een table. We praten hier niet over MySQL troep.

[mOrPhie]

Op donderdag 21 februari 2002 12:03 schreef Otis het volgende:

[..]

Hoge TPC benchmark scores worden bereikt door o.a. partitioned views. Daar geen indices op definieren levert je nooit een hoge TPC benchmark op. SQLserver staat overal bovenaan...

Heb je ook gezien wat voor servers ze gebruiken daaro.
Kijk maar 'ns hier.

Ik dacht nog even van, naja, toch niet zo heel erg zwaar... een PIII 900 Xeon..... Blijkt dat er 272 processoren in de cluster zitten! Damn...

[The - DDD]

Is even kijken....


Ik heb het allemaal een beetje na lopen zoeken. En ik kom tot de volgende vraag:

Wat zijn de exacte indexes die je gelegd hebt op die ene tabel (zoals je in een bovenstaande reply zei)?

Dit is vooral gericht op de primary key, de unique kolommen en zelf gedefinieerde indexes. Ben heel erg benieuwd wat de exacte SQL is die je hiervoor gebruikt hebt.

Ik heb het namelijk na lopen zoeken en het kan wel degelijk zin hebben om op je order by kollomen een clustered index te zetten. Netto resultaat heb ik niet kunnen checken, m'n gegevens set was te klein voor een goede meting.