Query take so long - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query take so long

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-01-16 : 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]

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-01-16 : 12:48:41

I would suggest indexing the Salesid and transferid columns

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources