January 16, 2008 at 1:13 pm
The two things I saw that stood out was that there was a join without a predicate. That's usually not a good thing. Joins, for the most part, should have criteria on which to join. Also, the biggest cost in the plan I was looking at was a RIGHT JOIN doing a HASH between the Person and Customer tables.
The plan that you've labeled LEFT JOIN doesn't have the missing join predicates. That sure suggests more changes between these two pieces of TSQL than simply changing one join from INNER to LEFT.
Plust what everyone else has said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2008 at 1:35 pm
Megistal (1/16/2008)
it is computed by view which call views which call views and so on.
Ah. That's useful to know. I was assuming it was a straight join between two tables.
Views atop views atop more views is not recommended at the best of times. As a query gets more and more complex (as multiple layers of views tend to) so the chances of the optimiser picking the best execution plan decreases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2008 at 2:14 pm
I've updated statistics on all tables and views and it didn't help.
I didn't saw the 3.5 billions row join in the plan. That's sure not helping the query!
What's sad (but common in enterprise) as soon as someone here did find the left join was working, they closed the issue right away 🙁
Anyway, thanks everyone for all your helps. I'll still try to know exactly where I could optimize to avoid 3.5 billions rows but that would be on my spare time as it is already...
However my curiosity still tell me to find out why SQL best execution plan comes out with 3.5 billions rows. Does that mean that all other plan are... worse? Is there a way to look at others estimated execution plan?
January 16, 2008 at 2:35 pm
Grant, myself and a few others have all mentioned that you're missing a JOIN predicate in the "inner join". It's not the optimizer - the query itself is flawed. The outer join (or left) doesn't have that problem becuase the join criteria is present, so your conclusion that INNER JOIN isn't working correctly is , well, incorrect. The query itself is the problem.
the 3.5 Trillion (not Billion, TRILLION) records is just a clue of that. It's a classic symptom to look for ( your record count bring way out of whack usually means you screwed up).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 2:42 pm
Which is also why we keep asking to see the query.
January 16, 2008 at 2:58 pm
Apart from the missing join predecate I would also start assessing how many records each of the participating tables have in them in total.
Are the actual table objects that are being joined the correct ones to join. For example, I've seen instances where two tables have been joined when in actual fact they should have been joined via an intermediate table. That is, the incorrect join yielded an open ended many to many relationship rather than a proscribed set of records.
Also, there used to be a case when a condition placed in the join rather than in the where clause sometimes caused the query optimiser to choose a different query plan. I haven't seen this happen in SQL2005 but it definitely happens in SQL2000.
Before I get flamed, yes I know that a condition in a LEFT/RIGHT join does this as a matter of course, but I am explicitly talking about INNER JOINs.
If your query is too big then break it down into smaller section, possibly pushing data into intermediary or temporary tables.
For small recordsets just joining tables together is the most efficient route, but when you are talking about millions of records, pre-selecting might be the better option.
January 16, 2008 at 3:55 pm
I'm still surprised we're discussing this! David I agree - seen some strange things in 2000 too, will let you know if I find them in 2005.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 29, 2008 at 1:17 pm
If I remember, at the bottom, 3 main tables were used. Each one having around 7000 entries in each (at most 32k rows). So we were not talking about millions records tables.
It's not much of a big DB. However, this issue was closed as soon as the "inner join" solution was found. I wanted to know more about the issue, hence this thread but I was assigned "physically" elsewhere now so I can't provide anymore information about it.
Thank you for all your help
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply