November 14, 2011 at 5:33 am
Thanks for your input, Gail. I appreciate it.
November 14, 2011 at 6:22 am
J Livingston SQL (11/13/2011)
good to hear you have the testrig sorted now.so back to your original post
However, upon conducting some tests using a simple single column query (I can provide the query and underlying table structure if required) on a single table I was perplexed as to why the query ran faster when the table was completely unindexed.
Here's some stats to illustrate:
* Unindexed query = 23 seconds
* With primary key (not on SELECTed column though) took 44 seconds
* With index on SELECTed column took 32 seconds
what figures are you returning from the testrig?
I ran two tests using your test rig script as a blueprint. One version was basically the script you sent me which creates and produces stats based on a 1,000,000 row sample.
The second is a modified version of your test rig which actually uses the live data I have (i.e. the 88.6 million row version).
Here's the results ....
1,000,000 basic sample results:
-----------UNINDEXED
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TempSales'. Scan count 3, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 588 ms.
-----------INDEX ON PRICE
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TempSales'. Scan count 1, logical reads 2731, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 338 ms.
-----------IWITH PRIMARY KEY ON SalesID & Price INDEX DROPPED
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TempSales'. Scan count 3, logical reads 4242, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 578 ms, elapsed time = 608 ms.
And here's the results based on my actual TempSales table ...
____________________ UNINDEXED QUERY
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TempSales'. Scan count 3, logical reads 274319, physical reads 4089, read-ahead reads 274318, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 28062 ms, elapsed time = 24771 ms.
____________________ INDEXED ON Price QUERY (NONCLUSTERED)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TempSales'. Scan count 3, logical reads 198374, physical reads 1009, read-ahead reads 197892, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 19640 ms, elapsed time = 36028 ms.
____________________ WITH PRIMARY KEY ON SalesID QUERY (Price INDEX DROPPED)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TempSales'. Scan count 3, logical reads 275253, physical reads 906, read-ahead reads 274704, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 29016 ms, elapsed time = 58774 ms.
November 14, 2011 at 6:26 am
Hard to explain without the new execution plans... nothing obvious from what you posted.
November 14, 2011 at 7:22 am
Ninja's_RGR'us (11/14/2011)
Hard to explain without the new execution plans... nothing obvious from what you posted.
The query itself is unimportant and as such I'm a little reluctant to continue pursuing an answer by wasting the time of all those that have helped so far. As a newbie the fact that an unindexed table performs better in terms of query speed was somewhat puzzling, but not by any means a show-stopper.
Could it simply be that an index on a single column will actually be a degrading factor in query performance if there's high numbers of duplicates present?
If you want me to send some execution plans then please let me know how to do this so as to be useful to you.
Thanks once again.
November 14, 2011 at 7:25 am
raotor (11/14/2011)
Ninja's_RGR'us (11/14/2011)
Hard to explain without the new execution plans... nothing obvious from what you posted.The query itself is unimportant and as such I'm a little reluctant to continue pursuing an answer by wasting the time of all those that have helped so far. As a newbie the fact that an unindexed table performs better in terms of query speed was somewhat puzzling, but not by any means a show-stopper.
Could it simply be that an index on a single column will actually be a degrading factor in query performance if there's high numbers of duplicates present?
If you want me to send some execution plans then please let me know how to do this so as to be useful to you.
Thanks once again.
Save the plan as .sqlplan file. Then hit edit attachements when you make a new post.
Indexes are good but they are not the end all be all of performance. There are many cases where a scan is better than a seek.
November 14, 2011 at 7:25 am
raotor (11/14/2011)
Could it simply be that an index on a single column will actually be a degrading factor in query performance if there's high numbers of duplicates present?
No.
An index on a single column is generally not that useful. If the optimiser mis-estimates the cardinality and selects the wrong plan, the query will be slower. It's not a result of duplicates though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 8:07 am
Ninja's_RGR'us (11/14/2011)
Save the plan as .sqlplan file. Then hit edit attachements when you make a new post.Indexes are good but they are not the end all be all of performance. There are many cases where a scan is better than a seek.
Please find attached the execution plans I beleive you wanted.
There are three included and their filenames make it clear which is which.
BTW all these were run on my live TempSales table.
November 14, 2011 at 8:09 am
Those are estimated plans, we need to real (actual) plans. There's critical info in there to debug this.
November 14, 2011 at 8:12 am
SELECTCOUNT(Price),
Price
FROMTempSales
GROUP BY Price
ORDER BY Price;
That's a really, really weird query to run. Count the non-null values of price for each unique value or price, then order by the same column.
That can't seek (no predicates), so at best it's an index scan and the only real benefit from the index is the stream aggregate without a sort.
p.s. According to your statistics, the indexed one is the fastest. It has the lowest CPU time. Display time's affected by network transmission, display and all other fun things and does vary from one execution to another. If you've only taken stats from a single execution you can't draw any wide-ranging conclusions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 8:58 am
GilaMonster (11/14/2011)
SELECTCOUNT(Price),Price
FROMTempSales
GROUP BY Price
ORDER BY Price;
That's a really, really weird query to run. Count the non-null values of price for each unique value or price, then order by the same column.
That can't seek (no predicates), so at best it's an index scan and the only real benefit from the index is the stream aggregate without a sort.
p.s. According to your statistics, the indexed one is the fastest. It has the lowest CPU time. Display time's affected by network transmission, display and all other fun things and does vary from one execution to another. If you've only taken stats from a single execution you can't draw any wide-ranging conclusions.
OK .... ermm ..., I don't know what all that meant regarding your analysis of the query. I do feel a bit like a caveman confronted with a MP3 player! so apologies for my lack of comprehension at my end.
The unindexed query runs the fastest and has consistently done so over more than a dozen executions. I am running SQL Server on my own PC, so there's no network traffic etc to worry about.
November 14, 2011 at 9:05 am
raotor (11/14/2011)
GilaMonster (11/14/2011)
SELECTCOUNT(Price),Price
FROMTempSales
GROUP BY Price
ORDER BY Price;
That's a really, really weird query to run. Count the non-null values of price for each unique value or price, then order by the same column.
That can't seek (no predicates), so at best it's an index scan and the only real benefit from the index is the stream aggregate without a sort.
p.s. According to your statistics, the indexed one is the fastest. It has the lowest CPU time. Display time's affected by network transmission, display and all other fun things and does vary from one execution to another. If you've only taken stats from a single execution you can't draw any wide-ranging conclusions.
OK .... ermm ..., I don't know what all that meant regarding your analysis of the query. I do feel a bit like a caveman confronted with a MP3 player! so apologies for my lack of comprehension at my end.
The unindexed query runs the fastest and has consistently done so over more than a dozen executions. I am running SQL Server on my own PC, so there's no network traffic etc to worry about.
Faster's faster. Sometimes that needs to be good enough.
I admire the curiosity tho!
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply