Unexpected behaviour - Query and a Table

• Ranil Rupasinghe

  Grasshopper

  Points: 11

  More actions

  July 10, 2004 at 3:28 am

  #111824

  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.

   

• deepankarb

  SSC Journeyman

  Points: 75

  More actions

  July 12, 2004 at 11:19 pm

  #514332

  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