| Author |
Topic |
|
PeterG
Posting Yak Master
156 Posts |
 Posted - 2004-04-08 : 16:15:50
|
| I have a stored proc that uses a temporary table and when executed, does a table scan. I modified it by designating a field as a primary key. Now the plan states it is now using a clustered index scan. Should there be a difference in performance now that I'm doing an index scan? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-08 : 17:23:40
|
| It should. How big is the table?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-04-08 : 18:17:24
|
| It's not that big. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-04-08 : 19:18:43
|
| I've found that many times when I have a Table Scan on a Temp table, it's better to leave the Table scan instead of creating an index on the table and then scanning the index. Needless to say, these are fairly small Temp Tables, but the code is a bit easier to maintain because there's less of it. Your mileage may vary.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-08 : 23:52:23
|
| If you have a temp table where an index will not help, you should probably use a table variable if possible.Otherwise, the indexed temp table should be the best choice. How many rows/columns are we looking at? Also, are you seeing any issues?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-04-09 : 00:54:09
|
| clustered index scan and full table scan are pretty similar as far as cost. Either way you are physically scanning every block of the table looking for data.As far as using a table variable, that is a good idea as long as the table is not that large. It turns out that SQL Server does not generate statistics on table variables, therefore it can make wrong choices with the optimizer. Keep this in mind when using the tablevar, especially if you create a table valued function and use it in a join.-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-09 : 09:28:18
|
| clustered index scan and full table scan are pretty similar as far as cost. They aren't at all similar if the columns of the clustered index are in your WHERE clause and FROM clause. It's much more efficient than a table scan, and this becomes more the case as the width and length of the table grow.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-09 : 10:09:55
|
| my 2 CHAR(163)....Creating a temp table is going to (Usually) contain stuff you want to take action with/against...So it should always scan since it's going to be the driver...The temp table should only be what you need...so in this case I would think (and damn it, if I did that more often, I'd be in good shape) that an index scan or table scan would be similar...If you do the SELECT * INTO thing, then it wouldn't...Brett8-) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-09 : 10:14:12
|
| So if you're looking at a table with 30 columns, you want 10 of those columns, it will end up joining in the stored proc 5 or six times off of three of those columns, and there are 100,000 rows in the temp table.You would rather create an index on the three columns and not have to hit the base tables again that created the temp table? (option 1)You would only create the temp table with three columns and go back to the base table 5 or six times to get the rest. Since you don't have an index on the temp table it would be table scanning each time and the base table would be scanned each time also while you're at it.You would use a table variable which would just make the whole thing perform like Yak kaka.???It's too hard to make a blanket statement. You need to think about what you're doing each time and make a decision based on how SQL Server works.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-04-09 : 11:37:09
|
quote:
Originally posted by derrickleggettThey aren't at all similar if the columns of the clustered index are in your WHERE clause and FROM clause. It's much more efficient than a table scan, and this becomes more the case as the width and length of the table grow.
If you had a covered index then you would not be doing a clustered index scan. You would be doing a seek or a range scan depending on your query.A clustered index scan costs as much as a full table scan.http://www.sql-server-performance.com/jc_sql_server_quantative_analysis3.asp-ec |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-09 : 12:22:00
|
| So your take is that...Table ScanA table scan operation occurs when there are no suitable indexes. This could mean that no indexes exist period, or that many rows are expected and it is less expensive to scan the entire table. The execution plan shows a table scan operation if the table is heap organized, and an index scan operation if the table has a clustered index or if all required values are in a non-clustered index as shown in Figure 2-6. [img]http://www.sql-server-performance.com/images/jc_sql9.jpg[/img]Figure 2-6. Execution plans for table scan and clustered index scan operations.Figures 2-7 and 2-8 shows the cost detail for the table scan and index scan operations. An index scan has the same cost structure as a table scan.[img]http://www.sql-server-performance.com/images/jc_sql11.jpg[/img]Figure 2-7. Cost details for table scan.[img]http://www.sql-server-performance.com/images/jc_sql12.jpg[/img]Figure 2-8. Cost details for clustered index scan.by Joe Chang Table ScanA table scan operation occurs when there are no suitable indexes. This could mean that no indexes exist period, or that many rows are expected and it is less expensive to scan the entire table. The execution plan shows a table scan operation if the table is heap organized, and an index scan operation if the table has a clustered index or if all required values are in a non-clustered index as shown in Figure 2-6. Figure 2-6. Execution plans for table scan and clustered index scan operations.Figures 2-7 and 2-8 shows the cost detail for the table scan and index scan operations. An index scan has the same cost structure as a table scan.Figure 2-7. Cost details for table scan. Figure 2-8. Cost details for clustered index scan.By examining the I/O and CPU costs for tables over a range of pages and rows, the execution plan cost formula is as follows:I/O Cost = 0.0375785 + 0.000740741 per additional page CPU Cost = 0.0000785 + 0.0000011 per rowThe I/O base cost (0.0375785) is exactly 6 x 0.0062500 + 0.0000785. None of the table scan cost components were observed to vary by platform (1P/2P/4P). ...means that a table scan and index scan cost the same?I don't buy it.....it just says it has the same cost structure...not the same cost...unless I'm misunderstanding it...Which happens all the time, so I'm sure I won't be suprised....Brett8-) |
|
|
|
|
table scan vs. clustered index scan
