Amazingly Stupid Query

October 24, 2008 at 1:27 pm (TSQL) (, , )


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.

4 Comments

  1. DavidB said,

    …. 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. :)

  2. Gail said,

    Perhaps the question should be were they thinking at all?

  3. scarydba said,

    It gets better. I’ve found two more.

  4. scarydba said,

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: