June 18, 2013 at 12:58 pm
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
June 18, 2013 at 1:13 pm
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/
June 18, 2013 at 1:14 pm
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
June 18, 2013 at 1:43 pm
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
June 19, 2013 at 2:32 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply