58234

Optimize update query

Question:

I have the following query . i am looking to update more than 350 millions of rows

update t set [OC]=r.[OC] from [dbo].[Vente MPX] t with (index (idocdate )) INNER join [dbo].tempOCMPX r with (index (idoc)) on t.[Date Time] between r.[DATE DEBUT] and r.[DATE FIN] --WHERE convert(date,t.[Date Time]) >= '2015-06-01' --AND convert(date,t.[Date Time]) <= '2015-08-31' WHERE t.[Date Time] >= '2015-01-01 00:0:00.000' --AND r.[DATE DEBUT] <= '2015-08-31' and t.[OC] is null

Here the execution plan <a href="https://i.stack.imgur.com/OwddP.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/OwddP.png" data-original="https://i.stack.imgur.com/OwddP.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a> <a href="https://i.stack.imgur.com/3fS8d.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/3fS8d.png" data-original="https://i.stack.imgur.com/3fS8d.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a> This query is been executed for more for 4 days and the number of rows updated is 60 millions of rows. How can I optimize it ?

Answer1:

When you need to update millions of records I advice you following steps:

<ol><li>Split one update statement to many short transactions. Create a loop and update 10 thousand - 1 million records at once (choose you amount). You can limit update query by dates: start from MIN([Date Time]) and one day/week/month on every iteration and update data</li> <li>You can drop index before updating and create it after all updates will complete. It can be faster to build index once after all than rebuild it during updates.</li> </ol>

Recommend

  • Chang the product price when custom checkbox is selected in Shopify
  • Use Maven Replacer Plugin Before WAR is Packaged
  • Restoring Mysql dump from java: why does it hang the process?
  • MongoDB shell script using projection to format date and get local time
  • How can I write a where clause in SQL to filter a DATETIME column by the time of day?
  • OSX - always hide certain files
  • Convert SQLite database to XML
  • Combining two different ActiveRecord collections into one
  • bad substitution shell- trying to use variable as name of array
  • Java color detection
  • IE7 and TinyMCE with Plone
  • WPF Visiblity Binding to Boolean Expression with multiple Variables
  • Conversion from string “a” to type 'Boolean' is not valid
  • Moving Android View and preventing onDraw to be called over and over again
  • Stop Bash Script if Hive Fails
  • Is playing sound in Javascript performance heavy?
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • HTTP/2 streams vs HTTP/1.1 connections
  • print() is showing quotation marks in results
  • Why querying a date BC is changed to AD in Java?
  • Row Count Is Returning the incorrect number using RaptureXML
  • C++ Partial template specialization - design simplification
  • Is there a javascript serializer for JSON.Net?
  • Illegal mix of collations for operation for date/time comparison
  • What is the “return” in scheme?
  • Where to put my custom functions in Wordpress?
  • Release, debug version and Authorization Google?
  • Arrays break string types in Julia
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Buffer size for converting unsigned long to string
  • Hits per day in Google Big Query
  • How to get Windows thread pool to call class member function?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal