Sql-server – Efficient Value Update with Large Database

optimizationperformancequery-performancesql server

I have a table as large as 8GB and with 20M records. There is an int field called mth. The mth field stores date information in form of YYYYMM, I want to transform the mth field to consecutive integers. So I use a formula to get the year and month from field mth and calculate the month order, specifically, I use the following code:

create function mth_to_num(@month int)
returns int
as 
begin
   return(round(@month/100,0)*12+@month-100*round(@month/100,0))
end

Then I use the following code to update the value in the huge table

update full_orig_month_Q1_1999 
set mth_order = dbo.mth_to_num(period)
Go

However, the code took quite long time to execute, roughly 2-3 minutes. My system is windows 10 64 bit with SQL server 2016. Is there any way I can speed it up?

Another problem is that I found SQL server occupying as much as 8GB in the database after executing the above query. Does it need so much memory? How can I release them?

Thanks for your help in advance!

Jason

Best Answer

When possible don't use scalar user defined functions (UDF) in queries. They force the entire plan to be serial, they can cause a lot of additional CPU resources to be used, they are a black box to the optimizer and can lead to cardinality estimation issues in other parts of the plan, and they can result in needlessly large memory grants.

Try running your UPDATE query without the UDF. Just take the code that you have and put it into the UPDATE query directly. Something like this:

update full_orig_month_Q1_1999 
set mth_order = round(period/100,0)*12+period-100*round(period/100,0);

Does that improve the runtime and memory usage?

Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id_int);

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

This very much depends on the data present in the table at the time of the UPDATE.

If, for instance, the table has 1,000,000 rows where value1 = 'foo' and only 1 row where value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; will only actually update a single row. Whereas UPDATE table1 SET value1='foo' WHERE value2='bar'; would update 1,000,001 rows.

If you are talking about a table with only two rows, the difference is entirely negligible.

If you were using SQL Server and had a filtered index like WHERE value2 = 'bar' AND value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; would most likely use the index instead of scanning the entire table.

Best Answer

Related Solutions

Mysql – Update Large Table

Answer to question 1

Answer to question 2

CAVEAT

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

Related Question