Execution plan differences
-
I've been working on a new client-site with over 4000 stored procedures that were reverse-engineered from an Oracle application (into SQL 2k sp3), and have been handling the task of tuning many of the under performers. One quirk to the reverse-engineering solution they used is that the syntax of the JOIN relationships expressed appears a little hokey. Recently I posted a topic about the behavior of joins nested within each other and answered my own question in that thread. I wanted to post this demo version of three queries that are functionally identical but behave differently under the optimizer and see if any of the bigger brains out here can help me understand #1 - why, and #2 - which method is preferable in most scenarios.
The full script to run this test on your own is below. The first select statement is the method the Oracle reverse-engineering tool ports out. The second is how I would write the query given my natural preference. Number 3 has an identical execution plan and statistics as Number 1, and helps me verify that when you nest the joins it treats them as sub-queries. Enjoy!
set nocount on
create table #tMain ([id] int identity(1,1), filler varchar(20))
create table #tIn1 ([id] int identity(1,1), main_id int, lookup_id int)
create table #tIn2 ([id] int identity(1,1), type_id int, t1_id int, text_value varchar(20))
create table #tIn3 (t2_id int)
create table #tIn4 ([id] int identity(1, 1), type_desc varchar(20))
create table #tIn5 ([id] int identity(1, 1), t2_id int, create_date datetime, curr_rec int)
create table #tIn6 (t5_id int, type_id int)
insert into #tMain (filler) values('Filler1')
insert into #tMain (filler) values('Filler2')
insert into #tMain (filler) values('Filler3') -- **
insert into #tMain (filler) values('Filler4')
insert into #tMain (filler) values('Filler5')
insert into #tIn1 (main_id, lookup_id) values(1, 1)
insert into #tIn1 (main_id, lookup_id) values(2, 1)
insert into #tIn1 (main_id, lookup_id) values(3, 2) -- **
insert into #tIn1 (main_id, lookup_id) values(4, 2)
insert into #tIn1 (main_id, lookup_id) values(5, 5)
insert into #tIn4 (type_desc) values('Lookup1') -- **
insert into #tIn4 (type_desc) values('Lookup2') -- **
insert into #tIn4 (type_desc) values('Lookup3')
insert into #tIn4 (type_desc) values('Lookup4')
insert into #tIn4 (type_desc) values('Lookup5')
insert into #tIn2 (type_id, t1_id, text_value) values(1, 3, 'One') -- **
insert into #tIn2 (type_id, t1_id, text_value) values(2, 3, 'Two') -- **
insert into #tIn2 (type_id, t1_id, text_value) values(3, 2, 'Three')
insert into #tIn2 (type_id, t1_id, text_value) values(4, 4, 'Four')
insert into #tIn2 (type_id, t1_id, text_value) values(5, 5, 'Five')
insert into #tIn3 (t2_id) values(1)
insert into #tIn3 (t2_id) values(2)
insert into #tIn3 (t2_id) values(3) -- **
insert into #tIn3 (t2_id) values(4)
insert into #tIn3 (t2_id) values(5)
insert into #tIn5 (t2_id, create_date, curr_rec) values(1, getdate() - 15, 1) -- **
insert into #tIn5 (t2_id, create_date, curr_rec) values(2, getdate() + 30, 3)
insert into #tIn5 (t2_id, create_date, curr_rec) values(3, getdate(), 1)
insert into #tIn5 (t2_id, create_date, curr_rec) values(4, getdate(), 2)
insert into #tIn5 (t2_id, create_date, curr_rec) values(5, getdate(), 1)
insert into #tIn6 (t5_id, type_id) values(1, 1004)
insert into #tIn6 (t5_id, type_id) values(1, 1005) -- **
insert into #tIn6 (t5_id, type_id) values(3, 1000)
insert into #tIn6 (t5_id, type_id) values(4, 999)
insert into #tIn6 (t5_id, type_id) values(2, 565)
dbcc freeproccache
dbcc dropcleanbuffers
-- First Query, execution plan is SAME as third query but written differently
select t2.text_value as return_val
from #tMain m
inner join #tIn1 t1
inner join #tIn2 t2
inner join #tIn3 t3 on t2.[id] = t3.t2_id
inner join #tIn4 t4 on t2.type_id = t4.[id]
inner join #tIn5 t5
inner join #tIn6 t6 on t5.[id] = t6.t5_id
on t2.[id] = t5.t2_id
on t1.[id] = t2.t1_id
on m.[id] = t1.main_id
where t1.lookup_id = 2
and t2.[id] = 1
and m.[id] = 3
and t6.type_id != 1004
and t5.create_date < getdate()
and t5.curr_rec = 1
dbcc freeproccache
dbcc dropcleanbuffers
-- Second Query, same results but differrent execution plan than First or Third version
select t2.text_value as return_val
from #tMain m
inner join #tIn1 t1 on m.[id] = t1.main_id
inner join #tIn2 t2 on t1.[id] = t2.t1_id
inner join #tIn3 t3 on t2.[id] = t3.t2_id
inner join #tIn4 t4 on t2.type_id = t4.[id]
inner join #tIn5 t5 on t2.[id] = t5.t2_id
inner join #tIn6 t6 on t5.[id] = t6.t5_id
where t1.lookup_id = 2
and t2.[id] = 1
and m.[id] = 3
and t6.type_id != 1004
and t5.create_date < getdate()
and t5.curr_rec = 1
dbcc freeproccache
dbcc dropcleanbuffers
-- Third Query, written as though each nested step in were a subquery - same execution plan as first query!
select x1.text_value as return_val
from #tMain m
inner join (
select t1.lookup_id, t1.[id], t1.main_id, x2.text_value, x2.[id] as x2_id
from #tIn1 t1
inner join (
select t2.[id], t2.t1_id, t2.text_value from #tIn2 t2
inner join #tIn3 t3 on t2.[id] = t3.t2_id
inner join #tIn4 t4 on t2.type_id = t4.[id]
inner join (
select t5.create_date, t5.curr_rec, t6.type_id, t5.t2_id
from #tIn5 t5
inner join #tIn6 t6 on t5.[id] = t6.t5_id
and t6.type_id != 1004 and t5.create_date < getdate()
and t5.curr_rec = 1
) x5 on x5.t2_id = t2.[id] and t2.id = 1
) x2 on t1.[id] = x2.t1_id and t1.lookup_id = 2
) x1 on m.[id] = x1.main_id
where m.[id] = 3
drop table #tMain
drop table #tIn1
drop table #tIn2
drop table #tIn3
drop table #tIn4
drop table #tIn5
drop table #tIn6
go
-
I ran your script but all 3 plans were exactly the same.
What version of sql server are you running?
What is the database compatibility level of the database?
Can you post the original code along with the execution plans?
Also I've always use instead of != in sql server (not sure it's a best practice but I think that != is the old syntaxe... and could become unsuported someday).
-
No idea why you are getting the same execution plan for the queries... As stated above we're on SQL 2k SP3. Compatability level is 80 (default). Here are the execution plans for the three queries I provided above. (thanks for the tip on !=
).-- First Query execution plan
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join)
| |--Hash Match(Inner Join, HASH
[t2].[type_id])=([t4].[id]), RESIDUAL[t2].[type_id]=[t4].[id]))| | |--Hash Match(Inner Join, HASH
[t1].[id])=([t2].[t1_id]), RESIDUAL[t1].[id]=[t2].[t1_id]))| | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn1_______________________________________________________________________________________________________________0000000383E6] AS [t1]), WHERE[t1].[main_id]=3 AND [t1].[lookup_id]=2))| | | |--Nested Loops(Inner Join)
| | | |--Hash Match(Inner Join, HASH
[t5].[id])=([t6].[t5_id]), RESIDUAL[t5].[id]=[t6].[t5_id]))| | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn5_______________________________________________________________________________________________________________0000000383E6] AS [t5]), WHERE([t5].[t2_id]=1 AND [t5].[curr_rec]=1) AND [t5].[create_date]<getdate()))| | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn6_______________________________________________________________________________________________________________0000000383E6] AS [t6]), WHERE[t6].[type_id]<>1004))| | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn2_______________________________________________________________________________________________________________0000000383E6] AS [t2]), WHERE[t2].[id]=1))| | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn4_______________________________________________________________________________________________________________0000000383E6] AS [t4]))| |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn3_______________________________________________________________________________________________________________0000000383E6] AS [t3]), WHERE[t3].[t2_id]=1))|--Table Scan(OBJECT
[tempdb].[dbo].[#tMain______________________________________________________________________________________________________________0000000383E6] AS [m]), WHERE[m].[id]=3))-- Second Query execution plan
|--Hash Match(Inner Join, HASH
[t2].[type_id])=([t4].[id]), RESIDUAL[t2].[type_id]=[t4].[id]))|--Hash Match(Inner Join, HASH
[t1].[id])=([t2].[t1_id]), RESIDUAL[t1].[id]=[t2].[t1_id]))| |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn1_______________________________________________________________________________________________________________0000000383E6] AS [t1]), WHERE[t1].[main_id]=3 AND [t1].[lookup_id]=2))| |--Nested Loops(Inner Join)
| |--Nested Loops(Inner Join)
| | |--Nested Loops(Inner Join)
| | | |--Nested Loops(Inner Join, WHERE
[t5].[id]=[t6].[t5_id]))| | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn6_______________________________________________________________________________________________________________0000000383E6] AS [t6]), WHERE[t6].[type_id]<>1004))| | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn5_______________________________________________________________________________________________________________0000000383E6] AS [t5]), WHERE([t5].[t2_id]=1 AND [t5].[curr_rec]=1) AND [t5].[create_date]<getdate()))| | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tMain______________________________________________________________________________________________________________0000000383E6] AS [m]), WHERE[m].[id]=3))| | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn3_______________________________________________________________________________________________________________0000000383E6] AS [t3]), WHERE[t3].[t2_id]=1))| |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn2_______________________________________________________________________________________________________________0000000383E6] AS [t2]), WHERE[t2].[id]=1))|--Table Scan(OBJECT
[tempdb].[dbo].[#tIn4_______________________________________________________________________________________________________________0000000383E6] AS [t4]))-- Third Query execution plan
|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join)
| |--Hash Match(Inner Join, HASH
[t2].[type_id])=([t4].[id]), RESIDUAL[t2].[type_id]=[t4].[id]))| | |--Hash Match(Inner Join, HASH
[t1].[id])=([t2].[t1_id]), RESIDUAL[t1].[id]=[t2].[t1_id]))| | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn1_______________________________________________________________________________________________________________0000000383E6] AS [t1]), WHERE[t1].[lookup_id]=2 AND [t1].[main_id]=3))| | | |--Nested Loops(Inner Join)
| | | |--Hash Match(Inner Join, HASH
[t5].[id])=([t6].[t5_id]), RESIDUAL[t5].[id]=[t6].[t5_id]))| | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn5_______________________________________________________________________________________________________________0000000383E6] AS [t5]), WHERE([t5].[t2_id]=1 AND [t5].[curr_rec]=1) AND [t5].[create_date]<getdate()))| | | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn6_______________________________________________________________________________________________________________0000000383E6] AS [t6]), WHERE[t6].[type_id]<>1004))| | | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn2_______________________________________________________________________________________________________________0000000383E6] AS [t2]), WHERE[t2].[id]=1))| | |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn4_______________________________________________________________________________________________________________0000000383E6] AS [t4]))| |--Table Scan(OBJECT
[tempdb].[dbo].[#tIn3_______________________________________________________________________________________________________________0000000383E6] AS [t3]), WHERE[t3].[t2_id]=1))|--Table Scan(OBJECT
[tempdb].[dbo].[#tMain______________________________________________________________________________________________________________0000000383E6] AS [m]), WHERE[m].[id]=3)) -
You obviously have different plans but since I don't see the same thing as you do it's gonna be hard for me to help.
Also I'm confirming that ! and != are not ainsi 92 standard.
I would normally say that you could update the stats or reindex because the optimizer should always use the same plan in this situation but since we create the table right before it cannot be that.
I'll have to let the real gurus take this one. I can't think of anything that could be causing this problem, besides the stats being out of date, parameter sniffing or multiple plans being cached at the same time. But you already ruled all those out.
Anything else???
-
I agree that the optimizer *should* use the same plan every time, but it's obvious (on my end) that it treats the first and third syntax style the same but handles #2 very differently. This SQL instance is a named instance on a 32-way Unisys box (partitioned out to 8 processors / 4GB ram on this particular instance). The implementation of SQL Server is basically default and in mixed-mode. None of these facts seem relevant to the execution plan differences [that I see and you don't], but I'm just trying to rule things out.
-
If it's a 32 way box, maybe the server is using parallelism on the 2nd plan...
what is the cost of the 2nd query compared to the 2 others (pourcentage).
-
I already considered parallelism as the deviation here but don't think it's the case. None of the execution plans reveal a parallelism step, and if I explicitly force a MAXDOP option hint not allowing for multi-processor I get the same execution plans.
According to the showplan estimations, the total cumulative subtree cost of the first query is 0.31806996 and the second is 0.30079865. With the statistics profile on, the actual costs are 0.31806996 and 0.30079865 respectively. As for the IO statistics, all tables have a scan count of 1 and logical read of 1 -- EXCEPT -- for the second query, which has a count of 4 for the table #tIn5. Despite this, the second query appears to have a lower cost.
-
Looks like I can't help you. Maybe somebody else will, but if we can't it probabely means that you have found a bug in sql server... wouldn't be the first one.
-
I only have SQL Server 2005 to test with here, but I am getting the same results as Cris. The cost for the first and third queries are each 36% of the batch, and the second query is 28%. It's interesting that this does not happen with Remi's machine. I am guessing that in your case Remi the optimizer continues to evaluate plans and manages to find the one used for the second query also when evaluating the first and third queries. In Cris' case it stops evaluating earlier and goes with the plan it has, which happens to be a less effective plan.
-
This is annoying... I'll try this query on other servers here... but they are all 2k, with different services packs. Maybe I'll see a difference then.
-
What'll really blow your mind is that the sample I created here always results in the 2nd query running more efficiently (which I love to hear, since that is my natural preference / style) - but in the real world example I have - the 1st query is ALWAYS slightly better.
The nesting level and data is similar to the real world example, except that the real plan involves hundreds of thousands of records and resides in indexes tables. It bugs me because now I'm always going to have to test two ways of writing a basic select statement involving joins just to "be sure" that I'm getting the best performance... and I can't explain why the optimizer chooses the different plans!
-
Cris have you tried switching the order of the statements when testing. i.e. test statement 2 then 1 then 3
Mike
-
Yep, doesn't seem to impact the optimizers chosen path on each statement. #1 and #3 are treated identically; #2 is the special case regardless of execution order. I also perform the DBCC statements to force the optimizer to determine the most appropriate path each time.
From a functional stand-point there doesn't seem to be a big deal here, but (and it's a BIG but) if any of the nested joins are outer - the results WILL be different. This phenemenon was discussed in my prior thread where I first determined that it treated the nested joins as subquerys instead of true individual members of the outer primary query. The only weird difference is that you have access to all of the tables being treated as subqueries in the WHERE clause of the primary query.
-
I tried on all my servers and still no difference (all sql server 2000 but with different service packs (3+)). So I'm officially out of ideas.
Have you contacted Microsoft about this?
-
Hi,
I have the same behavior as Cris. We are on SQL2K SP3A.
Regards,
Carl
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply