Update statement on big table

Question

Update statement on big table

Eshwar 216

HI,

This update statement is taking lot of time, the data in the staging table is ~7-10M and account table is ~1M.

Can you help what can be done to speed it up?

UPDATE STG
SET account_id = A.account_id,
	account_agency_id = A.account_agency_id,
	account_number = A.account_number
FROM ETL.Stg_data STG
INNER JOIN account A ON RTRIM(LTRIM(SUBSTRING(STG.Data, 23, 20))) = A.account_number
WHERE STG.RecordType > 30

Thanks,

Eshwar.

Viorel 122.6K Reputation points

2023-09-11T18:54:11.5166667+00:00

Is it possible to improve the Stg_data table by adding a computed column using the RTRIM(LTRIM(SUBSTRING(STG.Data, 23, 20))) formula, use it in your query, and define indices for the new column, as well as A.account_number and STG.RecordType?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-09-11T20:56:26.99+00:00
As Viorel says, it is possible that improved indexing could help, but with the numbers you have given I am not so sure. It seems like it has to be a hash join anyway. How many rows does the condition on RecordType qualify?

I would be more inclined to look at disk performance and such things. Exactly how long time is "lot of time"? What specs does your machine have?

If you run this update in a fresh query window and then run

SELECT TOP 5 * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY wait_time_ms DESC

What result do you see?

Also, please share the output of SELECT @@version
Eshwar 216 Reputation points

2023-09-12T03:39:57.4766667+00:00

Thanks Both

I am adding that column and index.

Meanwhile @Erland Sommarskog PFA attached..

Eshwar 216

Added new column account_number_1 and added index.

create index idx_account_number_1 on ETL.Stg_data(account_number_1)

Removed RecordType condition but no difference in timing:

UPDATE STG
SET account_id = A.account_id,
	account_agency_id = A.account_agency_id,
	account_number = A.account_number
FROM ETL.Stg_data STG
INNER JOIN account A ON account_number_1 = A.account_number

LiHongMSFT-4306 31,571 Reputation points

2023-09-28T08:17:31.98+00:00

Hi @Eshwar

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!

2 answers

Your answer

Viorel 122.6K Reputation points

2023-09-11T18:54:11.5166667+00:00

Is it possible to improve the Stg_data table by adding a computed column using the RTRIM(LTRIM(SUBSTRING(STG.Data, 23, 20))) formula, use it in your query, and define indices for the new column, as well as A.account_number and STG.RecordType?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-09-11T20:56:26.99+00:00

As Viorel says, it is possible that improved indexing could help, but with the numbers you have given I am not so sure. It seems like it has to be a hash join anyway. How many rows does the condition on RecordType qualify?

I would be more inclined to look at disk performance and such things. Exactly how long time is "lot of time"? What specs does your machine have?

If you run this update in a fresh query window and then run

SELECT TOP 5 * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY wait_time_ms DESC

What result do you see?

Also, please share the output of SELECT @@version
Eshwar 216 Reputation points

2023-09-12T03:39:57.4766667+00:00

Thanks Both

I am adding that column and index.

Meanwhile @Erland Sommarskog PFA attached..
Eshwar 216 Reputation points

2023-09-12T04:20:16.9166667+00:00

Added new column account_number_1 and added index.

create index idx_account_number_1 on ETL.Stg_data(account_number_1)

Removed RecordType condition but no difference in timing:

UPDATE STG SET account_id = A.account_id, account_agency_id = A.account_agency_id, account_number = A.account_number FROM ETL.Stg_data STG INNER JOIN account A ON account_number_1 = A.account_number
LiHongMSFT-4306 31,571 Reputation points

2023-09-28T08:17:31.98+00:00

Hi @Eshwar

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

The top wait is SLEEP_BPOOL_STEAL, you can read about it here: https://www.sqlskills.com/help/waits/sleep_bpool_steal/

This suggests that you are short on memory. How much memory is there in the machine? What does sp_configure 'max server memory' say? How big are the tables in megabytes?

Answer 2

soumen barua 5

Hi,

Can you try following and see if it made difference:

create clustered index c1 on stg(data)

You will need a clustered index on that joining column to get this update working that is improve performance. Preferably both tables having clustered indices.

Also, try to remove string functions at ON clause by using a stored procedure to do the update for better performance. Functions will cause table scan.

Rgds.

Share via

Update statement on big table

2 answers

Your answer