April 9, 2015 at 3:02 pm
Hello all, I have atttached the query plan. I think the problem is from using Count Distinct. Is there any work around that? If I remove, count(distinct), it runs very fast. Appreciate your help.
April 9, 2015 at 3:13 pm
SQL_Surfer (4/9/2015)
Hello all, I have atttached the query plan. I think the problem is from using Count Distinct. Is there any work around that? If I remove, count(distinct), it runs very fast. Appreciate your help.
I assume the "problem" is performance? I don't see a COUNT aggregate in this query at all.
SELECT
Item.DSCA AS Item
,ta.TerritoryId
,ta.TerritoryName
,ta.RegionName
,ta.AreaName
,OrdHeader.[orno] AS OrderNumber
,CAST(OrdLine.[qoor] AS INT) AS Quantity
,CAST(OrdLine.STPR AS DECIMAL(19,2)) AS Price
,CONVERT(DECIMAL(19,2),OrdLine.STPR * OrdLine.[qoor]) AS ExtendedPrice
INTO #TEMP_MASTER
FROM tdsls400 OrdHeader
INNER JOIN tdsls401 OrdLine
ON OrdLine.orno = OrdHeader.orno
INNER JOIN TCIBD001 Item
ON OrdLine.[item] = Item.[item] and OrdLine.CONBR = Item.CONBR
LEFT OUTER JOIN #TEMP_ALLOWED_ITEMS ti
ON OrdLine.[item] = ti.[ItemCode]
LEFT OUTER JOIN tccom100 ARBP
ON OrdHeader.ofbp = ARBP.bpid
LEFT OUTER JOIN tccom110
ON ARBP.bpid = tccom110.ofbp AND ARBP.CONBR = tccom110.CONBR
LEFT OUTER JOIN TCCOM130 Addrs
ON tccom110.cadr = Addrs.CADR
INNER JOIN Teva_Territory_Master ta
ON Addrs.PSTC = ta.Zip
WHERE OrdHeader.[cofc] = @SalesOffice
AND (OrdHeader.Odat_CST >= @LastDayToPull )
AND (OrdHeader.hdst_enDesc NOT IN ('Canceled'))
AND (Item.item <> '99999')
AND (@HasAllowedItems IS NULL OR ti.ItemCode LIKE '%')
I do however see a pointless predicate in your where clause. What is the point of ti.ItemCode LIKE '%'? That will cause a scan to determine...well it can be anything but we need to evaluate each and every row anyway. Why not just remove that?
Did you notice all the missing indexes that were suggested? There are 4 of them. Now I am not saying to just blindly starting generating indexes based off those suggestions but it is a clear indication that your tables needs some help.
We can help but you need to provide a lot more information here. Please take a look at this article about posting performance problems.
http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
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/
April 9, 2015 at 3:30 pm
I do however see a pointless predicate in your where clause. What is the point of ti.ItemCode LIKE '%'? That will cause a scan to determine...well it can be anything but we need to evaluate each and every row anyway. Why not just remove that?
I agree with that.
It appears he is using it for an optional parameter. If that is the case, I would change that to ti.ItemCode = ti.ItemCode
I know this doesn't answer the original question, but it should speed it up a bit.
April 9, 2015 at 3:32 pm
Sorry, I attached incomplete plan. I've reattached the complete plan.
April 10, 2015 at 7:26 am
Which portion takes a long time to complete?
Sometimes the plan cost can tell a porkie and the longest running bit might be the cheaper costed plan.
I see a view, vw_Teva_Territory_ZipCodes, with a higher rowcount than tempmaster so may actually be the zipcodes temp table which is the problem.
Might consider creating the temp table for zipcodes with a clustered index on it before the insert from the view, on (areaname,Item) to cater for costliest part of the query (SUMMARY BY AREA).
April 10, 2015 at 7:55 am
SQL_Surfer (4/9/2015)
Sorry, I attached incomplete plan. I've reattached the complete plan.
Thanks for the updated plan. Now can you post all the other information needed to help? The article I reference previously talks about table and index definitions. I don't see those anywhere. I can tell you that there is a LOT of room for improvement here. There are 7 queries and there are missing indexes on every single one of them.
It seems like you have several views that are all referencing tdsls400 which does not have a clustered index. We will probably need to see the definition of these views too including their base tables.
_______________________________________________________________
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply