Unexpected behaviour - Query and a Table

  • 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.

     

  • 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