I have to share this one.
I’m working on a set of queries, tuning them. They’re all following a similar pattern. They’re passing in XML data sets which are then shredded into a temporary table. Once that’s done, the temp table is used in a series of joins to other tables to return the data. Some of the queries were missing indexes, some were structured a bit poorly, but overall, it was pretty standard stuff. Until…
I ran a query that looked, at first glance, the same as the rest. When I looked at the execution plan, I saw a warning symbol, one of those little exclamation points on an operator. I figured, based on the other issues with this database, that it was just some out of date or missing statistics. When I looked at the tooltip it said, “NO JOIN PREDICATE.” Huh? What the…
So I looked at the query. I’m changing what I saw, just a bit, so I don’t violate any of my business data:
SELECT DISTINCT x.MyValue
FROM #Temp t
INNER JOIN (SELECT a.MyValue, b.xvalue, c.yvalue
FROM Atable a, Btable
INNER JOIN Ctable….) AS x
ON x.xvalue = t.xvalue
AND x.yvalue = t.yvalue
Clearly Atable needs some join criteria. So I look through the hole query. It’s joining about 8 different tables and none of them refer to Atable. None of them have referential constraints with Atable. Atable is just glommed on in a cartesian join, everything with everything. Clearly this is messed up, but, as you can see, the whole point of the excercise is to get a value out of Atable. I look at the data in Atable. There are over 100 rows. In all those rows, the interesting value from Atable is identical, on each and every row. Suddenly, the DISTINCT makes sense. The rest of the query doesn’t. They joined all kinds of tables together and passed in parameters all to return a single value that can never be anything else, regardless of the parameters passed in. Further, regardless of the value of the column in ATable, they’re only ever going to get all the rows of ATable, again without regard to parameters.
I’m awaiting word from the development team to find out just what the **** they were thinking.