November 8, 2011 at 9:48 am
Hi - I am found a particular statement in a trigger that is causing extensive CPU usage. The code is as follows:
begin transaction
update h
set
r_len = (select min(r_len) from view_batch_results where batch_id = h.batch_id),
r_wid = (select min(r_wid) from view_batch_results where batch_id = h.batch_id),
r_len_back = (select min(r_len_back) from view_batch_results where batch_id = h.batch_id),
r_wid_right = (select min(r_wid_right) from view_batch_results where batch_id = h.batch_id),
r_thic = (select min(r_thic) from view_batch_results where batch_id = h.batch_id),
r_thic_2 = (select min(r_thic_2) from view_batch_results where batch_id = h.batch_id),
r_force = (select min(r_force) from view_batch_results where batch_id = h.batch_id),
r_strength = (select min(r_strength) from view_batch_results where batch_id = h.batch_id),
r_strength_b2 = (select min(r_strength_b2) from view_batch_results where batch_id = h.batch_id),
r_strength_c3 = (select min(r_strength_c3) from view_batch_results where batch_id = h.batch_id),
Ave_strength = (select(Ave_strength) from view_batch_results where batch_id = h.batch_id),
Ave_Density = (select(Ave_density) from view_batch_results where batch_id = h.batch_id),
St_Deviation = (select(St_Deviation) from view_batch_results where batch_id = h.batch_id),
r_diagonals = (select min(r_diagonals) from view_batch_results where batch_id = h.batch_id),
r_len_diff = (select min(r_len_diff) from view_batch_results where batch_id = h.batch_id),
r_wid_diff = (select min(r_wid_diff) from view_batch_results where batch_id = h.batch_id),
r_wid_2_diff = (select min(r_wid_2_diff) from view_batch_results where batch_id = h.batch_id),
r_thic_diff = (select min(r_thic_diff) from view_batch_results where batch_id = h.batch_id),
r_wid_2 = (select min(r_wid_2) from view_batch_results where batch_id = h.batch_id),
r_wid_2_right = (select min(r_wid_2_right) from view_batch_results where batch_id = h.batch_id),
r_strength_cube = (select max(r_strength_cube) from view_batch_results where batch_id = h.batch_id),
r_failure_id = (select max(r_failure_id) from view_batch_results where batch_id = h.batch_id),
r_pitch_a = (select min(r_pitch_a) from view_batch_results where batch_id = h.batch_id),
r_pitch_b = (select min(r_pitch_b) from view_batch_results where batch_id = h.batch_id),
r_dome_height = (select min(r_dome_height) from view_batch_results where batch_id = h.batch_id),
r_dome_wid = (select min(r_dome_wid) from view_batch_results where batch_id = h.batch_id),
-- Following two lines added 170305 to calculate breaking strength average and standard deviation for Flag results
Ave_bload = (select(Ave_bload) from view_batch_results where batch_id = h.batch_id),
StDev_bload = (select(StDev_bload) from view_batch_results where batch_id = h.batch_id)
from tbl_lab_test_header h inner join inserted i
on h.batch_id = i.batch_id
commit
What is the best way I can go about tuning this code. The join to INSERTED makes this difficult for me to figure out.
Thanks
November 8, 2011 at 9:52 am
Go back to read the business requirements of this code.
That screams bug to me.
I've seen this type of code often when the user actually needs top 1 order by x. If this is the case, then the results are flat out wrong.
The join to inserted filters the rows so that only the row(s) that have been affected by the previous statement get processed.
November 8, 2011 at 9:53 am
Are you trying to store calculated fields for things like average across all the records of a given type? This is exactly the reason NOT to store calculated values. You calculate them when you need them not when you save a new entry that will affect the result.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2011 at 1:24 pm
Its quite complicated, as the view its querying also performs a mass of min and avg functions to bring back the mins and avgs per batch_id.
If I was to turn the trigger code such that it only references the view once, and keep the join to inserted then would that return the same results?
November 8, 2011 at 1:37 pm
Kwisatz78 (11/8/2011)
Its quite complicated, as the view its querying also performs a mass of min and avg functions to bring back the mins and avgs per batch_id.If I was to turn the trigger code such that it only references the view once, and keep the join to inserted then would that return the same results?
If you are pretty sure that the logic is correct then you can try to use this:
begin transaction
update h
set
r_len = br.r_len,
r_wid = br.r_wid,
r_len_back = br.r_len_back,
r_wid_right = br.r_wid_right,
r_thic = br.r_thic,
r_thic_2 = br.r_thic_2,
r_force = br.r_force,
r_strength = br.r_strength,
r_strength_b2 = br.r_strength_b2,
r_strength_c3 = br.r_strength_c3,
Ave_strength = br.Ave_strength,
Ave_Density = br.Ave_density,
St_Deviation = br.St_Deviation,
r_diagonals = br.r_diagonals,
r_len_diff = br.r_len_diff,
r_wid_diff = br.r_wid_diff,
r_wid_2_diff = br.r_wid_2_diff,
r_thic_diff = br.r_thic_diff,
r_wid_2 = br.r_wid_2,
r_wid_2_right = br.r_wid_2_right,
r_strength_cube = br.r_strength_cube,
r_failure_id = br.r_failure_id,
r_pitch_a = br.r_pitch_a,
r_pitch_b = br.r_pitch_b,
r_dome_height = br.r_dome_height,
r_dome_wid = br.r_dome_wid,
-- Following two lines added 170305 to calculate breaking strength average and standard deviation for Flag results
Ave_bload = br.Ave_bload,
StDev_bload = br.StDev_bload
from tbl_lab_test_header h inner join inserted i
on h.batch_id = i.batch_id
left join
(
select
batch_id,
r_len = min(r_len),
r_wid = min(r_wid),
r_len_back = min(r_len_back),
r_wid_right = min(r_wid_right),
r_thic = min(r_thic),
r_thic_2 = min(r_thic_2),
r_force = min(r_force),
r_strength = min(r_strength),
r_strength_b2 = min(r_strength_b2),
r_strength_c3 = min(r_strength_c3),
Ave_strength = MIN(Ave_strength),
Ave_Density = MIN(Ave_density),
St_Deviation = MIN(St_Deviation),
r_diagonals = min(r_diagonals),
r_len_diff = min(r_len_diff),
r_wid_diff = min(r_wid_diff),
r_wid_2_diff = min(r_wid_2_diff),
r_thic_diff = min(r_thic_diff),
r_wid_2 = min(r_wid_2),
r_wid_2_right = min(r_wid_2_right),
r_strength_cube = max(r_strength_cube),
r_failure_id = max(r_failure_id),
r_pitch_a = min(r_pitch_a),
r_pitch_b = min(r_pitch_b),
r_dome_height = min(r_dome_height),
r_dome_wid = min(r_dome_wid),
-- Following two lines added 170305 to calculate breaking strength average and standard deviation for Flag results
Ave_bload = MIN(Ave_bload),
StDev_bload = MIN(StDev_bload)
from view_batch_results
group by batch_id
) br on br.batch_id = h.batch_id
commit
November 8, 2011 at 2:37 pm
I see thats great thanks for all the help, I will check out if the logic is correct first of all then maybe use what Alex has provided as an improvement.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply