SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query take so long
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 01/16/2013 :  09:44:22  Show Profile  Reply with Quote
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]

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 01/16/2013 :  12:48:41  Show Profile  Reply with Quote
I would suggest indexing the Salesid and transferid columns

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000