CODEWITHSUNDEEP
sqlsql-serverperformance
JJ.
JJ.

Reputation: 9950

is there ANY way I can improve performance on this simple query?

I can't think of a way to speed this up. It's doing a table scan but I kind of have to because I need to update ALL records...

The problem is that this table has MILLIONS of records... like around 30 million.

This is taking about 50 minutes to run. Anyone have any tips on how I can improve this?

  update  A
  set     A.product_dollar_amt = round(A.product_dollar_amt, 2),
          A.product_local_amt = round(A.product_local_amt, 2),
          A.product_trans_amt = round(A.product_trans_amt, 2)
  from    dbo.table A

The table is currently a heap (no clustered index) because it isn't used anywhere else... not sure if creating a clustered index would improve anything here.

Upvotes: 1

Views: 98

Answers (3)

YoYo
YoYo

Reputation: 9415

  1. Parallelize your update by using options or hints that would allow your query to do so;

  2. If not available you can manually split the work in separate concurrently running update sessions. This will require you to be able to efficiently split the work, preferably by using partitions.

  3. Turn off any and all overhead: disable/drop indexes, turn off transaction logging allowing point in time recovery, etc. In your case, indexes won't harm as you are not updating indexed columns, or adding new rows.

    Theoretically with a 16 core system (x2 for hyperthreading available on CPU architecture). A 50 mins job will run in 3mins, if you don't have other bottlenecks (disk io).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Here are three options.

The first mentioned by Randy is to do the work in batches.

The second method is to dump the results into a temporary table and recreate the original table:

select . . . , 
       product_dollar_amt = round(A.product_dollar_amt, 2),
       product_local_amt = round(A.product_local_amt, 2),
       product_trans_amt = round(A.product_trans_amt, 2)
into a_temp
from a;

drop a;

sp_rename 'a_temp', 'a';

Note: This is not guaranteed to be faster, but because logging inserts goes faster than logging updates, it often is. Also, you would need to rebuild indexes and triggers.

Finally, there is the "no-update" solution: create derived values instead:

sp_rename 'A.product_dollar_amt', '_product_dollar_amount', 'COLUMN';
sp_rename 'A.product_local_amt', '_product_local_amt', 'COLUMN';
sp_rename 'A.product_trans_amt', '_product_trans_amt', 'COLUMN';

Then add the columns back as formulas:

alter table A add product_dollar_amt as (round(product_dollar_amt, 2));
alter table A add product_local_amt = round(product_local_amt, 2);
alter table A add product_trans_amt = round(product_trans_amt, 2);

Upvotes: 4

Randy Minder
Randy Minder

Reputation: 48402

You really don't have any alternatives here. You are updating every single row and it's going to take as long as it takes. I can tell you though that updating 30M rows in a single transaction is not a great idea. You could easily blow out your transaction log. And if this table is used by other users, you are probably going to lock them all out until the entire table is updated. You are much better off updating this table in small batches. Overall performance won't be improved but you'll be putting much less strain on your trans log and other users.

Upvotes: 2

Related Questions