I have noticed that whenever I use a where clause in my select statement It hangs( even in EM ). These statements used to work very fast from VFP or QA or EM. Can the indexes be corrupted ?

How can I fix this corruption ?

the sales table has 11 mill rows. if I add one index it takes about 10 hours to run.

Queries work very fast on other tables. It only works slow on 1 table. The CPU usage goes over 60% whenever I run a query on the sales table. even to return 10 rows.


TIA


>And to add to what Rod said, if you have relatively few rows with week_calculated = 0, then make it a tinyint and index on that. At the very least, you need a clustered index ( you can only have one). If you most often search on the date field, then that may be a good candidate. Also - keep in mind that just because you are only returning 1000 rows, SQL Server in your case, will still need to do a full table scan to pick off which would be the top 1000 rows.
>
>
>
>
>
>>The table has 10 million rows. The query is
>>select top 1000 * from sales where date > '1/27/2003' and week_calculated = 0
>>
>>date is a datetime field
>>week_cauculated is a tinyint that store 0 or 1
>>
>>I first used week_caucluated as bit but I could not create an Index
>>
>>TIA
>>
>>
>>>I believe that when you return rows through EM, it begins return rows immediately, fetching more rows in the background.
>>>
>>>Starting with the basics, can you issue a query in QA that returns just one or a few rows? How big is your table. In a C/S environment, you would rarely return all rows form a tx table.
>>>
>>>Understand that when you return all rows, it is going to be a full table-scan. i.e., no optimization and no indexs (unless I suppose the fields you are returning are all found in an index).
>>>
>>>FWIW, based on your facts, while there is likely a "problem", it does not rest with SQL Server.
>>>
>>>
>>>>Hi Guys,
>>>>
>>>>I am having a very strange problem. When I run a query in enterprise manager(server machine,open table, return all rows, click on sql button and type the query there) it runs super fast. When i run the same query in the query analyzer on the server machine it hangs. This also happens If i run this query from a VFp Client or .net. it just hangs. can someone help me.
>>>>
>>>>The Sql server is running on Windows 2003, 1 gb mem, 300 gb hdd.
>>>>The LDF file is 110 GB
>>>>the MDF File is 17 GB. No backup of the transaction log has ever been run. The IT Manager only let me know this 1 day ago.
>>>>
>>>>TIA.