Slow Query - 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

Slow Query

Author

Topic

ChrisK1506
Starting Member

6 Posts

Posted - 2011-09-07 : 08:13:53

Hi,

I was wondering if anyone had any tips on how to speed up the following query.

SELECT distinct (SELECT TOP 1 dbo.tblWorkFlowLog.Operation
FROM dbo.tblWorkFlowLog
WHERE (dbo.tblOrderLine.OrderLineId = dbo.tblWorkFlowLog.OrderLineId)
ORDER BY WorkFlowLogId DESC) AS Operation,
dbo.tblOrderLine.OrderLineStatus,
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef,
dbo.tblOrderHeader.SourceCode,
dbo.tblOrderLine.OrderLineId,
dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
CASE
WHEN (dbo.tblCustomer.Firstname <> '') AND
(dbo.tblCustomer.Firstname IS NOT NULL)
THEN COALESCE (dbo.tblCustomer.Firstname + ' ', N'') +
COALESCE (dbo.tblCustomer.Surname, N'')
ELSE COALESCE (dbo.tblCustomer.Surname, N'')
END as Name, dbo.tblCustomer.PostCode,
CAST(dbo.tblOrderLine.BarCode as nvarchar(50)) AS BarCode,
SUBSTRING(dbo.tblOrderLine.BarCode,7, LEN(dbo.tblOrderLine.BarCode)) AS SortableBarCode,
CONVERT (varchar(50),
dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblOrderLine.ProductCode AS ProductCode,
MAX(dbo.tblOrderLineOptions.CompletedDate) AS Printed,
dbo.tblOrderLine.DespatchStatus,
dbo.fnc_OrderLineIsReplacement(dbo.tblOrderLine.OrderLineId) AS IsReplacement,
dbo.tblDSRegenerated.IsRegenerated AS Regenerated
FROM dbo.tblOrderLine
LEFT OUTER JOIN dbo.tblOrderLineOptions ON dbo.tblOrderLine.OrderLineId = dbo.tblOrderLineOptions.OrderLineId
LEFT OUTER JOIN dbo.tblOrderHeader ON dbo.tblOrderLine.OrderId = dbo.tblOrderHeader.OrderId
LEFT OUTER JOIN dbo.tblCustomer ON dbo.tblOrderHeader.CustId = dbo.tblCustomer.CustId
LEFT OUTER JOIN dbo.tblDSRegenerated ON dbo.tblOrderLine.OrderLineId = dbo.tblDSRegenerated.OrderLineId
WHERE (dbo.tblOrderLine.ProductCode IN (N'SPPF', N'SMBK', N'SPFD', N'SRBW', N'SPPO', N'SPWR', N'SBRW', 'USPW', 'USPP', 'USPO', 'USPF', 'USMB'))
GROUP BY dbo.tblOrderLine.OrderLineStatus,
dbo.tblDSRegenerated.IsRegenerated,
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef,
dbo.tblOrderLine.OrderLineId,
dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
dbo.tblCustomer.PostCode,
dbo.tblOrderLine.BarCode,
CONVERT (varchar(50),
dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblOrderLine.ProductCode,
dbo.tblOrderLine.DespatchStatus,
dbo.tblOrderLineOptions.WorkFlowId,
dbo.tblOrderHeader.SourceCode
HAVING (dbo.tblOrderLine.DespatchStatus = 'pk' OR
dbo.tblOrderLine.DespatchStatus IS NULL OR
dbo.tblOrderLine.DespatchStatus = '') AND
(dbo.tblOrderLineOptions.WorkFlowId = 3)
ORDER BY dbo.tblOrderHeader.DateStamp DESC

Thanks

Chris

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-07 : 09:26:29

The syntax looks pretty decent at first glance...you should probably have a look at the index situation. What does the execution plan look like? Do all the join-columns have indexes on them? Is tblOrderLine.ProductCode indexed?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 10:12:45

@Lumbago : Its pretty much the same query that the OP asked last time:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164537

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources