Puzzled about results

• bev.kaufman

  SSC Enthusiast

  Points: 163

  More actions

  April 5, 2013 at 9:59 am

  #270667

  I have created the tables using your script and have been running queries. I ran the following in one batch because I wanted to compare the difference in the search parameter as well as the difference between having an index and not having one.:

  SELECT *

  FROM dbo.Contacts_index

  WHERE LastName LIKE 'S%'

  SELECT *

  FROM dbo.Contacts_index

  WHERE LastName LIKE 'Ste%'

  SELECT *

  FROM dbo.Contacts_noindex

  WHERE LastName LIKE 'Ste%'

  The logical reads were 568, 111 and 568 respectively, but the elapsed time was 227 ms, 106 ms and 95 ms, making it appear that it took less time to search the table with no index.

  Unable to believe that, I tried it again with just Statements 2 & 3. The logical reads remained at 111 and 568, but now the elapsed time was 66 ms and 191 ms.

  I've gone through the excercise three times now, and while the elapsed time number are slightly different each time, the pattern remains the same. The non-indexed table performs better if I first run Statement 1, which is on a completely different table.

  Any clues?