4

I have an SQL statement that is taking about 40 seconds to run and the only thing I see is an high hash match and sort. I have added indexs to the temp table but still taking an long time to complete.

https://www.brentozar.com/pastetheplan/?id=rJ7iMDC9m

SQL Server Execution Times: CPU time = 46735 ms, elapsed time = 9004 ms.

SELECT g.CustomerId, g.LogDate
INTO #TempGuidelineLog
FROM vwGuidelineLog g --nolock
where g.LogDate >= '2017-10-01' and g.LogDate < dateadd(day, 1, '2018-09-30')

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)
INCLUDE ([LogDate])


select
g.*,
a.StateId, 
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId)
INCLUDE ([CustomerId])

SELECT States=(SELECT Total=COUNT(c.CustomerID), c.StateId, st.Name
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN [State] st ON st.StateId = c.StateId
where c.CountryId = 1 and st.StateId NOT IN (65,66)
GROUP BY c.StateId, st.Name
ORDER BY 1 DESC
FOR XML PATH('State'),type)

,Dates = (SELECT StartDate = CONVERT(VARCHAR(10), '2017-10-01' , 101), EndDate = CONVERT(VARCHAR(10), '2018-09-30', 101)
FOR XML PATH('Date'),type)

,Countries=(SELECT Total=COUNT(c.CustomerID), co.CountryID, co.Name, ISOCode=co.TwoLetterISOCode
,[Distinct] =  COUNT(DISTINCT c.CustomerId)
FROM #Temp c
JOIN Country co ON co.CountryID = c.CountryId
GROUP BY co.CountryID, co.Name,co.TwoLetterISOCode
ORDER BY 1 DESC
FOR XML PATH('Country'),type)
FOR XML PATH('Report')

58% Cost for Hash Match

3
  • I see that estimate vs actual is way off. can you refresh your view sp_refreshview view_name and update stats to see if that helps ? Also, see if forcing join is an option FROM #Temp c JOIN Country to FROM #Temp c hash JOIN Country
    – Kin Shah
    Oct 12 '18 at 19:13
  • @Kin I get an Incorrect syntax near 'hash' FROM #Temp c hash JOIN Country co ON co.CountryID = c.CountryId
    – Jefferson
    Oct 12 '18 at 19:48
  • should be inner hash join
    – Kin Shah
    Oct 12 '18 at 20:45
0

First of all true test of sql is to take variable instead of constant value. So ,

Declare @From date='2017-10-01' 
Declare @To date='2018-09-30'

Immediate correction ,put the filter here

select
g.*,
a.StateId, 
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId
where g.CountryId = 1 and g.StateId NOT IN (65,66)

and remove the same filter from where it is now.

I see no requirement of LogDate in #TempGuidelineLog so remove it.

so new index,

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)

Since countryid is more selective,

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId,Stateid,CustomerId)

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CustomerId)

IMO, group by St.Name and co.Name is also hurting your performance.

so you can seperatly query group by function and then with that resultset join State and country table.hope it is clear.

The way you are creating XML is also complicated.You can choose one of the xml design which suite you from here,XML

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.