August 17, 2016 at 12:56 pm
Were you able to adjust an index to remove the lookup?
August 17, 2016 at 1:02 pm
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.
August 17, 2016 at 1:16 pm
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.
Good point, Ed. Brandie, the easiest way to do this is to look at the properties of the result operator, in this case the SELECT, leftmost in the plan. Somtimes it's not displayed in hover properties so check rightclick properties too.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 18, 2016 at 5:02 am
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.
You know what? I wasn't even thinking about implicit casts. I have no functions in the ON clauses and there is no WHERE clause (on purpose because this is an ITD query), but I forgot about the implicit stuff. I'll double-check this. Thanks.
August 18, 2016 at 5:21 am
Brandie Tarvin (8/18/2016)
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.You know what? I wasn't even thinking about implicit casts. I have no functions in the ON clauses and there is no WHERE clause (on purpose because this is an ITD query), but I forgot about the implicit stuff. I'll double-check this. Thanks.
No implicit casts happening. Most of the ON clauses are INT to INT with a few being CHAR to CHAR (secondary after the INT and all the same size as the one it's joining to). EDIT: Changed mistype to correct datatype.
To answer Josh's question about the Key Lookup index, I think I made progress yesterday, but then other fires cropped up, so now I need to go back and figure out where I left that one.
August 18, 2016 at 5:28 am
Brandie Tarvin (8/18/2016)
Brandie Tarvin (8/18/2016)
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.You know what? I wasn't even thinking about implicit casts. I have no functions in the ON clauses and there is no WHERE clause (on purpose because this is an ITD query), but I forgot about the implicit stuff. I'll double-check this. Thanks.
No implicit casts happening. Most of the ON clauses are INT to INT with a few being VARCHAR to CHAR (secondary after the INT and all the same size as the one it's joining to).
To answer Josh's question about the Key Lookup index, I think I made progress yesterday, but then other fires cropped up, so now I need to go back and figure out where I left that one.
VARCHAR to CHAR is an implicit conversion. What do you see about this in the properties or extended properties of the SELECT operator?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2016 at 5:33 am
ChrisM@Work (8/18/2016)
Brandie Tarvin (8/18/2016)
Brandie Tarvin (8/18/2016)
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.You know what? I wasn't even thinking about implicit casts. I have no functions in the ON clauses and there is no WHERE clause (on purpose because this is an ITD query), but I forgot about the implicit stuff. I'll double-check this. Thanks.
No implicit casts happening. Most of the ON clauses are INT to INT with a few being VARCHAR to CHAR (secondary after the INT and all the same size as the one it's joining to).
To answer Josh's question about the Key Lookup index, I think I made progress yesterday, but then other fires cropped up, so now I need to go back and figure out where I left that one.
VARCHAR to CHAR is an implicit conversion. What do you see about this in the properties or extended properties of the SELECT operator?
Sorry. I meant to say CHAR to CHAR. I mistyped that. EDIT: That's what I get when I'm flipping between this window and SSMS typing code. @=)
August 18, 2016 at 5:34 am
ChrisM@Work (8/18/2016)
Brandie Tarvin (8/18/2016)
Brandie Tarvin (8/18/2016)
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.You know what? I wasn't even thinking about implicit casts. I have no functions in the ON clauses and there is no WHERE clause (on purpose because this is an ITD query), but I forgot about the implicit stuff. I'll double-check this. Thanks.
No implicit casts happening. Most of the ON clauses are INT to INT with a few being VARCHAR to CHAR (secondary after the INT and all the same size as the one it's joining to).
To answer Josh's question about the Key Lookup index, I think I made progress yesterday, but then other fires cropped up, so now I need to go back and figure out where I left that one.
VARCHAR to CHAR is an implicit conversion. What do you see about this in the properties or extended properties of the SELECT operator?
Absolutely true, but I'm pretty sure it's one of the conversions that won't interfere with index/statistic use. NVARCHAR to CHAR/VARCHAR, different story.
"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
August 18, 2016 at 5:38 am
Grant Fritchey (8/18/2016)
ChrisM@Work (8/18/2016)
Brandie Tarvin (8/18/2016)
Brandie Tarvin (8/18/2016)
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.You know what? I wasn't even thinking about implicit casts. I have no functions in the ON clauses and there is no WHERE clause (on purpose because this is an ITD query), but I forgot about the implicit stuff. I'll double-check this. Thanks.
No implicit casts happening. Most of the ON clauses are INT to INT with a few being VARCHAR to CHAR (secondary after the INT and all the same size as the one it's joining to).
To answer Josh's question about the Key Lookup index, I think I made progress yesterday, but then other fires cropped up, so now I need to go back and figure out where I left that one.
VARCHAR to CHAR is an implicit conversion. What do you see about this in the properties or extended properties of the SELECT operator?
Absolutely true, but I'm pretty sure it's one of the conversions that won't interfere with index/statistic use. NVARCHAR to CHAR/VARCHAR, different story.
I'm pretty sure too, it's just a hook to see what else might be reported in the result operator warnings.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2016 at 5:55 am
ChrisM@Work (8/18/2016)
Grant Fritchey (8/18/2016)
ChrisM@Work (8/18/2016)
Brandie Tarvin (8/18/2016)
Brandie Tarvin (8/18/2016)
Ed Wagner (8/17/2016)
I know this may seem elementary, Brandie, but check the data types of the columns involved in your predicates - in both the JOIN and WHERE clauses. If you have an implicit cast going on, that can tank performance. Grant already mentioned the functions on columns. I've found that consistently starting by checking for these two things can yield decent results when troubleshooting performance problems. If nothing else, it's a quick sanity check that nothing was missed when writing the initial version.You know what? I wasn't even thinking about implicit casts. I have no functions in the ON clauses and there is no WHERE clause (on purpose because this is an ITD query), but I forgot about the implicit stuff. I'll double-check this. Thanks.
No implicit casts happening. Most of the ON clauses are INT to INT with a few being VARCHAR to CHAR (secondary after the INT and all the same size as the one it's joining to).
To answer Josh's question about the Key Lookup index, I think I made progress yesterday, but then other fires cropped up, so now I need to go back and figure out where I left that one.
VARCHAR to CHAR is an implicit conversion. What do you see about this in the properties or extended properties of the SELECT operator?
Absolutely true, but I'm pretty sure it's one of the conversions that won't interfere with index/statistic use. NVARCHAR to CHAR/VARCHAR, different story.
I'm pretty sure too, it's just a hook to see what else might be reported in the result operator warnings.
I think it started in SQL 2012 where there's a little icon shown on the result operator to indicate warnings. It draws attention to spills, implicit casts, etc. I thought it was pretty cool when we moved to 2012.
Brandie, any scalar functions or MTVFs we should know about?
August 18, 2016 at 6:21 am
<headdesk>
So I realized that the date column I'm pulling from history was acting a little funny. We have some dates in August (I.E., the "order" was entered in August but the date in details history is in July). That seemed awkward and strange. The vendor still hasn't responded to my questions on why this would be happening.
I went down to my business unit and asked a guy where he would find this date information (client side) if he needed it. He gave me an answer that told me I could drop the history table and just pull from details (since I'm there anyway). I like this answer. This would solve my problem. But I need to double-check. I asked a second person, different department, who told me "Oh, we don't use that feed anymore. The reporting team built us a new one."
Hrrm. So I went to a third guy (a manager type) and asked him what he knew about the feed, explaining my problem. And apparently we do not use this feed anymore.
Three 1/2 days of work troubleshooting a feed that isn't supposed to be running anymore, but no one bothered to tell us.
<headdesk>
Can I go home now?
Thank you, everyone, for your suggestions. I was making progress and I learned a few new things about Execution Plans. This should help me with future queries.
August 18, 2016 at 6:38 am
Well, Brandie, I guess I'm happy and sad for you. You didn't get to figure out the puzzle, but it isn't a problem for you any more.
August 18, 2016 at 6:49 am
Ed Wagner (8/18/2016)
Well, Brandie, I guess I'm happy and sad for you. You didn't get to figure out the puzzle, but it isn't a problem for you any more.
I was getting close, but there were two questions I had for the vendor. The first was "is there a key column that is disguised with a different name that I can join on other than OrderId?" and the second was "how is this date column working and can I compute it without joining to monster table or pull it from elsewhere?"
I'm not going to tell the vendor I don't need the information, just wait to see when / if they get back to me. Then use that information to give it another shot. If I find an answer, I'll let you guys know. But my hope is that I can either join to a smaller subset of that table or that I can dump it from the query. With UPDATE STATS, one nonclustered index, and two possibly redundant tables removed, I managed to get my time down to less than 5 minutes. But that was without the monster table joined.
August 18, 2016 at 7:53 am
Brandie, go home.
"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
August 18, 2016 at 7:57 am
Grant Fritchey (8/18/2016)
Brandie, go home.
Thank you.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply