January 15, 2008 at 9:08 am
First of all, yes I know what's the difference between an inner join and a left join just in case some might wonder ehhe
Here's what happened, I was ask to look up upon a query which crashed an application. That query, would run endlessly.
That query does not update, insert or delete any rows in any table.
On my test machine, the tempDB fill up the entire test drive (9 gigs) just under 15 minutes.
With the profiler, I checked Deadlock and lock escalation, just in case, no event from the profiler.
Found that the query use Views that use views that use views which use tables that are already use by other views in the same query. (Redundancy)
In the activity monitor, I've remark that a lot of lock "IS" come from the DB holding the query I'm testing on and "IX" from tempDB
None of the involved underlying tables use triggers
The main view that return the information use "Inner Join" with other underlying views for it's data. However, when all "Inner Join" are modified by "Left Join" (the architect / creator of the application says that the information return by this modification will not impact the validity of the query) the query run flawlessly under 500 ms (TIME STATISTICS)
No index were recreated, no modification. Restored the database and rerun with same results.
tempDB didn't grow a bit.
One last thing, the estimated query plan is somewhat changed when comparing the query "inner join" and the same query with "left join".
Any ideas why changing "Inner Join" by "Left Join" would change radically a query?
Thank you
Thank you
January 15, 2008 at 4:17 pm
I really think BOL and a book on SQL would be a good place to start. Sorry but if you don't know join theory you shouldn't be let anywhere near a rdbms.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 5:35 am
When you say "somewhat changed" what exactly?
Have you updated statistics or, better still, rebuilt your indexes in recent memory?
"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 6:11 am
None of these
First I've reloaded a backup of the DB, then I've requested an estimated execution plan with the original query using inner join and check it.
Second, I've reloaded the same backup as in the first step, then I've modified the view to replaces Inner Join by left join and requested again an estimated execution plan
Third I've compared both plan and I've noticed that some part of the plan are similar while others use different algorithm for the same end result.
No other step were involved to avoid changing state that could compromise the estimated plan
(Estimate plan is use because the query using inner join never end so I can't have the final plan once the query complete hence estimate plan are compared for both queries)
January 16, 2008 at 6:27 am
Colins
Inner Join returns only rows that exist in both tables satisfying the join condition
Left Outer Join return all rows from the left join table whether or not there are any matching rows in the right table.
Does my of joins definitions are satisfying?
What I want to know is how in the code those join are made and their collateral effects. For example does for an inner join SQL lock all matching rows one by one which could lead to lock escalation for a lot of result while a left join SQL know that the entire table must be lock because the left table will return all rows anyway? (an S lock, a shared one)
That's the kind of information I'm looking for.
If this is not the case, why a left join perform near instantly while an inner join take age and crash tempDB?
January 16, 2008 at 6:40 am
If you haven't rebuilt the indexes on the this system in recent memory, I'd start there (and establish a mechanism that does it on a regular basis. There are several scripts available here[/url]).
After you've eliminated fragementation of the indexes in question and have more up to date statistics (rebuilding the index fixes those too) then you can get new estimated plans. BTW, you don't need to do two seperate restores to see the plan. Do a restore, compile the view under one name and under a different name (MyView & MyViewa, for example). Now look at the plans. Don't simply note that they are different, get the specific differences and let us know. Also, after rebuilding the indexes, I'd try rerunning the query.
The thing is, an inner join and an outer join will result in different plans. It's those differences that matter. Simply saying they're different isn't enough for us to help. We need to know specifically what the differences are. But first, rebuild the indexes.
"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 6:43 am
I've gave it a new try with indexes.
All underlying tables had clustered index on their primary key. As the Microsoft rule of thumb it is their preferred place. (I know it's not always true but tables were built that way)
I've also added indexes to fully returns data only using indexes, covering indexes on all underlying tables.
Performance was increase for the left join query but so slightly just for read operations that the overhead of managing those indexes on writes operations would anyway degrade performance.
(Both gone through the same rigorous testing to avoid cached plan, background services, connections etc)
Of course, with or without indexes the Inner Join query still took age and crash at the end.
So I concluded that inappropriate or bad-formed indexes is not the root cause of the issue.
January 16, 2008 at 6:45 am
Hi Grant Fritchey
I'll do as you say and post both plan
January 16, 2008 at 6:54 am
Rebuild the indexes on the tables involved, see if that makes a difference. If not, please save the two estimated plans (as .sqlplan), zip them and attach to a post please. Also, please post the query.
Usually, inner join and left join can use the same physical operations and perform fairly similarly
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 12:26 pm
From estimated execution plan, rebuilding indexes seem to help but the inner join query still have the issue while left join doesn't have.
Here's the zip file containing all estimated execution plans.
I'll try to get the whole query (with the profiler ?) as it is computed by view which call views which call views and so on. So giving only one view layer will probably not give enough insight to find the issue.
January 16, 2008 at 12:28 pm
Oh
This is the query I used to rebuild all indexes in the database:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
January 16, 2008 at 12:39 pm
Apparently your two joins aren't equivalent, because in one case (the inner join) the "stream aggregate" step estimates the rowcount to be 3.5 Trillion rows (that's 3 commas in there). The outer join on the other hand seems to think there should be only 9700 rows.
I'd say that alone might make a little difference:)
I'd say it's a matter of you missing join criteria and not just changing the word OUTER to INNER.
----------------------------------------------------------------------------------
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 12:41 pm
Of course - the plan might be lying, but you haven't posted the query, so we're not going to be able to tell you that..... Just a thought.
----------------------------------------------------------------------------------
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 12:41 pm
The only thing I can think of off the top of my head on this one is that the statistics on the tables are a mess. That might cause the inner joins to use inefficient join methods. There might also be a saved execution plan that could be way off. You could try updating the statistics on the tables, or flushing the execution cache, and see if that helps.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 12:56 pm
You may also want to read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/
It would help if you posted your code, provided the DDL for the tables involved, and some sample data.
😎
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply