Raj's questions

Fui encarregado de escrever uma consulta de atualização para atualizar uma tabela com mais de 850 milhões de linhas de dados. Aqui estão as estruturas da tabela:

Tabelas de origem:

    CREATE TABLE [dbo].[SourceTable1](
    [ProdClassID] [varchar](10) NOT NULL,
    [PriceListDate] [varchar](8) NOT NULL,
    [PriceListVersion] [smallint] NOT NULL,
    [MarketID] [varchar](10) NOT NULL,
    [ModelID] [varchar](20) NOT NULL,
    [VariantId] [varchar](20) NOT NULL,
    [VariantType] [tinyint] NULL,
    [Visibility] [tinyint] NULL,
 CONSTRAINT [PK_SourceTable1] PRIMARY KEY CLUSTERED 
(
    [VariantId] ASC,
    [ModelID] ASC,
    [MarketID] ASC,
    [ProdClassID] ASC,
    [PriceListDate] ASC,
    [PriceListVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
    )

CREATE TABLE [dbo].[SourceTable2](
    [Id] [uniqueidentifier] NOT NULL,
    [ProdClassID] [varchar](10) NULL,
    [PriceListDate] [varchar](8) NULL,
    [PriceListVersion] [smallint] NULL,
    [MarketID] [varchar](10) NULL,
    [ModelID] [varchar](20) NULL,
 CONSTRAINT [PK_SourceTable2] 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 = 91) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SourceTable1contém 52 milhões de linhas de dados e SourceTable2contém 400.000 linhas de dados.

Aqui está a TargetTableestrutura

CREATE TABLE [dbo].[TargetTable](
    [ChassisSpecificationId] [uniqueidentifier] NOT NULL,
    [VariantId] [varchar](20) NOT NULL,
    [VariantType] [tinyint] NULL,
    [Visibility] [tinyint] NULL,
 CONSTRAINT [PK_TargetTable] PRIMARY KEY CLUSTERED 
(
    [ChassisSpecificationId] ASC,
    [VariantId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 71) ON [PRIMARY]
    ) ON [PRIMARY]

A relação entre essas tabelas é a seguinte:

• SourceTable1.VariantIDestá relacionado aTargetTable.VariantID
• SourceTable2.IDestá relacionado aTargetTable.ChassisSpecificationId

O requisito de atualização é o seguinte:

1. Obtenha os valores de e VariantTypepara cada , tendo o valor máximo na coluna.VisibilitySourceTable1VariantIDPriceListVersion
2. Obtenha o valor da IDcoluna de SourceTable2onde os valores de , ModelIDe correspondem aos de .ProdClassIDPriceListDateMarketIDSourceTable1
3. Agora atualize o TargetTablecom os valores para VariantTypee Visibilityonde as ChassisspecificationIDcorrespondências SourceTable2.IDe VariantIDcorrespondênciasSourceTable1.VariantID

O desafio é fazer essa atualização na produção ao vivo, com o mínimo de travamento. Aqui está a consulta que montei.

-- Check if Temp table already exists and drop if it does
IF EXISTS(
        SELECT NULL 
        FROM tempdb.sys.tables
        WHERE name LIKE '#CSpec%'
      )
BEGIN
    DROP TABLE #CSpec;
END;

-- Create Temp table to assign sequence numbers
CREATE Table #CSpec
(
    RowID int,
    ID uniqueidentifier,
    PriceListDate VarChar(8),
    ProdClassID VarChar(10),
    ModelID VarChar(20),
    MarketID Varchar(10)
 );

-- Populate temp table 
INSERT INTO #CSpec
SELECT ROW_NUMBER() OVER (ORDER BY MarketID) RowID,
       CS.id, 
       CS.pricelistdate, 
       CS.prodclassid, 
       CS.modelid, 
       CS.marketid 
FROM   dbo.SourceTable2 CS 
WHERE CS.MarketID IS NOT NULL;

-- Declare variables to hold values used for updates
DECLARE @min            int, 
        @max            int,
        @ID             uniqueidentifier,
        @PriceListDate  varchar(8),
        @ProdClassID    varchar(10),
        @ModelID        varchar(20),
        @MarketID       varchar(10);
-- Set minimum and maximum values for looping
SET @min = 1;
SET @max = (SELECT MAX(RowID) From #CSpec);

-- Populate other variables in a loop
WHILE @min <= @max
BEGIN
    SELECT 
        @ID = ID,
        @PriceListDate = PriceListDate,
        @ProdClassID = ProdClassID,
        @ModelID = ModelID,
        @MarketID = MarketID
    FROM #CSpec
    WHERE RowID = @min;  

-- Use CTE to get relevant values from SourceTable1 
    ;WITH Variant_CTE AS
    (
    SELECT  V.variantid, 
            V.varianttype, 
            V.visibility,
            MAX(V.PriceListVersion) LatestPriceVersion
    FROM    SourceTable1 V 
    WHERE       V.ModelID = @ModelID
            AND V.ProdClassID = @ProdClassID
            AND V.PriceListDate = @PriceListDate
            AND V.MarketID = @MarketID
    GROUP BY
            V.variantid, 
            V.varianttype, 
            V.visibility
    )

-- Update the TargetTable with the values obtained in the CTE
    UPDATE      SV 
        SET     SV.VariantType = VC.VariantType, 
                SV.Visibility = VC.Visibility
    FROM        spec_variant SV 
    INNER JOIN  TargetTable VC
    ON          SV.VariantId = VC.VariantId
    WHERE       SV.ChassisSpecificationId = @ID
                AND SV.VariantType IS NULL
                AND SV.Visibility IS NULL;

    -- Increment the value of loop variable
    SET @min = @min+1;
END
-- Clean up
DROP TABLE #CSpec

Demora cerca de 30 segundos quando defino o limite de iterações para 10, codificando o valor da @maxvariável. No entanto, quando aumento o limite para 50 iterações, leva quase 4 minutos para ser concluído. Estou preocupado que o tempo de execução necessário para 400.000 iterações será executado em vários dias na produção. No entanto, isso ainda pode ser aceitável, se o TargetTablenão for bloqueado, impedindo que os usuários o acessem.

Todas as entradas são bem-vindas.

Obrigado, Raj