July 15, 2009 at 2:02 pm
SQL 2000:
I've attached a zip file with .sqlplan, table/index defs, and procedure code that I'm trying to optimize. I just can't get rid of the two index scans. I'd appreciate your guidance in figuring out why.
Thanks,
Edit: updated zip file to not have the FK constraints
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 16, 2009 at 3:37 am
You can't. Or, at least, I don't know how you could.
The scan is due to the OR to include NULL values for processed_flag and reviewed. Using ISNULL or COALESCE won't help, it will always lead to a scan.
I'm sorry.
As a side note, your DDL script contains FK for tables that are not there: I suggest you remove FKs creation from the script in order to help other members help you.
Gianluca
-- Gianluca Sartori
July 16, 2009 at 8:26 am
I wonder if you might be better off breaking it down into some steps using temp tables. You reference the transaction_update table 5 times. Could you get all the data you need from transaction_update in a temp table and then reference the temp table? That would also allow you to convert NULL's to a value and eliminate the OR's.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2009 at 8:30 am
Gianluca Sartori (7/16/2009)
You can't. Or, at least, I don't know how you could.The scan is due to the OR to include NULL values for processed_flag and reviewed. Using ISNULL or COALESCE won't help, it will always lead to a scan.
I'm sorry.
I find this theory interesting, since the columns that are in the where clause are not in the tables that are producing scans. Since the entire where clause hits just one table, I decided to try a bit of "divide and conquer" ... I made a temp table from the transaction_update table consisting of just the Person_GUID for the records matching the existing query's where clause (with a clustered PK on the Person_GUID column), and then joined the temp table to the other tables in a query that has no where clause. The person/instructor tables are still performing scans (and, interestingly, so is the temp table, which has 228 rows).
I'm attaching a new plan and the new code.
Oh, here are the IO & time stats:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 25 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 43 ms.
Table '#tmp'. Scan count 0, logical reads 457, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transaction_Update'. Scan count 2, logical reads 17, physical reads 2, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 95 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 'Instructor_Update'. Scan count 291, logical reads 873, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Person'. Scan count 1, logical reads 139, physical reads 2, read-ahead reads 137, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Instructor'. Scan count 1, logical reads 45, physical reads 3, read-ahead reads 43, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tmp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transaction_Update'. Scan count 11, logical reads 67, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 923 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 1469 ms.
As a side note, your DDL script contains FK for tables that are not there: I suggest you remove FKs creation from the script in order to help other members help you.
:blush: Whoops... sorry about that. I've modified the script to take that out (in the original post).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 16, 2009 at 8:48 am
Jack Corbett (7/16/2009)
I wonder if you might be better off breaking it down into some steps using temp tables.
Jack, I see we're thinking alike here...
You reference the transaction_update table 5 times. Could you get all the data you need from transaction_update in a temp table and then reference the temp table? That would also allow you to convert NULL's to a value and eliminate the OR's.
Okay, I tried completely removing all references to the transaction_update table (except for the first time in the from statement) of the original code, and it's still producing scans on the other two tables. I removed all references to it in the second try (where there is a temp table that is used instead in the from statement, and the second query has no where clause), and this also is producing scans on those two tables.
This doesn't seem to be what is causing those scans...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 16, 2009 at 3:22 pm
Tough to say. Presumably SQL thinks it is faster to SCAN than to SEEK and then do a bookmark lookup. Perhaps you could try a covering index?
On Instructor try an index on instructor_guid and instructor_rank_text..?
July 16, 2009 at 6:35 pm
Lamprey13 (7/16/2009)
Tough to say. Presumably SQL thinks it is faster to SCAN than to SEEK and then do a bookmark lookup. Perhaps you could try a covering index?On Instructor try an index on instructor_guid and instructor_rank_text..?
There's already one there.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 17, 2009 at 12:51 am
WayneS (7/16/2009)
I find this theory interesting, since the columns that are in the where clause are not in the tables that are producing scans.
It doesn't matter, the optimizer can choose any path to filter the rows. Try yourself: get rid of all the OR expressions and you'll see the engine going for a seek.
-- Gianluca Sartori
July 17, 2009 at 2:19 am
Gianluca Sartori (7/17/2009)
WayneS (7/16/2009)
I find this theory interesting, since the columns that are in the where clause are not in the tables that are producing scans.It doesn't matter, the optimizer can choose any path to filter the rows. Try yourself: get rid of all the OR expressions and you'll see the engine going for a seek.
Look at the revised plan that is in the post you just quoted... I did just that, but I'm still getting the scans. (I moved the entire where clause to a temp table built before this query is run, the query now is just joins against tables, but the two scans are still there)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 17, 2009 at 2:31 am
I'm sorry, I was referring to the original post 'cause I had no time to look at the revised one. It's very strange: yesterday I removed from your query all the OR expressions and the plan turned to seek...
Maybe this is beacause I tried it in SQL2005?
-- Gianluca Sartori
July 17, 2009 at 5:42 am
You might try changing the order of the table definitions. I know everyone says that doesn't make a difference, but what they mean is, it doesn't normally make a difference. If you take a look at the properties for the SELECT operator at the head of the execution plan you can see that the query terminated it's optimization because it timed out. That means that, in this case, SQL Server didn't find an optimal plan.
I think you're on the right track, loading the temp table and then using that to join the rows of data. Just see if putting Person & Instructor first in the FROM definition doesn't make a difference.
Otherwise, what I'd do is tear the procedure down to basics. Eliminate all the derived tables and see what kind of query you get. Try to optimize that. Then slowly, add the derived tables back in, one at a time.
"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
July 17, 2009 at 11:03 am
Gianluca Sartori (7/17/2009)
I'm sorry, I was referring to the original post 'cause I had no time to look at the revised one. It's very strange: yesterday I removed from your query all the OR expressions and the plan turned to seek...Maybe this is beacause I tried it in SQL2005?
No problem, I understand.
This is really strange...
I've copied the DB onto a SQL 2005 (Dev Edition), and I get a radically different execution plan, but it has all seeks.
I've been testing it on a SQL 2008 (Dev Edition), and get the plans that have been attached.
The DB is currently running in prod on a SQL 2000 (Ent Edition).
I'm going to try refreshing my copy of the db from prod, and see what happens...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply