December 6, 2012 at 1:28 am
Hi All,
I need a help or a suggestion
I have a SP which has an update statement and this updates around 200 columns and it is one record at a time.
Out off 200 columns may be around 90 columns are calculated columns which uses a subquery
for ex
Update table1
set col1 = (select count(1) from table2 where condtn1 = 'A'),
col2 = (select count(1) from table2 where condtn1 = 'c'),
col3 = (select count(1) from table3 where condtn1 = 'B'),
........
........
col90 =(select count(1) from table3 where condtn1 = 'B'),
where id = 123
How can i work on the performance of this update statement, by creating temporaary tables or ny other way?
Thanks in advance.
Manzil
December 6, 2012 at 8:36 am
Doing some of these subqueries up front and storing them in temp tables is definitely a place to start. You typically want to hit each table as few times as possible. Something to consider, even when your subqueries have different criteria they could still possibly be combined for example:
SELECT
SUM (CASE condtn1 WHEN 'A' THEN 1 ELSE 0 END) AS t2_A_cnt,
SUM (CASE condtn1 WHEN 'C' THEN 1 ELSE 0 END) AS t2_C_cnt
FROM table2
If all your subqueries are COUNT(*) like your example, you could even store the results in variables instead of a temp table, as long as none of the subqueries are correlated (the subquery references a column in the main table) correlated subqueries would be best to use a temp table and join in your UPDATE based on the correlated condition.
December 6, 2012 at 10:01 pm
Hi Chris.
Thank You....
Yes it good to use table variables..... happy that im thinking in the right way
December 7, 2012 at 1:51 am
manzilkolur23 (12/6/2012)
Hi Chris.Thank You....
Yes it good to use table variables..... happy that im thinking in the right way
Table variables and temp tables are not the same. Temp tables often perform better - especially if lots of data is involved.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply