CODEWITHSUNDEEP
sqlsql-server
user3548593
user3548593

Reputation: 499

What is the fast way to update with 100 millions+ of records?

I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:

Source Tables :

OC

 CREATE TABLE [dbo].[OC](
    [OC] [nvarchar](255) NULL,
    [DATE DEBUT] [date] NULL,
    [DATE FIN] [date] NULL,
    [Code Article] [nvarchar](255) NULL,
    [INSERTION] [nvarchar](255) NULL,
    [Site] [nvarchar](10) NULL
) ON [PRIMARY]

Vente

CREATE TABLE [dbo].[Vente](
[N°tickets] [nvarchar](6) NULL,
[EAN] [nvarchar](13) NULL,
[Code Caisse] [nvarchar](3) NULL,
[Code Site] [nvarchar](5) NULL,
[Prix de vente TTC] [nvarchar](15) NULL,
[Quantité] [nvarchar](10) NULL,
[Date Time] [datetime] NULL,
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DateOfHire] [date] NULL,
[Promo] [nvarchar](1) NULL,
[KeyVenteSite]  AS ([Code Site]+CONVERT([varchar](8),[Date Time],(112))),
[Code Article] [nvarchar](13) NULL,
[Prix de Revient] [nvarchar](15) NULL,
[Code Structure] [nvarchar](13) NULL,
[Alimentaire] [nvarchar](1) NULL,
[TVA] [nvarchar](2) NULL,
[nbrClientMPX] [nvarchar](max) NULL,
[OC] [nvarchar](50) NULL,
[Comp] [nvarchar](1) NULL,
CONSTRAINT [PK_Vente] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 20) ON [PRIMARY]
) ON [PRIMARY]

The update requirement is as follows:

     SET rowcount 10000
 update t 
  set [OC]=r.[OC]

  from [dbo].[Vente MPX] t 
  inner join [dbo].[OC MPX] r

  on t.[Date Time] between r.[DATE DEBUT] and r.[DATE FIN]
  WHERE convert(date,[Date Time]) BETWEEN '2015-01-01' AND '2015-12-31'
  and t.[OC]is null 
while @@rowcount>0

BEGIN
SET rowcount 10000
 update t 
  set [OC]=r.[OC]

  from [dbo].[Vente MPX] t 
  inner join [dbo].[OC MPX] r

  on t.[Date Time] between r.[DATE DEBUT] and r.[DATE FIN]
  WHERE convert(date,[Date Time]) BETWEEN '2015-01-01' AND '2015-12-31'
  and t.[OC]is null
end

SET rowcount 0

The update took more than 48h and didn't terminate , how to accelerate it ?

Upvotes: 2

Views: 133

Answers (1)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
    SET rowcount 10000

    update t 
    set [OC]=r.[OC]
    from [dbo].[Vente MPX] t 
    INNER join [dbo].[OC MPX] r on t.[Date Time] between r.[DATE DEBUT] and r.[DATE FIN]
    WHERE t.[Date Time] > '2015-01-01 00:0:00.000'
    AND t.[Date Time] < '2016-01-01'
    and t.[OC] is null
end

SET rowcount 0

Ensure you have these indexes:

  • Index on [Vente MPX] for [OC] + [Date Time]
  • Index on [OC MPX] for [OC] + [DATE DEBUT] + [DATE FIN]

Upvotes: 2

Related Questions