|
Villanuev
Constraint Violating Yak Guru
311 Posts |
 Posted - 01/16/2013 : 09:44:22
|
Hi Guys,
I have a query with 3 table. it take so long to execute my query.
the #sample3 has a lot more records as compare to the 2 table.
kindly please help me to modify my script or any idea. thank you in advance.
[code]
Create table #sample1
(Itemid nvarchar(35),
Salesid nvarchar(35),
Qtyorder int,
remainqty int)
Insert into #sample1(Itemid,Salesid,Qtyorder,remainqty)values('MOT2993-RTR','PSO0028935',1900,995)
Create table #sample2
(Salesid nvarchar(35),
poolid nvarchar(35),
createddate datetime)
Insert into #sample2(Salesid,poolid,createddate)values('PSO0028935','Parts','2012-12-13')
Create table #sample3
(Itemid nvarchar(35),
transrefid nvarchar(35),
Qty int,
statissue int,
datephysical datetime)
Insert into #sample3(Itemid,transrefid,Qty,statissue, datephysical)values('MOT2993-RTR','PSO0028935',-995,6,'1900-01-01')
Insert into #sample3(Itemid,transrefid,Qty,statissue, datephysical)values('MOT2993-RTR','PSO0028935',-217,1,'2012-12-20')
Insert into #sample3(Itemid,transrefid,Qty,statissue, datephysical)values('MOT2993-RTR','PSO0028935',-50,1,'2012-12-27')
Insert into #sample3(Itemid,transrefid,Qty,statissue, datephysical)values('MOT2993-RTR','PSO0028935',-192,1,'2013-01-09')
Insert into #sample3(Itemid,transrefid,Qty,statissue, datephysical)values('MOT2993-RTR','PSO0028935',-446,2,'2013-01-14')
select * from #sample1
select * from #sample2
select * from #sample3
--my query
select
s1.Itemid,
s1.Qtyorder,
s1.remainqty,
s3.Qty,
s1.Salesid,
s2.poolid,
s3.statissue,
s3.datephysical
from #sample1 s1
Inner Join #sample2 s2
On s2.Salesid=s1.Salesid
Left Outer join #sample3 s3
On s3.transrefid=s1.Salesid
[\code] |
|
Query take so long
