Update performance 1

[Naith]

Is there a more efficient way to perform this update?

UPDATE TSR_Forecast
SET TSR_Forecast.fc_sales_district_id = b.sales_district_id
FROM TSR_Actuals b
WHERE TSR_Forecast.customernumber = b.apple_hq_id
AND fc_sales_district_id IS NULL
and sales_district_id <> 'SP20'

At the moment, despite indices on all the fields concerned, the performance on this is incredible (in a bad way).

 

[Catadmin]

Try making it a join instead.

UPDATE tf
SET fc_sales_district_id = b.sales_district_id
FROM TSR_Forecast tf
JOIN TSR_Actuals b
on tf.CustomerNumber = b.Apple_hq_ID
WHERE tf.fc_sales_district_id IS NULL
and b.sales_district_id <> 'SP20'

I'm not sure where Sales_District_ID is coming from. You may need to change that.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Naith]

I like the look of this. Seems to be a lot easier on the processing.

Thanks. Have a star.

-N

 

[Catadmin]

Glad I could help. @=)

Thanks for the star.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"