query takes long time to run - 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

General SQL Server Forums

New to SQL Server Programming

query takes long time to run

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2014-09-08 : 15:27:54

Hi,

From the query below, if I comment out the line --b.shpDate, then it
tooks ~ 1.30 mins to execute the query. But i put it back, it only took 2 sec to exexute. The source is and left(convert(varchar(30),b.shpDate,120),10) =left(convert(varchar(30),getdate(),120),10)
i've try to put it at the end but it did not make any diffrent. Does anyone know?

Thanks

select distinct
a.saleorder,
a.setnu,
a.cust,
c.name,
a.toaddress,
a.delDate
--b.shpDate
from hinh a
INNER JOIN brstbl b
ON a.so = b.so and b.comp_id =250 and left(convert(varchar(30),b.shpDate,120),10) =left(convert(varchar(30),getdate(),120),10)
INNER JOIN com c
ON a.stc = c.pid and
a.so not in
(select d.so from hinh d
where d.so = a.so and d.seto = a.seto )
WHERE a.dep = 'yrt'

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-08 : 16:09:58

you could try running the SQL using SET STATISTICS IO ON
: the results will appear in the messages tab to the right of the results tab. Also include query plan XML you could post

a couple of questions:
How many rows in each table?
does the the shipdate conatin the time?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-08 : 21:33:07

[code]SELECT DISTINCT a.SaleOrder,
a.SetNu,
a.Cust,
c.Name,
a.ToAddress,
a.DelDate,
b.ShpDate
FROM dbo.Hinh AS a
INNER JOIN dbo.BrsTbl AS b ON b.so = a.so
AND b.Comp_ID = 250
AND b.ShpDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')
AND b.ShpDate < DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101')
INNER JOIN dbo.Com AS c ON c.pID = a.Stc
LEFT JOIN dbo.Hinh AS d ON d.so = a.so
AND d.so = a.so
AND d.SetO = a.SetO
AND a.Dep = 'yrt'
WHERE d.so IS NULL;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources