Why is this Query Taking a long time?

Question

Why is this Query Taking a long time?

bdgreen

Newbie

Points: 9
More actions
June 18, 2013 at 12:58 pm

#274000

This query seems to take forever. Is there a way I can speed it up?
SELECT cat.Category1 ,
cat.Category2 ,
cat.Category3 ,
tblItem.GPItemNumber AS [Item Number] ,
tblItem.ItemNumber AS [Regional Item Number] ,
GlbOrItm.QUANTITY AS [Quantity] ,
GlbOr.OrderNumber AS [Order Number] ,
GlbOr.Created AS [Order Date] ,
( tblUserProfile.FirstName + ' '
+ tblUserProfile.LastName ) AS [Ordered By] ,
GlbOr.GPLOCNCODE ,
GlbOr.GPCSTPONBR AS LocationID ,
GlbOr.ShipContact ,
GlbOr.ShipAddress1 ,
GlbOr.ShipAddress2 ,
GlbOr.ShipAddress3 ,
GlbOr.ShipCity ,
GlbOr.ShipState ,
GlbOr.ShipZipCode ,
GlbOr.ShipCountry ,
GlbOr.ShipPhone ,
tTH3.TypeHierarchyDesc AS Channel ,
tTH2.TypeHierarchyDesc AS ChannelType ,
tTH1.TypeHierarchyDesc AS LocationType ,
tLH3.LocationHierarchyDesc AS Region ,
tLH2.LocationHierarchyDesc AS SubRegion ,
tLH1.LocationHierarchyDesc AS Market
FROM OPENQUERY (OrderSvr,'Select *
From Commerce.dbo.GlobalOrders with (NoLock)') AS GlbOr
JOIN OPENQUERY(ORDERSvr,'Select *
From Commerce.dbo.GlobalOrderItems with (NoLock)') AS GlbOrItm
ON GlbOrItm.fk_GlobalOrderID = GlbOr.GlobalOrderID
JOIN tblItem WITH (NOLOCK)
ON tblItem.GPItemNumber = GlbOrItm.GPItemNmbr
LEFT JOIN (SELECT fkItemId ,aih1.itemhierarchydesc + '/' + aih2.itemhierarchydesc AS 'Category1' ,
bih1.itemhierarchydesc + '/' + bih2.itemhierarchydesc AS 'Category2' ,
cih1.itemhierarchydesc + '/' + cih2.itemhierarchydesc AS 'Category3'
FROM (SELECT fkItemID,
fkItemHierarchyid,
ROW_NUMBER() OVER (PARTITION BY fkitemid ORDER BY fkitemhierarchyid DESC ) AS 'Row'
FROM tblitemcategory WITH (NOLOCK)
) S PIVOT ( MAX(fkitemhierarchyid) FOR Row IN ([1],[2],[3])) P
LEFT JOIN tblItemHierarchy aih2 WITH (NOLOCK)ON aih2.ItemHierarchyID = [1]
LEFT JOIN tblitemhierarchy aih1 WITH (NOLOCK)ON aih1.itemhierarchyid = aih2.parentid
LEFT JOIN tblItemHierarchy bih2 WITH (NOLOCK)ON bih2.ItemHierarchyID = [2]
LEFT JOIN tblitemhierarchy bih1 WITH (NOLOCK)ON bih1.itemhierarchyid = bih2.parentid
LEFT JOIN tblItemHierarchy cih2 WITH (NOLOCK)ON cih2.ItemHierarchyID = [3]
LEFT JOIN tblitemhierarchy cih1 WITH (NOLOCK)ON cih1.itemhierarchyid = cih2.parentid
) AS cat ON cat.fkitemid = tblItem.itemid
LEFT OUTER JOIN tblUserProfile WITH (NOLOCK)ON GlbOr.UserID = tblUserProfile.fkUserID
JOIN tblLocation AS tL WITH (NOLOCK)ON GlbOr.GPCSTPONBR = CAST(tL.LocationID AS VARCHAR(20))
JOIN tblTypeHierarchy AS tTH1 WITH (NOLOCK) ON tL.fkTypeHierarchyID = tTH1.TypeHierarchyID
JOIN tblTypeHierarchy AS tTH2 WITH (NOLOCK) ON tTH1.ParentID = tTH2.TypeHierarchyID
JOIN tblTypeHierarchy AS tTH3 WITH (NOLOCK) ON tTH2.ParentID = tTH3.TypeHierarchyID
JOIN tblLocationHierarchy AS tLH1 WITH (NOLOCK) ON tL.fkLocationHierarchyID = tLH1.LocationHierarchyID
JOIN tblLocationHierarchy AS tLH2 WITH (NOLOCK) ON tLH1.ParentID = tLH2.LocationHierarchyID
JOIN tblLocationHierarchy AS tLH3 WITH (NOLOCK) ON tLH2.ParentID = tLH3.LocationHierarchyID
WHERE GlbOr.Created >= DATEADD(day, -365, @EndDate)
AND GlbOr.Created <= @EndDate
AND (LEFT(GlbOr.OrderNumber, 5) = 'CINOR'
OR LEFT(GlbOr.OrderNumber, 5) = 'CINSK')
ORDER BY Category1 ,
Category2 ,
GlbOrItm.GPItemnmbr

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Hi and welcome to the forums. I don't quite understand why you are using openquery for this.

There are a number of things that are causing this be far slower than you would want. The first issue is your query is nonSARGable because of all the function calls in the where clause.

You have queries using OPENQUERY that have subselects to OPENQUERY with subselects to subselects and then a couple more subselects.

In order to offer much assistance we are going to need a LOT more information than just a big complicated query. Please take a look at this article that explains how to post performance issues.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

Also, I noticed you have NOLOCK hints littering your query. Are you aware of all the issues this hint brings to the table? It is not a magic pill to make your queries faster.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post table definitions, index definitions and execution plan.

You can start by removing the nolock hints, not because removing them will improve performance, but because removing them will remove the risks of incorrect, duplicate and missing data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 3

well, the NOLOCKS bother me enormously, it is not a magic "go fast" button for SQL, it has a lot of dark ramifications like missing or duplicate data.

as far as the code

at a minimum, you could combine the two open queries into a single one, so at least that join is done on the remote server, and not copied to temp and then performed.:

SELECT cat.Category1,

cat.Category2,

cat.Category3,

tblItem.GPItemNumber AS [Item Number],

tblItem.ItemNumber AS [Regional Item Number],

GlbOr.QUANTITY AS [Quantity],

GlbOr.OrderNumber AS [Order Number],

GlbOr.Created AS [Order Date],

(tblUserProfile.FirstName + ' ' + tblUserProfile.LastName) AS [Ordered By],

GlbOr.GPLOCNCODE,

GlbOr.GPCSTPONBR AS LocationID,

GlbOr.ShipContact,

GlbOr.ShipAddress1,

GlbOr.ShipAddress2,

GlbOr.ShipAddress3,

GlbOr.ShipCity,

GlbOr.ShipState,

GlbOr.ShipZipCode,

GlbOr.ShipCountry,

GlbOr.ShipPhone,

tTH3.TypeHierarchyDesc AS Channel,

tTH2.TypeHierarchyDesc AS ChannelType,

tTH1.TypeHierarchyDesc AS LocationType,

tLH3.LocationHierarchyDesc AS Region,

tLH2.LocationHierarchyDesc AS SubRegion,

tLH1.LocationHierarchyDesc AS Market

FROM OPENQUERY(OrderSvr, 'Select

T1.*,

T2.QUANTITY,

T2.GPItemnmbr

From Commerce.dbo.GlobalOrders T1

INNER JOIN Commerce.dbo.GlobalOrderItems T2

ON T2.fk_GlobalOrderID = T1.GlobalOrderID') AS GlbOr

INNER JOIN tblItem WITH (NOLOCK)

ON tblItem.GPItemNumber = GlbOrItm.GPItemNmbr

LEFT JOIN (

SELECT fkItemId,

aih1.itemhierarchydesc + '/' + aih2.itemhierarchydesc AS 'Category1',

bih1.itemhierarchydesc + '/' + bih2.itemhierarchydesc AS 'Category2',

cih1.itemhierarchydesc + '/' + cih2.itemhierarchydesc AS 'Category3'

FROM (

SELECT fkItemID,

fkItemHierarchyid,

ROW_NUMBER() OVER (

PARTITION BY fkitemid ORDER BY fkitemhierarchyid DESC

) AS 'Row'

FROM tblitemcategory WITH (NOLOCK)

) S

PIVOT(MAX(fkitemhierarchyid) FOR Row IN (

[1],

[2],

[3]

)) P

LEFT JOIN tblItemHierarchy aih2 WITH (NOLOCK)

ON aih2.ItemHierarchyID = [1]

LEFT JOIN tblitemhierarchy aih1 WITH (NOLOCK)

ON aih1.itemhierarchyid = aih2.parentid

LEFT JOIN tblItemHierarchy bih2 WITH (NOLOCK)

ON bih2.ItemHierarchyID = [2]

LEFT JOIN tblitemhierarchy bih1 WITH (NOLOCK)

ON bih1.itemhierarchyid = bih2.parentid

LEFT JOIN tblItemHierarchy cih2 WITH (NOLOCK)

ON cih2.ItemHierarchyID = [3]

LEFT JOIN tblitemhierarchy cih1 WITH (NOLOCK)

ON cih1.itemhierarchyid = cih2.parentid

) AS cat

ON cat.fkitemid = tblItem.itemid

LEFT JOIN tblUserProfile WITH (NOLOCK)

ON GlbOr.UserID = tblUserProfile.fkUserID

INNER JOIN tblLocation AS tL WITH (NOLOCK)

ON GlbOr.GPCSTPONBR = CAST(tL.LocationID AS VARCHAR(20))

INNER JOIN tblTypeHierarchy AS tTH1 WITH (NOLOCK)

ON tL.fkTypeHierarchyID = tTH1.TypeHierarchyID

INNER JOIN tblTypeHierarchy AS tTH2 WITH (NOLOCK)

ON tTH1.ParentID = tTH2.TypeHierarchyID

INNER JOIN tblTypeHierarchy AS tTH3 WITH (NOLOCK)

ON tTH2.ParentID = tTH3.TypeHierarchyID

INNER JOIN tblLocationHierarchy AS tLH1 WITH (NOLOCK)

ON tL.fkLocationHierarchyID = tLH1.LocationHierarchyID

INNER JOIN tblLocationHierarchy AS tLH2 WITH (NOLOCK)

ON tLH1.ParentID = tLH2.LocationHierarchyID

INNER JOIN tblLocationHierarchy AS tLH3 WITH (NOLOCK)

ON tLH2.ParentID = tLH3.LocationHierarchyID

WHERE GlbOr.Created >= DATEADD(day, - 365, @EndDate)

AND GlbOr.Created <= @EndDate

AND (

LEFT(GlbOr.OrderNumber, 5) = 'CINOR'

OR LEFT(GlbOr.OrderNumber, 5) = 'CINSK'

)

ORDER BY Category1,

Category2,

GlbOrItm.GPItemnmbr

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Another option may be to use a Recusive CTE's to replace the Hierarchy Joins that you are using.

_________________________________________________________________________
SSC Guide to Posting and Best Practices