July 10, 2004 at 3:28 am
Hi All,
I have a table (say Table1) with around 182,000 records and it has about 64 rows.
Difference between below 2 queries is significant and difficult to understand why.
SELECT TOP 134 * FROM Table1 ORDER BY Field1, Field2, Field3
- Above completes in about 3 seconds
SELECT TOP 135 * FROM Table1 ORDER BY Field1, Field2, Field3
- Above takes about 4 mints & 15 secs.
Field1 - varchar(25)
Field2 - varchar(5)
Field3 - varchar(5)
The table is indexed on above 3 columns individually but there's no compound index on all 3 columns.
Avg row size returned is around 694.
[Well, we don't really run this query in our production environment but I noticed above behaviour when I was working with the above table and wonder what could possibly causing the above behaviour. Execution plans don't provide any clue re this!]
Any hints or tips that can be helpful to identify the problem is highly appreciated!
Thanks!
Ranil.
July 12, 2004 at 11:19 pm
May be I am shooting arrows in the dark, but my feleing here is that the query has become slow due to defragmentation of data.
Try using dbcc showcontig command.
For more help follow this link
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply