CODEWITHSUNDEEP
sql-serverreporting-servicessql-server-data-tools
Liew Kahpou
Liew Kahpou

Reputation: 1

How to do query optimization. Current dashboard took more than 3 minutes to show up

I am enhancing a dashboard which was built in that is taking a long time to query. However, I'm a newbie to SSDT and I have no idea where I should look.

I tried to remove the charts that might caused the problem, but no luck.

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1

SELECT HD.Incident_Number,HDA.log,HDA.Assigned_To,HD.Entry_ID,

DATEADD(hour, 14, DATEADD(day, 25567, HDA.Create_Date / (60.0 * 60.0 * 24.0) - 0.25)) AS MSC_submit_Date,
       CONCAT(FORMAT(DATEPART(hh, DATEADD(hour, 14, DATEADD(day, 25567, HDA.Create_Date / (60.0 * 60.0 * 24.0) - 0.25))), '00'), ':', FORMAT(DATEPART(mi, DATEADD(hour, 14, DATEADD(day, 25567, HDA.Create_Date / (60.0 * 60.0 * 24.0) - 0.25))), '00'), ':', FORMAT(DATEPART(ss, DATEADD(hour, 14, DATEADD(day, 25567, HDA.Create_Date / (60.0 * 60.0 * 24.0) - 0.25))), '00')) AS Time

INTO #temp1
FROM HPD_Help_Desk HD LEFT OUTER JOIN 
                    -- HPD_WorkLog AS HPD_WL ON HPD_WL.Incident_Number = HD.Incident_Number LEFT OUTER JOIN
                    --reference.dbo.SAP_worker AS rsw ON HD.Internet_E_mail = LOWER(rsw.micron_username) + '@micron.com' LEFT OUTER JOIN
                    HPD_HelpDesk_AuditLogSystem AS HDA ON HD.Entry_ID = HDA.Original_Request_ID 
                    --SLM_Measurement AS SM ON HD.Incident_Number = SM.ApplicationUserFriendlyID  

WHERE 
(DATEADD(hour, 14, DATEADD(day, 25567, HDA.Create_date/ (60.0 * 60.0 * 24.0) - 0.25)) >= @Shift_start_time) 

NA

Upvotes: 0

Views: 81

Answers (1)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

Using date functions in the WHERE clause is killing index usage. There are plenty of articles on the web about that. You can easily google for that by searching for SARG. For example here.

So to make your query working faster you need to do following things:

  • Create an index for HPD_HelpDesk_AuditLogSystem table. The easiest way for you could be check explain plan for that query and SSMS would probably suggest the index if it doesnt' exist
  • Make the predicate in the WHERE clause SARGable. It's hard to understand what's the logic is there. But simply speaking, that you need to re-write it in a way where all the functions are used against the @Shift_start_time variable.

One more thing is that you can use INNER JOIN instead of LEFT OUTER because you are using the joined table column as the predicate.

Upvotes: 1

Related Questions