Both auditdata and a_sns should generally have an index on their id column if your are joining based on these columns. There are exceptions. If there is an index on both columns, the next best thing to do would be to run an explain plan on the sql query to find out what the optimizer is doing. At that point you can pinpoint the problem. [kccrosser] How many rows are in each of the tables? Particularly the temp table. First, why not use the following? update auditdata set TATCallType='12',TATCallUnit='1' where ID in (select id from Auditdata_sms_12 ); If there are a lot of rows in your temp table (like hundreds of thousands or millions?), you may need to break this into smaller chunks to avoid creating excessive rollback segments. It has been my experience that as rollback segments start getting really large, the transaction performance degrades exponentially. Best is to break into smaller transactions, like: declare cur cursor for select id from Auditdata_sms_12; declare @id int declare @i int set @i = 0 open cur fetch next from cur into @id begin transaction while @@fetch_status = 0 begin update auditdata set TATCallType='12',TATCallUnit='1' where auditdata.ID = @id; set @i = @i + 1 if (@i % 1000) = 0 begin -- every 1000 updates, commit to database commit transaction -- close current transaction begin transaction -- start a new one end fetch next from cur into @id end commit transaction This approach also has the benefit that we aren't trying to create the join product table of Auditdata and Auditdata_sms_12.

Both auditdata and a_sns should generally have an index on their id column if your are joining based on these columns. There are exceptions. If there is an index on both columns, the next best thing to do would be to run an explain plan on the sql query to find out what the optimizer is doing. At that point you can pinpoint the problem. [kccrosser] How many rows are in each of the tables? Particularly the temp table. First, why not use the following? update auditdata set TATCallType='12',TATCallUnit='1' where ID in (select id from Auditdata_sms_12 ); If there are a lot of rows in your temp table (like hundreds of thousands or millions?), you may need to break this into smaller chunks to avoid creating excessive rollback segments. It has been my experience that as rollback segments start getting really large, the transaction performance degrades exponentially. Best is to break into smaller transactions, like: declare cur cursor for select id from Auditdata_sms_12; declare @id int declare @i int set @i = 0 open cur fetch next from cur into @id begin transaction while @@fetch_status = 0 begin update auditdata set TATCallType='12',TATCallUnit='1' where auditdata.ID = @id; set @i = @i + 1 if (@i % 1000) = 0 begin -- every 1000 updates, commit to database commit transaction -- close current transaction begin transaction -- start a new one end fetch next from cur into @id end commit transaction This approach also has the benefit that we aren't trying to create the join product table of Auditdata and Auditdata_sms_12.