March 8, 2011 at 5:39 am
I have one BIG search query which is using near by 30 joins to get the expected output. First few tables (5) are using INNER JOIN whereas rest of the tables are using LEFT JOIN.
The search query is using only one input & filter parameter i.e. DateTime.
FirstTableInJoin.SearchColumn > DateTime
The execution plan generated for this query shows N.C Index Scan, C. Index Scan, Table Spool (:w00t:). which is natural for such kind of query.
I revised the query in SP using table variable that holds the keyId of search records for FirstTableInJoin, which has generated the better execution plan .. C. Index Seek, N.C Index Seek, C. Index Scan.
But the major differenece in both the execution plans are reads (logica reads).
The unrevised query is showing less reads as compared to revised which is 10 times greater than unrevised. The query cost is showing less as compared to unrevised.
Now em little bit confused with which query I shoud go..:doze:. Please help.
Abhijit - http://abhijitmore.wordpress.com
March 8, 2011 at 8:13 am
Abhijit More (3/8/2011)
I have one BIG search query which is using near by 30 joins to get the expected output. First few tables (5) are using INNER JOIN whereas rest of the tables are using LEFT JOIN.The search query is using only one input & filter parameter i.e. DateTime.
FirstTableInJoin.SearchColumn > DateTime
The execution plan generated for this query shows N.C Index Scan, C. Index Scan, Table Spool (:w00t:). which is natural for such kind of query.
I revised the query in SP using table variable that holds the keyId of search records for FirstTableInJoin, which has generated the better execution plan .. C. Index Seek, N.C Index Seek, C. Index Scan.
But the major differenece in both the execution plans are reads (logica reads).
The unrevised query is showing less reads as compared to revised which is 10 times greater than unrevised. The query cost is showing less as compared to unrevised.
Now em little bit confused with which query I shoud go..:doze:. Please help.
Since I can't see the queries, this is pure speculation.
When you say "costs" do you mean execution time and I/O or do you mean the estimated costs of the operations within the execution plan. If the latter, you need to remember that those costs are only estimates and that the estimated cost for a table variable, since it has no statistics, will be completely off because of the lack of statistics.
Use execution plans to understand what a query is doing, not as an apples-to-apples comparison for performance tuning. The way to measure and compare performance is to measure and compare performance, meaning execution time, seeks and reads. Those values will tell you what you need to know.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply