CODEWITHSUNDEEP
azure-sql-database
Dieter
Dieter

Reputation: 121

How to update a large amount of records in SQL Azure everyday

I have a table where i have to update more then 10K records everyday. This takes a lot of time when using a simple update statement. Is there a faster way in SQL azure to batch update records?

regards Dieter

Upvotes: 1

Views: 432

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

SQL Azure is not radically different with exception of few things.when a query is slow ,you have to consider so many factors same like OnPremises .Below is the way you can start troubleshooting..

1.) whether there are any locking/Blockings 

select blocking_session_id,wait_type,wait_time,last_Wait_type
 from sys.dm_exec_requests qs
cross apply
sys.dm_exec_sql_text(qs.sql_handle)

It depends on from where you are trying to do Bulk load,above query should show help you in detemining in what is the wait type,is it network,DTU..

2.) While working with Azure DBs,you have to conisder one more factor called DTUS(simply put how much of transactions you can run per second).

you can use below query to see if your tier is choking ..

SELECT 
    (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats

if you wait type in first query is disk,ram,IO and DTU query consistently shows DTU percent >90,then you may need to upgrade your database tier too or consider tuning queries

If none of the above helps,you can also post your execution plan tooo

Upvotes: 1

Related Questions