September 26, 2012 at 2:13 pm
I have some SQL (SQL 2008) that I inherited and am trying to find out why some of queries are running really slow. In the Actual Execution Plan I have three clustered index scans which are costing me 19%, 21% and 26%, so this seems to be the source of my problem.
The contents of the fields (job numbers) are usually numeric (but some job numbers have an alpha prefix)
The database design (vendor supplied) is pretty poor. The max length of a job number in their application is 12 chars, but in the tables that are joined it is defined as varchar(50) in some places and varchar(15) in others. My parameter is a varchar(12), but I get same thing if I change it to a varchar(50)
The node contains this:
Predicate: [Live_Costing].[dbo].[TSTrans].[JobNo] as [sts1].[JobNo]=CONVERT_IMPLICIT(varchar(50),[@JobNo],0)
sts1 is detrived table, but the table it pulls jobno from is a varchar(50)
I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?
I'm fairly new to the execution plan, so I have a few more questions:
Is there an easy way to figure out which node in the exc plan relates to which part of the query?
Is the predicate, the join clause?
Regards
Mark
September 26, 2012 at 2:58 pm
mark 4643 (9/26/2012)
I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?
Yep.
Is there an easy way to figure out which node in the exc plan relates to which part of the query?
Is the predicate, the join clause?
Not... exactly.
If you take a look at the second link in my signature for indexes and tuning, it'll walk you through what we'll need to help you decipher what the problems are with your schema and queries. It gets a bit involved.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2012 at 3:00 pm
Evil Kraig F (9/26/2012)
mark 4643 (9/26/2012)
I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?Yep.
Is there an easy way to figure out which node in the exc plan relates to which part of the query?
Is the predicate, the join clause?
Not... exactly.
If you take a look at the second link in my signature for indexes and tuning, it'll walk you through what we'll need to help you decipher what the problems are with your schema and queries. It gets a bit involved.
Thansk Craig
September 26, 2012 at 3:08 pm
Attached is the exec plan and stats
September 28, 2012 at 2:00 pm
Apologies Mark, I've been heavily tied up the last few days and didn't have time to really dig into the information and virus-scan the .zip. I'd hoped someone else would have had time to swing through. Am I correct in that you'd still like assistance with this?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 28, 2012 at 8:01 pm
This is a pretty large "all in one" query that needs to be divided up. If you look for the large arrow in the bottom right section of the execution plan, you find it contains a row count of more than 17 million rows which is quite a bit larger than any of the tables involved. That means that you have a many-to-many join going on similar to a small cross join.
One you find the hash join that arrow is coming out of, look at the "residual probe" in the properties window for the hash join. It'll let you know what tables are involved in the join which will help you isolate the code.
My recommendation is to determine what the core of this query actually should be and to quickly isolate that much smaller result set in a Temp Table and then join to that instead of trying to do this query all at once. "Divide'n'Conquer".
--Jeff Moden
October 2, 2012 at 5:42 pm
Evil Kraig F (9/28/2012)
Apologies Mark, I've been heavily tied up the last few days and didn't have time to really dig into the information and virus-scan the .zip. I'd hoped someone else would have had time to swing through. Am I correct in that you'd still like assistance with this?
Hi Craig
Yes, still struggling with it
October 2, 2012 at 5:43 pm
Jeff Moden (9/28/2012)
This is a pretty large "all in one" query that needs to be divided up. If you look for the large arrow in the bottom right section of the execution plan, you find it contains a row count of more than 17 million rows which is quite a bit larger than any of the tables involved. That means that you have a many-to-many join going on similar to a small cross join.One you find the hash join that arrow is coming out of, look at the "residual probe" in the properties window for the hash join. It'll let you know what tables are involved in the join which will help you isolate the code.
My recommendation is to determine what the core of this query actually should be and to quickly isolate that much smaller result set in a Temp Table and then join to that instead of trying to do this query all at once. "Divide'n'Conquer".
Thanks Jeff
Will take a look tomorrow
Regards
mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply