Hi Rod,
It even takes long in EM. Takes more than 30 minutes. The same query used to take less than a second to run. We moved the Server from one machine to another using attach database.
The foll is the results returned that you requested.
SELECT TOP 1000 * FROM SALES WHERE DATE >= '1/27/2003' AND WEEK_CALCULATED=0
|--Top(1000)
|--Clustered Index Seek(OBJECT:([backoffice].[dbo].[sales].[IX_sales_1]) SEEK:([sales].[week_calculated]=0) WHERE:([sales].[date]>='Jan 27 2003 12:00AM') ORDERED FORWARD)
There is a clustered index on week_calculated
and a index on date
TIA
>>>The table has 10 million rows. The query is
>>>select top 1000 * from sales where date > '1/27/2003'
>>>and week_calculated = 0
>
>Things to check
>
>1. Do you have a clustered index ?
>2. Do you have an index on that date field ?
>
>
>
>
>
>You can always text the query in query analyzer and do a show plan on the query.
>
>Do the following
>
>1. Select you database in the Query Analyzer
>2. Run the following in the query analyzer
>
>SET SHOWPLAN_ALL ON
>go
>
>select top 1000 * from sales where date > '1/27/2003'
>and week_calculated = 0
>go
>
>Post the text results here and we'll take a look at the problem.
>
>
>Rodman
Fred Besterwitch, MCP
The harder you work. The luckier you get.