General information
Forum:
Microsoft SQL Server
Environment versions
SQL Server:
SQL Server 2005
Your first query has an order too because of "top" clause. Using "top" clause without "order by" causes adding an "auto order by" with first field of query by sql-server.
You have to create an index for "somedte" field for run your first query fast too...
>I have a 2 queries. One takes 45 seconds and the other is instand. The only differences is that the first query has no order. The order by clause uses a primary key. I think that the DATEADD() function is causing the problem. If I don't use the dateadd fuction and hardcode the date, it's much faster as well.
>
>declare @dateonly as datetime
>
>select @dateonly = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
>
>-- query takes 45 seconds
>select top 1 somedte, * from sqlservertable
>where somedte> @dateonly
>
>-- query is instant
>select top 1 somedte, * from sqlservertable
> where somedte > @dateonly
>order by serialnum desc
>
>Thanks,
>Dan
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only