Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Puzzled about results Expand / Collapse
Author
Message
Posted Friday, April 05, 2013 9:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 2:38 PM
Points: 38, Visits: 42
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?
Post #1439329
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse