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.
…. oh to be a fly on the wall when the call comes to the developer… Hopefully not catastrophic for them but definitely should be educational. 🙂
Perhaps the question should be were they thinking at all?
It gets better. I’ve found two more.
If anyone is interested, I got the word back from the developers… They put that table in because their “requirements” were a list of tables and a list of columns. When the developers in question saw referential constraints, they used them, when they didn’t, they just put the tables in anyway because the requirements called for them. No questioning or examination of the requirements, no thought at all, so Gail hit it on the head in one. They weren’t thinking.