Performance Issue with Query

Transact-SQL
1

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
2

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.

3

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.

4

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.

5

hi RogierPronk

Good Advice

:ok_hand: :ok_hand: