August 22, 2002 at 11:44 am
I have a clustered index on tag_claim_id. Which query is faster?
1.select tag_claim_id from CENTRAL_FACT_TABLE
2.select tag_claim_id from CENTRAL_FACT_TABLE
where SVC_CAT='N' --a small amount of rows
August 22, 2002 at 1:28 pm
The first will result in a clustered index scan (whole table).
The second will depend on if you have a non-clustered index on SVC_CAT. If you don't, you'll go to a clustered index scan as well because SQL Server won't have anything in which to narrow down SVC_CAT on.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 22, 2002 at 2:24 pm
there is no index on svc_cat.
So the second is faster or not?
-K
August 22, 2002 at 5:45 pm
If there is no index on svc_cat, it's most likely going to a scan, same as #1. Best way to check is to start up QA, toggle Show Execution Plan on (CTRL+K is the shortcut or Query | Show Execution Plan from the menu is the "long way"), and run each query. You should see an Execution Plan tab which you'll be able to view to see exactly what each query is doing.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 26, 2002 at 8:21 am
Unless MS has changed how clustered indexes work, a clustered index cannot cover a query. At least the way it used to work, a clustered index is a pointer tree, but nowhere is the actual page data duplicated in whole like in a nonclustered index. The "data leaves" of a clustered index are the table itself, so a SELECT col against a clustered index column will still result in a table scan.
If there were a nonclustered index on tag_claim_id, then it could be covered and a table scan could be avoided, but for the queries as written, the clustered index will not speed up either query. Clustered indexes are superior for range queries like greater-than, less-than, or between, but don't do much good other than that.
The only thing that could speed up query 1 is a nonclustered index on tag_claim_id, and depending on the width of the table, the index may or may not be used. The wider the table, the more likely the index is to be used.
The only thing that could definitely speed up query 2 is an index (clustered or nonclustered) with SVC_CAT as the first or only key, assuming that columns with the value 'N' are truly few in number. The wider the CENTRAL_FACT_TABLE, the more likely the index is to be used.
Frankly, more information (and then testing) is required to be able to make the right indexing decision, including frequency and timing of the query and importance/frequency/timing of queries other than these. We're not really given enough information in the original question to answer for sure. There are many other facts required to know whether a given index is a benefit or a detriment. Whatever you do, though, resist the urge to provide table/index hinting in the query.
- Troy King
August 26, 2002 at 8:31 am
Clustered Index Seeks are possible. Here's all the physical operators:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 26, 2002 at 8:59 am
But index seeks being possible doesn't mean that the clustered index pages contain every single value possible... clustered indexes contain pointers to the first value on a given table data page, not a duplicate of the column data itself (like a nonclustered index). The actual column data in a clustered index remains in the table itself... that's why clustered indexes are tiny, even for very large tables. The table itself is ordered by the clustered index column (which is why it is possible to have only a single clustered index per table).
Note the sql help page on Clustered Indexes, Architecture: http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1tbn.asp?frame=true . The index leaf nodes are the actual table data pages, not a duplicate of that column like a nonclustered index. Thus, a "SELECT col1 from table", in which col1 is the clustered index column, still results in the actual table being read because the clustered index only contains a single row for the first value on each actual data page, not the contents of each row itself.
Note the Nonclustered Indexes, Architecture: http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true . The leaf nodes contain an actual copy of the data from the table (but in sorted order), so a nonclustered index can "cover" a query without actually touching the table. A clustered index cannot do that since it does not contain a complete copy of the data it indexes.
- Troy King
August 26, 2002 at 9:19 am
We're conflicting because our semantics are different.
I was thinking along the lines of the WHERE clause, which would keep from doing an entire clustered index scan, but instead a seek. That's one of the benefits of using a clustered index for a column which will be receiving a lot of range queries. I agree wholeheartedly if you are doing just a SELECT against the column in a clustered index it will result in a table scan. No way around this one based on the way it's structured.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 26, 2002 at 9:43 am
Yup, Brian, I'm pretty sure you and I are the same page<g>. I was actually responding originally to chrhedga, who said "...This is because the clustered index covers the entire first query...".
I'm new to these forums, but I've been in the SQL Server performance game a while. Your name seems very familiar to me. Have we met?
- Troy King
- Troy King
August 26, 2002 at 2:23 pm
katravax, you are absolutely right. My mistake of course, don't know what I was thinking of.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply