Hva er kjappest i query - string vs int

[plxz0r]

Bruker SQL Server 2005

 

Jeg har en side som viser brukerinformasjon. Av diverse grunner tenkte jeg å bruke ?bruker=Brukernavn i querystringen for å hente ut data fra db.

 

Jeg har vel alltid brukt ?bruker=1 f.eks i querystringen, pga dette er jo mange måter naturlig ettersom ID er tabellens unike ID.

 

Men spørsmålet mitt er, er det vesentlig stor forskjell i ytelse og "performance hit" ved å hente en post i tabellen via string mot int?

 

Jeg antar jo at det er mest optimalt med int, pga det er vel større ressursbruk å sammenligne strings istedenfor tall.

 

Men hvor mye forskjell kan det være?

[Manfred]

Det kommer jo helt an på hvor mange rader du har i tabellen din da. Er det 200 så er det nok ikke store forskjellen, men..

 

Du kan jo bare ta tiden på det... Sjekke hvor mange ms en spørring med ID tar, og hvor mange ms en spørring med brukernavn tar.

[kaffenils]

Det vil selvfølgelig alltid være veldig mye raskere å sammenlikne int enn varchar. Du vil nok ikke se det med det blotte øyet, men det vil ha endel å si for et veldig belastet system.

 

Gjorde en liten test mot en tabell med to kolonner id_int int og id_string varchar(10).

Fylte tabellen med 1.5 millioner rader hvor verdiene i id_int og id_string var et identisk stigende nummer.

Eksempelet er litt søkt da ingen av kolonnene er indekset, og ergo så er det en table scan som vil brukes i mitt SELECT eksempel, men det viser veldig godt forskjellen.

 

select id_string from mytable where id_string='500000'   tid: ~50ms
select id_int from mytable where id_int=500000   tid: <1ms

 

Som du ser så brukte spørringen med int kolonnen under 1ms, mens den andre spørringen tok ca 50ms.

 

 

Hadde kolonnene vært indeksert så ville det i praksis ikke vært noen forskjell.

Endret 10. april 2008 av kaffenils

[plxz0r]

Takk for testen kaffenils.

 

Leste litt om indeksering nå, og ser at det er en god ting i mange sammenhenger.

 

Men leste det at det passet veldig bra for tabeller med veldig mange rader som ikke ble oppdatert så ofte.

 

For når tabellen ble oppdatert (update, insert, delete) så måtte indeksen bygges opp på nytt, og at dette igjen kunne slå ut ytelsemessig. I så fall går det jo opp i opp.

 

Tanker om dette?

Der de sa dette brukte de forøvrig i SQL 2000, kan jo være 2005 er mer optimalisert.

Endret 10. april 2008 av plxz0r

[Manfred]

Du kan ha ganske mye trafikk mot databasen, og fortsatt vil indekser gi deg et MYE bedre resultat når det kommer til ytelse.

[plxz0r]

ID'en i tabellen blir jo automatisk satt til å bli cluster-indeksert.

 

Men hvis jeg legger til at brukernavnet også skal bli indeksert går indekseringen fra tabellen til non-clustered.

 

Så da blir jo ikke indekseringen sortert i rekkefølge + referansematriale, men blir kun referansematriale.

 

Clustered index er jo den beste veien å gå, men siden det ikke går an å ha flere enn en clustered index pr tabell, vet noen av dere om det er mye forskjell hastighetmessig mellom clustered og non-clustered?

[blackbrrd]

Å vedlikeholde indekser på felt du slår opp på ofte lønner seg. Hvis du har et felt som det blir slått opp på kanskje 1 av 1000 ganger, lønner det seg kanskje ikke å indeksere det. Spørsmålet er hva som skjer når du må slå opp på den uindekserte kolonnen. Hvis resultatet er at du må lese 50gb med data så kan det være uakseptabelt.

 

Indekser blir ikke fullstendig bygget opp på nytt for hver insert/update/delete. De fleste databaser bruker btree algoritmen til å lagre indekser. Når du skal gjøre en oppdatering til denne indeksen vil den i beste fall utføre 1-2 skriveoperasjoner, og i verste fall kanskje 10-20? Disse operasjonene kan stort sett gjøres i seperate tråder i forhold til transaksjonstråden din, mao så er det kun disk i/o bruken som kan være noe problem.

 

Angående kolonner som det kjøres cluster index på så er det veldig dyrt for tabeller som oppdateres ofte. (Det gir mye skrive-aktivitet som er en av to ressurs-flaskehalser i databaser. Den andre er mengden ram.) Det cluster index vil hjelpe på er om kun deler av tabellen er i aktiv bruk. F.eks hvis du har en ordre-tabell og du har kjørt cluster index på ordrenr så vil du ha svært lite aktivitet på de eldre ordrene. Databasen slipper å cache disse i ram hvis du har kjørt cluster index på ordreid og alle spørringene dine går mot indekser. I det øyeblikk du får en sequential scan på tabellen hjelper ikke cluster index noe.

[kaffenils]

For når tabellen ble oppdatert (update, insert, delete) så måtte indeksen bygges opp på nytt, og at dette igjen kunne slå ut ytelsemessig. I så fall går det jo opp i opp.

 

Som blackbrrd sier så bygges ikke hele indexen opp på nytt. En CUD (create, update delete) på data som inngår i indexen vil kun medføre endring i de aktuelle sidene i indexens b-tre.

 

Det som VIL skje når du har mye CUD aktivitet er at indexen fragmenteres noe som vil medfører mer I/O. Uansett er en fragmentert index mye bedre enn en om SQL Server må utføre table scans hele tiden.

 

Ang. clustered indexes så er det delte meninger om hvor bra disse egentlig er både for range scans og bookmark lookups. Har lest mange artikler som forklarer hvordan clusterd indexes IKKE er bedre for verken range scans eller bookmark lookups (link1,link2) selv om "best pratice" ofte beskriver at du bør ha en clustered index for performance.

Jer har enda ikke hatt tid til teste selv for å bekrefte/avkrefte hvorvidt dette stemmer.

 

Det som jeg VET at en tabell med en stigende unik clustered index vil være effektiv for er inserts. Dette skyldes at SQL Server bruker forskjellige algoritmer for å finne ut hvor en ny rad skal plasseres i en clustered index i motsetning til en heap (som er en tabell uten en clustered index). I en clustered index er posisjonen av en ny rad bestem av verdien i kolonnene som utgjør clustered index, mens for en heap så vil SQL Server sjekke om det er ledig plass i eksisterende pages pluss diverse andre ting som medfører at en insert blir mye tregere. Bare vær oppmerksom på at dette KUN gjelder når du setter inn stigende og unike verdier.