May 27, 2005 at 2:48 pm
Hey all, got a query here that was reverse ported out of an Oracle app before I arrived out on customer site and the structure leads me to believe it's behaving more like a nested subquery arrangment than straight-forward joins. Here's an idea of how it is structured:
select <RETURN LIST>
from Table1 t1
inner join Table2 t2
inner join Table3 t3
inner join Table4 t4 on t3.Col3 = t4.Col3
on t3.Col2 = t2.Col2
on t2.Col1 = t1.Col1
where <SARGS>
Of course the actual query nests much deeper (down to 11 levels at one point) and involves a total of 18 tables, so the above is a simplification for discussion purposes. The structure of nesting sub-tables in this manner reminds me of Access, but I wasn't sure what kind of behavior to expect out of SQL Server. In a first pass attempt to rewrite it using a more readable approach (like below), I did not get back the same results. Any advice would be appreciated!
select <RETURN LIST> from Table1 t1 join Table2 t2 on t1.Col1 = t2.Col1 join Table3 t3 on t2.Col12= t3.Col2 join Table4 t4 on t3.Col13= t4.Col3 where <SARGS>
Thanks for the help!
May 28, 2005 at 7:31 am
Hi I can not reproduce your problem. checking in QA I get the same execution plans for both queries.
Mike
IF Object_ID('TempDb..#T1') >0
DROP TABLE #T1
IF Object_ID('TempDb..#T2') > 0
DROP TABLE #T2
IF Object_ID('TempDb..#T3') >0
DROP TABLE #T3
IF Object_ID('TempDb..#T4') >0
DROP TABLE #T4
CREATE TABLE #T1
(
Col1 int, --PK relates to col2 T2
Data VarChar(50)
)
INSERT INTO #T1(COL1,DATA)VALUES(1,'Hello World')
INSERT INTO #T1(COL1,DATA)VALUES(2,'ANOTHER VALUE')
CREATE TABLE #T2
(
Col1 int, --Relates to T1
Col2 int, --Relates to T3 Col2
Data Char(5)
)
INSERT INTO #T2(Col1,Col2,Data)VALUES(1,2,'T2')
INSERT INTO #T2(Col1,Col2,Data)VALUES(3,2,'T2')
INSERT INTO #T2(Col1,Col2,Data)VALUES(3,2,'T2')
CREATE TABLE #T3
(
Col1 int, --
Col2 int, --Relates to t2 col2
Col3 int, --Relates to t4 col3
Data Char(5)
)
INSERT INTO #T3(COL1,COL2,COL3,DATA) VALUES(3,2,4,'T3')
CREATE TABLE #T4
(
Col1 int,
Col2 int,
Col3 int, --Relates to T3 col 3
Data Char(5)
)
INSERT INTO #T4(COL1,COL2,COL3,DATA) VALUES(4,5,4,'T4')
select T1.*
from #T1 t1
inner join #T2 t2
inner join #T3 t3
inner join #T4 t4 on t3.Col3 = t4.Col3
on t3.Col2 = t2.Col2
on t2.Col1 = t1.Col1
select T1.*
from #T1 t1
join #T2 t2 on t1.Col1 = t2.Col1
join #T3 t3 on t2.Col2= t3.Col2
join #T4 t4 on t3.Col3= t4.Col3
May 31, 2005 at 3:13 pm
I was able to reproduce the problem with a simpler example - hopefully this gives some insight into where my problem was. Apparently this nested JOIN feature doesn't matter on INNER type joins, but one of the 17 nested tables I was attempting to work with was expressed as a LEFT outer followed by an INNER join on that outer table.
If the INNER relationship is expressed to the outer table BEFORE the ON condition is explicitly defined for the outer table, it is treated identical to the execution plan of a subquery. My mistake was enumerating the ON conditions of the outer table followed by performing an inner join on that same table - resulting in the elimination of the OUTER condition to begin with. For your enjoyment here's how I duplicated it:
set showplan_text on
go
set nocount on
if object_id('tempdb..#tCustomer') > 0
drop table #tCustomer
if object_id('tempdb..#tCustomerStatus') > 0
drop table #tCustomer
if object_id('tempdb..#tCustPOs') > 0
drop table #tCustomer
if object_id('tempdb..#tCity') > 0
drop table #tCustomer
create table #tCustomer (
customerid int identity(1, 1) not null
, customername varchar(50) not null
, customerstatus int not null
, customer_po varchar(50) null
)
create table #tCustomerStatus (
customerstatus int identity(1, 1) not null
, statusname varchar(30) not null
)
create table #tCustPOs (
customer_po varchar(50) not null
, customer_description varchar(100) null
, city varchar(50) null
, isactiveflag bit not null
)
create table #tCity (
city varchar(50) not null
, zipcode char(10) null
)
insert into #tCity values('SQLVille', '90210-0001')
insert into #tCity values('Oraclestinksville', '09542-0666')
insert into #tCity values('Chicago', '88465-8795')
insert into #tCity values('Miami', '46587-7891')
insert into #tCity values('Indianapolis', '46113-5467')
insert into #tCustPOs values('PO1', null, 'Shalomville', 1)
insert into #tCustPOs values('PO2', null, 'SQLVille', 1)
insert into #tCustPOs values('PO3', null, 'Oraclestinksville', 1)
insert into #tCustPOs values('PO4', null, 'Miami', 1)
insert into #tCustPOs values('PO5', null, 'Indianapolis', 1)
insert into #tCustPOs values('PO6', null, 'Chicago', 1)
insert into #tCustPOs values('PO7', null, 'Mixalottown', 1)
insert into #tCustomerStatus values('On hold')
insert into #tCustomerStatus values('Excellent')
insert into #tCustomerStatus values('In Collections')
insert into #tCustomerStatus values('Doesn''t Matter')
insert into #tCustomer values('Big Bob''s Plumbing', 1, 'PO1')
insert into #tCustomer values('Joe''s Crab Shack', 4, 'PO3')
insert into #tCustomer values('Department of Revenue', 1, 'PO9')
insert into #tCustomer values('Alcoa CSI', 2, 'PO7')
insert into #tCustomer values('Customer XYZ', 3, 'PO6')
insert into #tCustomer values('AMD', 4, 'PO4')
insert into #tCustomer values('Big Bad Bills', 1, 'PO2')
insert into #tCustomer values('Digital Underground LLC', 3, 'PO15')
select c.customername, cs.statusname, c.customer_po, po.city, ci.zipcode
from #tCustomer c
join #tCustomerStatus cs on c.customerstatus = cs.customerstatus
left join #tCustPOs po
join #tCity ci on po.city = ci.city
on c.customer_po = po.customer_po
select c.customername, cs.statusname, c.customer_po, po.city, ci.zipcode
from #tCustomer c
join #tCustomerStatus cs on c.customerstatus = cs.customerstatus
left join #tCustPOs po on c.customer_po = po.customer_po
join #tCity ci on po.city = ci.city
I won't dump all the execution plan stuff here, but essentially query #1 behaves as if I had created a subquery on the #tCustPOs and #tCity table and performed a LEFT join against the resultset. Query #2 behaves as though #tCustPOs were an INNER member of the main query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply