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?