June 2, 2014 at 2:03 pm
So, just having had to kill a couple instances of a query that was killing one of my QA servers, I started looking at the query plan and the query itself...
A rough idea as to what the query was doing:
select distinct item.number
, item.model
, model.model
, security.hr_number
, item_category.category
from items, security, item_category, model
After some poking around, I noticed the estimated query plan was guessing it was going to return ~3 billion records.
So, I re-wrote the query using inner joins, and it returns 36K rows in about 2 seconds...
With a query such as the problem one, the execution plan had a couple inner joins (no where predicate.) Cartesian joins, then?
At this point, that's what the guy in charge of the developers is thinking, and is kicking the query back to them for a re-write...
Thanks,
Jason
June 2, 2014 at 2:07 pm
Seems like cartesion joins to me.
All the more reason I dislike writing inner joins that way. Too easy to forget the predicates.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 2, 2014 at 2:08 pm
If there's no WHERE clause, then those are CROSS JOINS producing cartesian products. Without join conditions, that will only return a lot of bad information.
Kill the one that wrote that monstruosity.:-)
June 2, 2014 at 2:12 pm
Looks like a Cartesian product to me as well.
Looks like someone was writing the query using the ANSI-89 joins but forgot the WHERE clause to properly restrict the result set.
June 2, 2014 at 2:16 pm
Lynn Pettis (6/2/2014)
Looks like a Cartesian product to me as well.Looks like someone was writing the query using the ANSI-89 joins but forgot the WHERE clause to properly restrict the result set.
Cartesian products sounds like such a novely name.
They should think of something more evil to name that kind of join.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 2, 2014 at 2:23 pm
Koen Verbeeck (6/2/2014)
Lynn Pettis (6/2/2014)
Looks like a Cartesian product to me as well.Looks like someone was writing the query using the ANSI-89 joins but forgot the WHERE clause to properly restrict the result set.
Cartesian products sounds like such a novely name.
They should think of something more evil to name that kind of join.
But it isn't evil. We use Cartesian products to generate dynamic tally tables.
June 2, 2014 at 2:23 pm
Koen Verbeeck (6/2/2014)
Lynn Pettis (6/2/2014)
Looks like a Cartesian product to me as well.Looks like someone was writing the query using the ANSI-89 joins but forgot the WHERE clause to properly restrict the result set.
Cartesian products sounds like such a novely name.
They should think of something more evil to name that kind of join.
maybe "Its Arcane"......anagram 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 2, 2014 at 2:33 pm
Lynn Pettis (6/2/2014)
Koen Verbeeck (6/2/2014)
Lynn Pettis (6/2/2014)
Looks like a Cartesian product to me as well.Looks like someone was writing the query using the ANSI-89 joins but forgot the WHERE clause to properly restrict the result set.
Cartesian products sounds like such a novely name.
They should think of something more evil to name that kind of join.
But it isn't evil. We use Cartesian products to generate dynamic tally tables.
Darth Vader wasn't always evil... 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 3, 2014 at 5:19 am
Thanks all!
Yeah, the original *is* a monstrosity, it's the first time on my servers I've seen a query stalled out on CXPACKET waits...
Should be interesting to see what comes up next...
June 3, 2014 at 6:01 am
jasona.work (6/3/2014)
Thanks all!Yeah, the original *is* a monstrosity, it's the first time on my servers I've seen a query stalled out on CXPACKET waits...
Should be interesting to see what comes up next...
CURSORS! CURSORS EVERYWHERE! 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 3, 2014 at 6:29 am
... and this is why I prefer to use ANSI-92 syntax!
Where I work now we use Sybase ASE (I miss SQL Server so much boohoo!) and all the code is written using the older SQL syntax which is a pain in the backside IMHO. Every time I'm required to debug a sproc I'm having to rewrite the code using ANSI-92 joins to understand what it's doing.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply