På vilket sätt kan man i SQL göra sökning snabbare? Har det betydelse att i Where-satsen skriva variabler i en viss ordning? Om en tabell är sorterad på en kolumn, söker då SQL igenom alla poster i alla fall eller hittas på något sätt just de eftersökta posterna snabbare då? Hur kan i så fall SQL veta att kolumnen är sorterad? Jag har testat med och utan Order By på slutet men har inte märkt någon skillnad på sökhastighet. Tar det verkligen ingen märkbar tid att sortera? Databasen är på 24000 poster.

Order by och group by spelar ingen som helst roll om det är
exekverings-prestanda du är ute efter. De bestämmer bara hur
sökresultatet ska presenteras.

Däremot används index för att snabba upp sökningar. Dvs du väljer
vilka kolumner i en tabell som ska förses med index (ett eller ytterst få
per tabell snabbar upp - börjar man få hybris och indexera allt kommer
de inte att hjälpa eftersom sökningen då sker på samma sätt i alla fall).

Gör du sedan en sökning som har med din indexkolumn att göra
kommer sökningen att gå snabbare.

//Emma

Jag undrade om man har en kolumn som innehåller en massa Andersson och en kolumn som innehåller EN post med Kalle, påverkas då sökhastigheten om man då skriver ...Where Efternamn Like "Andersson" And Förnamn Like "Kalle" i jämförelse mot om man skriver ... Where Förnamn Like "Kalle" And Efternamn Like "Andersson"

Det händer något mystiskt här. Jag har en kolumn med personnummer. Ur den söker jag med tre alternativ.
1. Födda före 800101, det tar 90 sekunder att söka.
2. Födda efter 800101, det tar 15 sekunder att söka
3. Födda mellan 800101 och 900101, det tar 4 sekunder att söka


Hur kan det bli sådana tidsskillnader?

Det finns flera aspekter här:

1. Hur många träffar blir det på de 3 respektive fall du angett?
2. Är fältet indexerat , vilken fälttyp
3. Skriver du select * - så betyder det även hämta ALLA fält
4. Prova dina select satser med bara .. select count(*) from .. where .. och se om det fortfarande tar 90 sekunder.
5. Kan det vara så att när du testat med andra och tredje frågan så är datat cachat och visas snabbare?
6. Vad använder du, ADO, DAO
7. Client sided cursors eller server sided?
8. 24000 poster är ingenting i en databas. När du närmar dig flera hundratusen eller miljoner poster snackar vi accesstider på 1,5 minuter.

Några funderingar...
/Pelle

Det blir ca 21000, 6500 respektive 3000 sökträffar. Det är stora skillnader för så få poster. Fälet personnummer är indexerat och av texttyp. Sedan är den kolumnen relaterad till en annan tabells personnummer som också är indexerad. Den tabellen är i sin tur relaterad till en tredje tabell men där är inte kolunerna indexerade. Jag använder ADO och är "conectad" till en server. Om man kopplar en mätare till nätet så ser man att antalet Mbit varierar under sökningen på det första alternativet som alltså tar längst tid. Jag väljer bara ut några fält i sökningen och när jag använde select count(*) så fick jag EN träff istället för 6000. Det går något långsammare vid första sökningen än de påföljande. Jag lägger sökresultat i en ny tabell så programmet skall plocka lite med datat också. Det borde gå snabbare om man lägger sökträffarna i en tabell på clienten men då funkar det inte i den kommande hanteringen eftersom den tabellen är kopplad till ett annat program på en annan server.

En tumregel när man arbetar med SQL-Server eller Oracle är att man alltid ska sätta det mest restriktiva villkoret sist då dessa databaser är byggda kring en teknik som tolkar frågornas villkor bakifrån.

Eehh...ORDER BY och GROUP BY spelar definitivt roll för hur lång tid en fråga tar att exekvera. Beroende på vad frågan gör i övrigt samt hur mycket rader som hanteras så kan de ta mer eller mindre del av tiden för att exekvera en fråga. Behöver man inte ha datat i en viss ordning ska man naturligtvis inte köra ORDER BY.

Att man har flera index är aldrig dåligt för frågor. Frågeoptimeraren kommer att välja det eller de index som är bäst lämpade för en fråga. Däremot gör de att datamodifikation blir långsammare. Ett index måste ju upprätthållas hela tiden, så om man lägger till/uppdaterar/tar bort en rad så måste indexet uppdateras. Har man då flera index måste samtliga uppdateras. Därför ska man inte lägga upp index hej vilt utan att man egentligen behöver dem.

Om man aplicerar tumregeln på frågan ovan om Kalle Andersson så är det alltså varianten
Where Efternamn Like "Andersson" And Förnamn Like "Kalle"
som går snabbast?

Du nämner inte alls vad det är för databashanterare du använder, så det är svårt att säga något om tiderna. Testa ta upp samtliga rader ur tabellen och se hur lång tid det tar.

Jo, det kanske kan användas som tumregel, men det kommer normalt inte att påverka. Från sql-frågan skapas ju en exekveringsplan vilken är vad som bestämmer vad som ska köras först etc. Här tittar optimeraren på statistiken för relevanta index och väljer utifrån den vilket jämförelsetest den ska utföra först.

SQL-server har en form av cache, som optimeraren arbetar intimt med.
Om du ställer liknande fråga kollar den vad den s a s "har i minnet". Det är därför dina tider går snabbare och snabbare.

Detta blir speciellt viktigt då du gör uträkningar på vilka som är födda före och efter ett visst datum, formaterat som text. Då måste SQL-server först omvandla och dessa värden sparas troligtvis i "cachen".

Jag tror att Herr Headgate kan detta bättre.

Mitt råd till dig vad gäller optimering i detta fall är att spara personnummer utan -, och ha en numerisk datatyp. Dessutom mindre minnesåtgång och jämförelser går snabbre. Formatering utåt kan du ju sköta i ett annat lager.

Det är en Accessdatabas

Det skulle du nog påpekat från början med tanke på dem svar som kommit in :-)

Ursäkta

Mr Hedgate,
Du har helt rätt ang. hur frågan konstrueras och jag ville bara klargöra lite för varför man bör använda min tumregel.

När en databashanterare konstruerar uttagsmängderna för en fråga så går den precis som du säger efter indexstatistiken men om man gör frågor på ickeindexerade kolumner så går den alltid bakifrån och framåt. Detta beror på att de flesta sql-tolkar (jag säger INTE alla, men Oracle gör det garanterat då det till och med står omnämnt i deras dokumention under i Tuning-sektionen) är uppbyggda så att de läser frågor baklänges.

Ett enkelt test i SQL Server 2000 och Oracle 8.05 resulterade i följande:
Dator:
Intel P1 233 MMX 64mb RAM OS W2K
Data:
Ett personregister (drygt 150000 poster)
Namn:
PE_PERSON
Fält:
PE_ID => Numerisk Integer räknare Indexerad Primärnyckel
PE_FIRSTNAME => Varchar 35 tckn
PE_LASTNAME => Varchar 35 tckn Klustrat index
PE_LKFCODE => Varchar 6 tckn

Problem:
Ta ut alla 'Kalle' i med LKF som '01%' (tror 01 är stockholm)
SQL =
SELECT COUNT(PE_ID) FROM PE_PERSON WHERE
variant 1
PE_FIRSTNAME = 'Kalle' AND PE_LKFCODE LIKE '01%'
variant 2
PE_LKFCODE LIKE '01%' AND PE_FIRSTNAME = 'Kalle'

Antal 'Kalle' 1019st
Antal LKF = '01%' 31023st

Antal under båda villkoren 401st
variant 1 tog strax över
14 sekunder i båda databaserna

variant 2 (den jag normalt skulle använda) tog
drygt 8 sekunder i SQL-Server
knappt 7 sekunder i Oracle

Jag importerade datat utan några index sorterat på LKF-koden, tog backup och lade till indexen. Sedan körde jag variant 1 på båda databaserna. Sedan startade jag om datorn och återställde backuperna och körde variant två.

Jag vet att detta inte är ett vetenskapligt test som på något vis är slutgiltigt men det ger iaf en finger visning om att ordningsföljden på villkor spelar roll i sql-frågor.

Visst, om du inte har ett vettigt index att utnyttja, vilket jag nog hellre skulle 'predika' än att se till att skriva where-klausuler i 'rätt' ordning. Det är ju nämligen nästa problem, hur vet du vilket som är rätt ordning? Hur vet du om colA LIKE 'xyz%' ger färre träffar än colB LIKE 'abc%'?

Det vet man iofs inte, men många känner sitt data så väl att man ändå kan estimera vilket villkor som ger flest träffar, det är ju också som så att man kanske inte vill lägga index som slöar ner uppdateringen i transaktionsrika system på de fält som används till vad jag vill kalla för sällanfunktioner. Till exempel när man gör adressuttag för kampanjer ur CRM-system så kan ålder och kön vara såna fält man inte har indexerade men man kan ju misstänka att en begränsning på ålder ger färre träffar än en begränsning på kön och då är det applicerbart att använda tumregeln.

För övrigt är ju vettigt lagda index A och O i en databas med lite prestandakrav på sig så jag håller med dig om att det är mycket viktigt att lägga indexen rätt. Samtidigt så tycker jag att om man kan tjäna prestanda på att skriva frågorna rätt så är det inte fel det heller...