SQLTeam.com | Weblogs | Forums

Performance Issue with Query

Hello - I have this view, which I am trying to query. My simple select top 1000 statement is currently at over 47 minutes, and I am unable to bring this into PowerBI to develop a report.

Any assistance on how I can improve the performance, or break this up into smaller queries would be greatly appreciated.

SELECT 
	 Ans_ID 
	,Customer_ID
	,Customer_ID_S
	,last_result_in_a_day
	,[Store Compliance Check]
	,[Front End Compliance]
	,[Front End Non Compliance Reason]
	,[Front End Corrective Action]
	,[Front End Corrective Action Taken]
	,[In Aisle Compliance]
	,[In Aisle Non Compliance Reason]
	,[In Aisle Corrective Action]
	,[In Aisle Corrective Action Taken]
	,ST_Name
	,Rep_ID
	,Territory_ID
	,[Posted Date]

FROM

( SELECT fa.ANS_ID 
		,CAST(fa.ANS_Posted AS datetime)				AS [Posted DateTime]
		,CAST(fa.ANS_Posted AS date)					AS [Posted Date]
		,fa.CUS_RealID									AS Customer_ID
		,CASE	WHEN fa.ST_Name = 'CONFECTION'	THEN fa.CUS_RealID + '|1'
				WHEN fa.ST_Name = 'FOOD'		THEN fa.CUS_RealID + '|2'
				WHEN fa.ST_Name = 'PET'			THEN fa.CUS_RealID + '|3'
				ELSE NULL
		END												AS Customer_ID_S
		,pq.Q_Text_Rename 
		,fa.ST_Name
		,fa.ANSR_Ans_AsText
		,fa.USR_RealID as Rep_ID
		,ust.Territory_ID
		,RANK() OVER(	PARTITION BY	fa.CUS_RealID
										,fa.ST_ID
										,fa.USR_RealID
										,fa.FRM_ID  
										,CAST(fa.ANS_Posted AS date)
								ORDER BY CAST(fa.ANS_Posted AS datetime) desc)		AS last_result_in_a_day

FROM		ca_mars_quoforecloud.dbo.FormAnswer fa
LEFT JOIN	[CA_PowerBI].[dbo].v_AFS_UserToTerritory ust
ON			fa.USR_RealID=ust.[User_ID]	
LEFT JOIN	[CA_PowerBI].[dbo].Mars_MW_POG_Fr_to_Eng_Questions pq
ON			fa.Q_Text=pq.[Q_Text]
WHERE		CAST(fa.ANS_Posted AS date) >='2021-01-01'
AND			fa.USR_RealID IN (	SELECT	[User_ID]								
								FROM	[ca_mars_quoforecloud].[dbo].[User]		
								WHERE	USERCL6 IN (1,3) )								---only keep the results submitted by FT and PT reps
AND			fa.FRM_ID IN	(	840,841 )												---valid FRM IDs for 2021 POG Compliance Survey
AND			ust.Territory_ID IN (	SELECT	Territory_ID								---2020-04-20 display results for active territories only		 
									FROM	[CA_PowerBI].[dbo].[v_AFS_Sales_Hierarchy]) ---2020-04-20 display results for active territories only
) t
PIVOT (MAX
			(ANSR_Ans_AsText)
			FOR [Q_Text_Rename] IN (
 [Store Compliance Check]
,[Front End Compliance]
,[Front End Non Compliance Reason]
,[Front End Corrective Action]
,[Front End Corrective Action Taken]
,[In Aisle Compliance]
,[In Aisle Non Compliance Reason]
,[In Aisle Corrective Action]
,[In Aisle Corrective Action Taken]
)
					) pvt

Performance issue is always hard... my best guess will be to add

OPTION(RECOMPILE)

first and see if it's a lot faster but don't use it in production. It can be a lot of things. It can be helpfull to look at the execution plan. I like to watch Brent Ozar performance tuning video's.

A friend of mine just will take the top 1 first and see if that is fast, remove the where clause, remove tables joines etc. just start building up the query small and see where it's getting slow.

Maybe you can first prepare the FormAnswer first to select only the data you want from the table and then start doing you query with a CTE.

And joining on [Q_Text] isn't a good idea, try to join on numbers always as SQL Server is so much better at numbers then text.

hi RogierPronk

Good Advice

:ok_hand: :ok_hand: