Optimering på order by datetime felt.

måske

prøv og kig i query analyzer execution plan eller lav en lille test

Prøv at lave en

sp_help threads

og poste resultatet her

-> Janus:

http://upit.jtw.dk/uploads/2711/tsm-forumThreadshelp.JPG

-> Arne:

http://upit.jtw.dk/get_file/1a8/

Har du et index på answerDate feltet??


Du kan måske ændre feltet answerDate til et kortere (mindre præcist) felt - så vil der være mindre data at sortere udfra, og det skulle i teorien køre hurtigere.

Fra BOOKS ONLINE:
-> datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

-> smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

-> Remarks
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

IMHO er det en knapt så fed idé at have index på en datetime, jeg ville nok hellere forsøge at bruge en id-kolonne. Har du ikke en primary key kolonne på tabellen? Det står vel i den rigtige rækkefølge eller hvordan?

Jeg kan desværre ikke se de screenshots? Siden er bare hvid.