October 1, 2014 at 9:47 am
hello DBAs,
I am trying to improve the performance of the following view, the Query is running more than 30 mins. for execution in DEV servers.(Not sure Duration) As a result the asp.net page which is displaying the report is timing out.i.e,application is timing out.
SQL Query:
SELECT WholeSalerName,WholeSalerID,Sum(QTYSold) AS QTYSold,Sum(QtyOutOfStock) AS QtyOutOfStock
FROM vPTWeeklYData Where Convert(DateTime,EffectiveDate) >= '9/22/2014'
and Convert(DateTime,EffectiveDate) <= '9/28/2014'
GROUP BY WholeSalerID,WholeSalerName
my VIEW name :[dbo].[vPTWeeklyData]
SELECT W.DataID,W.DistDEANo,
CM.CustName AS DistributorName,
WH.WholeSalerName AS WholeSalerName,
W.WholeSalerID, W.EffectiveDate,W.ProdNDCNo,
M.productDesc AS ProductDesc,
PG.Hierarchy AS ProductGroup,
PG.HierarchyDesc AS ProductGroupDesc,
W.CurInvQtyShipSale, W.QtyOnOrder, W.QtyWFWInv,W.QTySold,
OnHand =
CASE
WHEN W.QTySold = 0 THEN 0
Else Convert(decimal(9,2),((Convert(Decimal(9,2),W.CurInvQtyShipSale)+ Convert(Decimal(9,2),W.QtyOnOrder))/W.QTySold))
END,
W.QtyReceived,
W.BegBalQty, W.EndBalQty, W.AddDemandQty, W.PlannedInvQty,W.QtyOutOfStock, w.qtycomm
FROM dbo.PTWeeklyData W WITH (NOLOCK)
INNER JOIN PTWholesaler WH WITH (NOLOCK) ON WH.WholeSalerID = W.WholeSalerID
LEFT JOIN vPTProductList M WITH (NOLOCK) ON Rtrim(M.NDCNum) = Rtrim(W.ProdNDCNo) AND ProductCOde <> '000000000000003908'
LEFT Join PTProductHierarchy PG WITH (NOLOCK) on Rtrim(M.Hierarchy) = Rtrim(PG.Hierarchy)
LEFT JOIN vPTDistinctDEANum CM WITH (NOLOCK) ON Rtrim(W.DistDEANo) = Rtrim(CM.DEANum)
FYI,When I tried executing the Select SQL Query its reaches to Total Server memory i.e,It takes up more memory and CPU usage is 100% ,
I found that Estimated IO cost is 78% and clustered Index scan cost is 23%
Please let me know how can i Improve the Performance,Please suggest me..
Thanks in advance!!.
Thanks & Regards,
DBA
SQL server DBA
October 1, 2014 at 10:27 am
First you need to find out if the issue is with your view or with the statement the view uses. Run the statement that creates the view, but add the where clause to it. How long does that take to run? If it takes a long time, then you need to look into improving that part of it....look into indexes, etc.
In other words...run this...
SELECT W.DataID,W.DistDEANo,
CM.CustName AS DistributorName,
WH.WholeSalerName AS WholeSalerName,
W.WholeSalerID,
W.EffectiveDate,
W.ProdNDCNo,
M.productDesc AS ProductDesc,
PG.Hierarchy AS ProductGroup,
PG.HierarchyDesc AS ProductGroupDesc,
W.CurInvQtyShipSale,
W.QtyOnOrder,
W.QtyWFWInv,
W.QTySold,
OnHand =
CASE
WHEN W.QTySold = 0 THEN 0
Else Convert(decimal(9,2),((Convert(Decimal(9,2),W.CurInvQtyShipSale)+ Convert(Decimal(9,2),W.QtyOnOrder))/W.QTySold))
END,
W.QtyReceived,
W.BegBalQty,
W.EndBalQty,
W.AddDemandQty,
W.PlannedInvQty,
W.QtyOutOfStock,
w.qtycomm
FROM dbo.PTWeeklyData W WITH (NOLOCK)
INNER JOIN PTWholesaler WH WITH (NOLOCK)
ON WH.WholeSalerID = W.WholeSalerID
LEFT JOIN vPTProductList M WITH (NOLOCK)
ON Rtrim(M.NDCNum) = Rtrim(W.ProdNDCNo)
AND ProductCOde <> '000000000000003908'
LEFT Join PTProductHierarchy PG WITH (NOLOCK)
on Rtrim(M.Hierarchy) = Rtrim(PG.Hierarchy)
LEFT JOIN vPTDistinctDEANum CM WITH (NOLOCK)
ON Rtrim(W.DistDEANo) = Rtrim(CM.DEANum)
Where Convert(DateTime,EffectiveDate) >= '9/22/2014'
and Convert(DateTime,EffectiveDate) <= '9/28/2014'
GROUP BY WholeSalerID,WholeSalerName
-SQLBill
October 1, 2014 at 10:37 am
Read the following article to know what is needed for performance problems:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Your view uses functions on your joins. Remove them as they're not useful at all as demonstrated in the following query.
SELECT 1
WHERE 'a' = 'a '
Why are you converting your date columns? Aren't they some kind of date/time data type?
Are you aware on what the NOLOCK hint does? Are your users aware that they can get inconsistent results? Are they fine with that? Do you know that it's not a go-fast option?
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/
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
October 1, 2014 at 1:11 pm
You have a lot of performance hurdles to overcome in here. You have lots of nonSARGable code. You have functions all over the place in your join and where predicates. Each of these functions renders any indexes useless. Then you are obviously storing datetime information in varchar columns. Again, indexes are useless here because the first thing that happens is every single row must first be converted into the datatype it should have been in already.
And take heed with the warnings about littering your database with NOLOCK. The article Luis referenced explain what the hint is really doing.
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply