Unclustered index faster than clustered index?
-
Hi.
I have a table with 338859 records. The table has a userid column
of type varchar(20) (null enabled). I use the following query:
select * from table1 where userid = 'X'
with X being a random userid. This is a slow test computer with 512 Mb ram and Windows 2000 Advanced Server.
** With no indexes the results are as follows:
33586 records. Logical reads 6797, physical reads 0, read-ahead reads 0. Estimated cost according to execution plan is 5.44
SQL Server Execution Times:
CPU time = 651 ms, elapsed time = 28002 ms.
** With clustered index <userid>:
33586 records. Logical reads 632, physical reads 0, read-ahead reads 0. Estimated cost according to execution plan is 0.545
SQL Server Execution Times:
CPU time = 230 ms, elapsed time = 21500 ms.
** With unclustered index <userid> (forcing the use of the index):
33586 records. Logical reads 33689, physical reads 0, read-ahead reads 0. Estimated cost according to execution plan is 206
SQL Server Execution Times:
CPU time = 461 ms, elapsed time = 13880 ms.
Why is the unclustered index so much faster? I've repeated the query
a few times and the results stays the same, with slight variations in the time values.
I've implemented DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE between each query.
Thanks
Leslie
-
Leslie,
To tabularly summarize the results
Mtehod LogicalRead Est Cost CPU Elapsed Time
No-Index 6797 5.440 651 28002
Clustered 632 0.545 230 21500
Non-Clust 33689 206.00 461 13880
So except for the elapsed time the Clustered Index is the best answer. The only thing I can think of is that you might not be getting a full data set back. If you run Query analyzer in Grid Mode I think it returns the first N rows (I don't know what N is) and since there are so many logical reads, it may stop transferring data sooner. Try running this with results in text and see if you get the same answer. I just realized you are probably only returning 1 row. There must be something in memory or you got lucky on the one you hit. It is wierd.
The clustered index is the best way to go, I can tell you that. Even no index is better that the non-clustered just based on Logical reads. What the system does with the non-clustered index, is find the indexed value, look up the key and then find the data in the clustered index.
Actually this might be the source of difference. When you have the non-clustered index on the table, is it the only index? Is there another clustered index on the table? If not, then that might affect the answers because the index would point to a data page and it might have an easier time getting the data expescially if that data page was in memory.
Try a test with a clustered index not on the column in question and the non clustered index on the column in question. The time should go up. Also other values might take longer.
-
You are right, the clustered index should be better.
I've just tested the records with a clustered index on a datetime field in the table, which is totally unrelated to the userid field. The logical reads is now 105082 and the time is faster than before: CPU 982 ms and Elapsed time is 2832 ms. How is this possible??
The grid displays all the records because I can browse each record, or am I missing something. When you talk about displaying the results in text, do you mean with a program or can Query Analyzer do this?
As for the index getting the information from memory, isn't DBCC DROPCLEANBUFFERS supposed to clean the memory of pages?
Like you said ... weird.
-
The time thing is wierd. Try running the clustered index twice in a row. See if time drops off. Do this clearing things, and not clearing things.
In Query Analyzer on the Menu option Query look for Results in Text and there is a corresponding results in grid. Text is handy for many things. BTW, on the Tools\Options page, results tab, you can change the default for which style the results are in. This is also where you can specify the max number of chars returned in a field. E.g. you have a VARCHAR(4000) column and you want to display all 4000 chars, that is where you can set it. It is also useful if you generate SQL statements.
I looked at DBCC DROPCLEANBUFFERS and it does look like the correct thing to do. to get consistent results.
Other than that I am out of ideas.
-
The text result is the same as the grid's result.
I tried the various multiple queries and the result stays the same: unclustered is faster. Everything I know about indexes is screaming against this.
I'm about ready to chalk this up as an anomaly and let it be.
I'm going to try this on a different pc and see what happens. After that...
Thanks Brian.
-
I also remembered something I did yesterday that might indicate what we haven't considered.
I executed the query
SELECT 1
and it took 2 minutes 10 seconds to run.
How the heck did that happen. I had run a query that returned 64,000 rows and the result set was still in query analyzer. I then ran another query in a different window that returned 64,000 rows or more on my PC, but Query analyzer actually stopped it because system resources got low.
I closed the first window and then in the second ran the SELECT 1 as I was waiting I brought up Task Manager and watched my swap file usage drop from well over a gig (on a 512M ram) and when it finally got low enough the query ran.
So maybe there is something there. I would toss it up as an anomally. If the table grows bigger, the delay should get bigger and at some point I can see it taking minutes or hours to run a simple query.
Good luck
-
Hi Brian.
I tried the query on different pc's and all the results except the CPU time varied.
My best guess is that the CPU resources available is playing havoc.
The CPU time is the only consistent factor and that is what I'll be using from now on.
I noticed that a faster pc with more ram delivered a result where the unclustered time was slower that the rest. Maybe it is something with older pc's or the amount or handling of ram (like you said: swap files).
Thanks Brian.
Leslie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply