Avatar billede petermonielsen Nybegynder
26. juni 2011 - 20:53 Der er 14 kommentarer

indexers mm. for at optimere stored procedure

SELECT *
FROM (SELECT ROW_NUMBER() OVER ( ORDER BY lm.departureDate, lm.days )
AS ROW,COUNT(*) OVER() as MaxNum, lm.lastminuteId,
lm.departureDate, lm.days, lm.price, lm.other, substring(lm.hotel,0,20) AS hotel, lm.directlink, lm.firstTimefound
FROM LastMinute AS lm

WHERE lm.foundlastTime = 1  AND lm.departureDate >= '2011-06-26')
AS LastMinuteWithRowNumbers
WHERE Row >0 AND ROW <11

Er der nogen som vil være behjælpelig med hvilke indexers jeg skal benytte på ovenstående forespørgsel for at optimere den?
Tabellen har typisk 30.000 poster. Kan paging funktionen gøres mere effektiv? Jeg benytter MaxNum til at returnere det totale antal poster som matcher forespørgelsen.
Avatar billede arne_v Ekspert
27. juni 2011 - 00:33 #1
lm.departureDate
lm.days
lm.foundlastTime

var de oplagte!
Avatar billede Syska Mester
27. juni 2011 - 01:15 #2
Husk, den rækkefølge de har i din where clause skal de også have i dit index for at blive brugt.

mvh
Avatar billede arne_v Ekspert
27. juni 2011 - 04:27 #3
Nu snakker du index over flere felter ikke?
Avatar billede hrc Mester
27. juni 2011 - 10:19 #4
Hvis du går ind i Management Studio så kan du se hvilke indekser der anbefales (se Actual Execution Plan når du har kørt scriptet)
Avatar billede petermonielsen Nybegynder
27. juni 2011 - 16:37 #5
Til buzzzz - min where clause er egentlig dynamisk genereret og afhængig af parametrene som denne stored procedure kaldes med. Hvordan sikrer jeg mig så at den gør brug af disse indexes. Et eksempel kunne være:

where days = 7

eller

where departureDate > '2011-07-07'
Avatar billede hrc Mester
27. juni 2011 - 16:46 #6
Umiddelbart kan det blive ret svært at generere indekser på baggrund af hvad du spørger på, men scriptet er det samme og hvis du kigger på førnævnte "actual execution plan", så kan du se hvor skoen trykker - og den foreslår endda hvilke indekser der kan hjælpe.
Avatar billede Syska Mester
27. juni 2011 - 18:03 #7
Days = isnull(@days, days)

Så angiver du bare @days som null hvis den ikke skal søge på det og derved kan du sikre dig at dit index bliver brugt.
Avatar billede janus_007 Nybegynder
27. juni 2011 - 20:55 #8
Du får ikke noget ud af et non-clustered index, det kan jeg sige med sikkerhed :-|, eneste mulighed du har er at lave et sammensat clustered index på departureDate og formentligt på days

DepartureDate kan jeg gennemskue, men hvad er datatypen? Date, smalldatetime, datetime?
og hvilken datetype er days? hvad kan den indeholde?

hvad kan foundlasttime indeholde? hvis det er en bit, så hjælper index ikke særligt meget (som i ingenting)

Rækkefølgen i din where-clause er egentlig ligegyldig, det er kardinaliteten og derved din selektivitet der er afgørende her :)
Query-optimizeren kan selv lave rækkefølgen om i din where-clause.

lidt om kardinalitet:
http://www.bennadel.com/blog/1424-Exploring-The-Cardinality-And-Selectivity-Of-SQL-Conditions.htm

Det er meget svært at komme med flere guidelines uden at se den aktuelle query plan.
Avatar billede petermonielsen Nybegynder
27. juni 2011 - 20:59 #9
Jeg har lavet et screenshot af den nuværende "execution plan" - er der nogen som kan gennemskue det?
http://imageshack.us/photo/my-images/42/screenshotlmw.jpg/
Avatar billede petermonielsen Nybegynder
27. juni 2011 - 21:01 #10
departureDate (Date)
days (int)
foundlasttime bruges kun til 0/1
Avatar billede petermonielsen Nybegynder
27. juni 2011 - 21:08 #11
angående days - kan den indeholde tallene 1-21.
Avatar billede janus_007 Nybegynder
27. juni 2011 - 22:00 #12
Jeg kan se du bruger PK og clustered index, fjern clustered index fra LastMinute og opret et nonclustered istedet.

Opret et clustered på departureDate og days.

Smid et nyt screenshot :)
Avatar billede petermonielsen Nybegynder
27. juni 2011 - 23:17 #13
Så skulle det være sat op - cost er nu 54% på clustered index seek.
http://img190.imageshack.us/img190/5793/screenshotlm2.jpg
Avatar billede janus_007 Nybegynder
28. juni 2011 - 12:15 #14
Prøv lige at smide en order by ind her:
SELECT *
FROM (SELECT ROW_NUMBER() OVER ( ORDER BY lm.departureDate, lm.days )
AS ROW,COUNT(*) OVER() as MaxNum, lm.lastminuteId,
lm.departureDate, lm.days, lm.price, lm.other, substring(lm.hotel,0,20) AS hotel, lm.directlink, lm.firstTimefound
FROM LastMinute AS lm

WHERE lm.foundlastTime = 1  AND lm.departureDate >= '2011-06-26'
ORDER BY lm.departureDate, lm.days
) AS LastMinuteWithRowNumbers
WHERE Row >0 AND ROW <11

Gad vide hvordan den ser ud? Jeg kunne godt tænke mig at komme af med den Sort :)

En anden ting du måske også kan prøve, er at lave en include på dine kolonner...

dvs. drop clustered på days... behold kun clustered på departuredate.

og
create index IDX on LastMinute(departureDate, days)
INCLUDE(lastminuteId, price, other, hotel, directlink, firstTimefound)


Ellers hvis det er et stort problem er du velkommen til at sende mig en backup af din tabel så kan jeg bedre se på det :)

Men i bund og grund er paging på sql-serveren a bitch, og når vi kun snakker 30.000 rækker så ville jeg holde det i memory i din application :)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester