November 29, 2015 at 10:07 pm
I have a 3rd Party app that produces queries which kill our server. The vendor has "discontinued" development so support is non-existent.
The problem query is of the form:
SELECT <fields>
FROM TableA, TableB, TableC
WHERE
Table1A.Aid = TableB.Aid
and TableB.Bid = TableC.Bid
If I rearrange this, it is snappy:
SELECT <fields>
FROM TableA
inner join TableB
on Table1A.Aid = TableB.Aid
inner join TableC
on TableB.Bid = TableC.Bid
I've been looking at query plans as the suggested method to improve vendor applications, but it doesn't seem to allow me to change the overall syntax, only suggest query hints.
Any advice?
November 29, 2015 at 11:21 pm
Plan forcing can't change the T-SQL syntax. Nothing can, other than rewriting the T-SQL syntax.
Sorry.
"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
November 29, 2015 at 11:58 pm
Correct, plan forcing can't change the query itself. The way plan forcing works is that the optimiser will optimise the query until it finds the plan being forced. If it doesn't, the query fails with an error.
There's no way to change syntax without changing the call. That said, those two queries should be completely equivalent. Can you post the plans that the two have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2015 at 4:01 pm
GilaMonster (11/29/2015)
...That said, those two queries should be completely equivalent. Can you post the plans that the two have?
Well, that was kinda my angle: since "those two queries should be completely equivalent" I can use the snappy plan and the other should plug in. It's a new area for me as ordinarily I'd just fix the code.
I can post the plans as attachments. How do you prefer them? The XML is rather long or would you be happy with a screen grab?
November 30, 2015 at 5:06 pm
Save the execution plans as .sqlplan files and upload those.
November 30, 2015 at 6:35 pm
Lynn Pettis (11/30/2015)
Save the execution plans as .sqlplan files and upload those.
Well, that seems obvious in hindsight. Plans attached.
Snappy plan is the first, whilst the problem plan is the second. I have obfuscated naming.
November 30, 2015 at 10:46 pm
For the slow query the optimizer is not seeing the old ANSI '89 style joins as having a join predicate. Everything goes south from there. I'm not entirely sure why that's the case, but that's what's going on. The estimated data size from the second query is 283tb. I'm assuming that's a little off considering the other one estimates a few kb.
The issue is still only going to be solved by changing the code (which I know you can't do). There's no way to get a plan to force on top of this without using the same T-SQL. You could attempt query hints with plan guides, but I'm not sure which, if any, would work best here.
"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
December 1, 2015 at 6:47 pm
Grant Fritchey (11/30/2015)
For the slow query the optimizer is not seeing the old ANSI '89 style joins as having a join predicate. Everything goes south from there. I'm not entirely sure why that's the case, but that's what's going on. The estimated data size from the second query is 283tb. I'm assuming that's a little off considering the other one estimates a few kb.The issue is still only going to be solved by changing the code (which I know you can't do). There's no way to get a plan to force on top of this without using the same T-SQL. You could attempt query hints with plan guides, but I'm not sure which, if any, would work best here.
Thanks Grant.
I did some more googling with what you've mentioned and found an article [/url] by some guy calling himself "The Scary DBA" 😛 that explains the syntax hasn't worked since SQL Server 2008.
Looking at the query plan again I notice that instead of joining A-B-C the optimizer is trying to join A-C then B, which explains why there's no join predicate with the linking table missing. I thought I could look at using the "option (force order)" as a query plan to correct the order and although it joins A-B-C it still complains of the No Join Predicate.
Although I can't change the code, I may have some leeway changing the database. The vendor application drills *another* vendor's reporting database, which is recreated completely each night. I tried jiggling keys, indexes, references to strongly suggest a join on the db side, to no effect. Am I barking up the wrong tree?
Google is pretty sparse on options to resolve this issue, pretty much stating "recode using SQL-92 syntax." Is there anything I can do database-side to help the optimizer see the light?
December 1, 2015 at 10:07 pm
Not that I know of. FORCE ORDER might do the trick. Forcing HASH or LOOP might also work. Experimentation is probably the only hope. It's just bad code.
"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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply