1

I have a script that, at some point, updates several large tables. The tables are independent of each other, have 40+mil rows and each row is updated by resetting an integer value. Something like:

update table table_with_40_mil_rows set integer_column=1

update table table_with_70_mil_rows set integer_column=1

etc.

Processing of each such statement takes between 8 and 15 minutes.

Is there a way to run 2 or more of these table updates in parallel? The disks are pretty fast and 8+ cores are available.

Thanks

ofcoursedude
  • 456
  • 4
  • 10
  • If you have multiple cores, the engine will determine if parallelism should be used depending on your server settings. Its probably not a great idea to run 5 different queries based on an ID column. If you are 'resetting' an entire column, you probably have an XY problem. – dfundako Jan 23 '19 at 15:12
  • I believe it will do so only for single statements such as select query or even a merge statement with cte(s). Not for several statements one after another. – ofcoursedude Jan 23 '19 at 15:15
  • XY problem? What do you mean? I basically need to change referenced "owner" (from a user table) to consolidate to one value (=single owner) – ofcoursedude Jan 23 '19 at 15:19
  • 1
    https://en.wikipedia.org/wiki/XY_problem If you are consistently 'resetting' a column to a simple, mostly arbitrary value across 70 mil rows, solve the issue of why you need to update every single row to the same value instead of speeding up the process. – dfundako Jan 23 '19 at 15:24
  • Because it's a migration scenario in a legacy database. – ofcoursedude Jan 23 '19 at 15:29

2 Answers2

1

Not as long as they are in a single script running on the same connection. SQL Server cannot be made to process a single script in an asynchronous, threaded fashion. At least not in TSQL. Maybe in a CLR procedure.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Good mention of `CLR`. I'd be interested if it could do it as well. Also, you should take a stab at [this one](https://stackoverflow.com/questions/54221468/what-can-i-use-in-a-sql-query-to-help-me-determine-why-my-query-is-not-returning/54226185#54226185) and see if you can translate something i missed. – S3S Jan 23 '19 at 17:31
1

Vanilla T-SQL will not give so much possibilities to run few updates in parallel. As exceptions - asynchronous service broker, but it is advanced topic.

The more or less easy ways:

  • SSIS package that executes few T-SQL tasks in parallel
  • Few SQL Agent Jobs that triggered by one master job at the same time
  • Few SSMS windows that run queries in parallel
Alexander Volok
  • 5,630
  • 3
  • 17
  • 33