November 9, 2010 at 1:16 pm
Hello community!!
I have a pretty weird issue and wanted to see if anyone could point me in the right direction. My user is querying a view looking for matches against the column form id but SQL is using the form id to probe against the message_id field for a match. The problem is that the message_id field is a unique_identifier so the execution plan has a no join predicate warning. Can someone help me with troubleshooting this problem?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT rl63_activities.name "rl63_at_405", outbound_forms.name "at_176", rl1267_EmailLinksView.UniqueLinkClicks "rl1267_at_21034"
FROM outbound_forms outbound_forms
LEFT JOIN activities rl63_activities
ON
(rl63_activities.activity_id = outbound_forms.activity_id)
JOIN EmailLinksView rl1267_EmailLinksView
ON
(rl1267_EmailLinksView.email_id = outbound_forms.outbound_id)
WHERE (outbound_forms.outbound_id = 26301)
November 9, 2010 at 1:19 pm
Can't really help on this without a copy of the execution plan.
What's the problem with the join method? Is the query unexpectedly slow?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2010 at 1:23 pm
Hi GSquared
The query is extremely slow....4 hours. Let me see if I can get the exec plan uploaded.
November 9, 2010 at 1:26 pm
Here is the execution plan.
November 9, 2010 at 2:23 pm
There are tables in the execution plan that aren't in the query, probably because they're in the view. Can you post the view definition? Ideally, the table DDL would be helpful as well.
It looks to me like there's a malformed join in the view, and that's probably causing your issue, but I can't nail it down from what you've posted thus far.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2010 at 2:45 pm
I thought it was the view also but could not find the offending join. The tables columns used for the join are all of the same type and have indexes. The message_id field is used in the view but I can not tell why SQL is using it for a probe. The view definition is attached.
November 9, 2010 at 2:52 pm
It's because the whole view is built around that column. It's got Not Null checks, joins, et al, in there, and it's indexed (looks like it's the PK and clustered index for one of the main tables in the view). The optimizer is basically going, "This is so complex that I'll just brute force that part of it, what's the biggest target I can pound on?"
Most likely, there's a simpler path to get the data the user needs, but one of the drawbacks of views is they plug their whole query definition into the outer query, and that can drive up the complexity on the execution plan like crazy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2010 at 3:34 pm
Thanks for looking G - Is there anyway that you can think of to force SQL to not use that field other than rewriting the view?
November 10, 2010 at 6:21 am
Can you simplify the outer query by getting rid of the view and including only those tables and columns that the outer query really needs, instead of the whole complex view being built into it?
A simplified derived table (either CTE or in the From clause) will probably solve the issue.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2010 at 6:27 am
Yes - The problem is that the view was built into the product years ago and is now used by all customers for data analysis. It seems to only throw up on itself when a customers runs a query and uses this particular where clause. That is why I am confused. If the view was extremely malformed I would expect it to blow up all the time. Am I making sense?
November 10, 2010 at 6:48 am
Yes, you're making sense. And the issue makes sense too. It's going to be some issue with stats vs execution plans vs query complexity vs etc vs etc. It'll only come up with certain queries, and there's no real way to tell before-hand which ones. That's one of the drawbacks of "Ring of Power" views ("one view to query them all and in the darkness ..." you get the idea). Like all "solves everything" generic solutions, they simplify some things, but don't work well all the time. No Swiss Army Knife is as good at driving screws as my screwdriver set, but it will cut apples better than the screwdrivers, and open wine bottles, and scale fish, and so on.
And yeah, I'm going on and on and on when you've already got the point. 😀
Either specialize queries that really need it, or live with the performance issues on the view, or see if you can fiddle with the view to force a recompile that works better for this query (but realize that might break other queries that currently do work well). I'd specialize the exception queries, but that's just a personal preference, not a law of nature.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2010 at 7:08 am
I understand. Thanks for taking a look at this with me and have an awesome day!!
November 10, 2010 at 1:29 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply