1
update  #temp_Or_GetPendingOrdersList
set     ApprovedOn = 
            dbo._UD_GetDate(dbo._UD_ConvertToCompanyTime(@HHA,#temp_Or_GetPendingOrdersList.ApprovedDate,#temp_Or_GetPendingOrdersList.branchid))
            + ' ' +dbo._UD_GetTime(dbo._UD_ConvertToCompanyTime(@HHA,#temp_Or_GetPendingOrdersList.ApprovedDate,#temp_Or_GetPendingOrdersList.branchid))
from    HConfigurations2 with(nolock), TimeZones with(nolock)
where   #temp_Or_GetPendingOrdersList.branchid = HConfigurations2.HHA_BRANCHID
        and HConfigurations2.TIME_ZONE = TimeZones.ZONE_ID
        and HConfigurations2.HHA_ID = @hha

There is a update statement in a stored procedure which has very high worktable logical reads. How to reduce them?

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Raj Kumar
  • 65
  • 1
  • 8
  • 1
    what does the execution plan look like? Also you may well be focusing on the wrong thing. Probably you should be looking at getting rid of the scalar UDFs to improve perf. Worktable reads are reported differently from other things [rows read not pages read](https://stackoverflow.com/a/5194902/73226) BTW – Martin Smith Oct 03 '20 at 07:00
  • It is likely that this is going to be iterative - fixing one problem will potentially cause another (and this is why performance DBAs still get paid). As @Martin says, removing the scalar UDFs will likely help. But it may (will) change the execution plan and you'd get other bottlenecks. Then I'd suggest reviewing execution plans to identify worst offenders (e.g., full table/clustered index scans for only a few rows) then update indexes. Then you'd check it again (with new execution plans) and keep improving it until you can improve it no longer, or it is acceptable even with a few quirks. – seanb Oct 03 '20 at 12:16

0 Answers0