Update select optimization

General
Enthusiast

Update select optimization

Hi,

I have one update statement .

update table A

from Table B

set 

A.col  = B.col...

where A.col = B.col

The columns in the where condition has Primary Index. And both the tables have NUSI also.

Can anybody pls suggest what can be done to tune this. 

4 REPLIES
Senior Supporter

Re: Update select optimization

why do you set A.col = B.col
when you already request where A.col = B.col???

Try to avoid to update the PI...
Enthusiast

Re: Update select optimization

Hi,

I am not updating the PI column. it is like this.

update Table A

from   Table B 

set COL 1  = B. COL1

COL2  = B.COL2

COL3  = B.COL3....

WHERE A.COL (PI)  =  B.COL (PI)

only in where condition i am using PI column. In other column updations NUSI Columns are also there.

Thanks,

Harshita

Senior Supporter

Re: Update select optimization

drop all NUSI, USI, JI? which contain columns which are going to be updated and recreate them after the update.
Enthusiast

Re: Update select optimization

Check if update can be changed to left outer join of insert script of table A. Updates are usually heavy compared to Inserts.

See if table is SET table ,on doing updates it might have to scan through other rows to check for duplicate rows.Make it multiset if feasible.

Check data types of primary indexes try and align them.

Collect stats on PI for both the tables.

If they are varchar fields with huge limit try to see alternatives liek using integers.