Início / dba / Perguntas / 123481
Accepted
mpag
Asked: 2015-12-12 13:59:33 +0800 CST

Implementação Lead/Lag no SQL Server 2008 R2: memória máxima excedida

  • 772

Fundo

Estou tentando estabelecer uma sequência de "visitas", em que se um animal for detectado essencialmente no mesmo local ( General_Location) conta como uma única visita, mas se for para outro lugar e depois voltar, é uma visita adicional ao mesmo local. Portanto, se o animal for detectado em uma Locationsequência
A1, A2, A3, A3, A3, A1, B2, D4, A2
em que, por exemplo, todos os locais A(n) pertencem a General_Location"A", isso terá as primeiras 6 detecções como visita 1 (@A), a seguir como visita 2 (@B), a seguir como visita 3 (@D), a seguir como visita 4 (voltar @A).

Como LAGe LEADnão estão disponíveis no SQL Server 2008R2 (nem está UNBOUNDED PRECEDINGna PARTITIONcláusula ing), estou tentando uma solução alternativa, conforme descrito nesta entrada de blog do SQL Authority .

Estou com problemas de memória (para não mencionar o tempo de computação) com o seguinte:

WITH s AS (
    SELECT
        RANK() OVER (PARTITION BY det.Technology, det.XmitID ORDER BY DetectDate ASC, ReceiverID ASC) as DetID,
        COALESCE(TA.AnimalID, det.Technology+'-'+cast(da.XmitID AS nvarchar), 'BSVALUE999') as AnimalID,
        det.Technology, det.XmitID, DetectDate, det.location as Location, RL.General_Location as GLocation, ReceiverID
    FROM
        Detections_with_Location as det JOIN
        Receiver_Locations as RL
            ON det.Location=RL.Location LEFT OUTER JOIN
        Tagged_Animal as TA
            ON det.AnimalID=TA.AnimalID
)
INSERT INTO ##ttOrder_det (AnimalID, Technology, XmitID, DD, Location, GLocation, ReceiverID, DetID, PrevDD, BinInc)
    SELECT 
        s1.AnimalID, --was a coalesce
        s1.Technology, s1.XmitID, s1.DetectDate, s1.Location, s1.GLocation, s1.ReceiverID,
        s1.DetID, 
        sLAG.DetectDate,
        CASE WHEN sLAG.DetectDate IS NULL
            THEN 1 
            ELSE CASE WHEN sLAG.GLocation = s1.GLocation
                THEN 0
                ELSE 1
            END
        END AS BinInc
    FROM s as s1
    LEFT OUTER JOIN s AS sLAG ON
        s1.DetID = sLAG.DetID + 1 AND
        s1.AnimalID= sLAG.AnimalID --and s.Technology=sLAG.Technology and s.XmitID=sLAG.XmitID;

Como vários usuários (@MartinSmith, @Frisbee) mencionaram ou aludiram, o uso de AnimalIDnão é a chave primária completa de Tagged_Animal, nem é definido em uma restrição UNIQUE. No entanto, a contagem de linhas na tabela onde A.AnimalID=B.AnimalID AND A.TagSN<B.TagSNé (atualmente) zero. Para tornar essa consulta robusta, eu teria que fazer com que fosse único (ou apenas retirar TagSN do PK).

Definições de tabela e índice

##ttOrder_det (tabela temporária)

Os índices são criados atualmente antes de preencher a tabela; Estou passando por testes em que mudo a criação NONCLUSTEREDnão UNIQUEindexada para uma posição depois que a tabela temporária é preenchida.

CREATE TABLE ##ttOrder_det (
    AnimalID nvarchar(50) not null,
    Technology varchar(25) not null,
    XmitID int not null,
    DD DateTime not null,
    [Location] [nvarchar](255) NULL,
    [GLocation] nvarchar(255) NULL,
    PrevDD DateTime NULL,
    ReceiverID int not null,
    DetID int NOT NULL,
    BinInc int NULL,
    BinNum int NULL,
CONSTRAINT [PK_ttRDA] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC, [DD] ASC, ReceiverID ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX NIX_F on ##ttOrder_det (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_VTC ON ##ttOrder_det (ReceiverID ASC, AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_TCD ON ##ttOrder_det (AnimalID ASC, DD ASC);
CREATE NONCLUSTERED INDEX NIX_R ON ##ttOrder_det (DetID ASC);
CREATE NONCLUSTERED INDEX NIX_A ON ##ttOrder_det (GLocation ASC);
CREATE NONCLUSTERED INDEX NIX_DD ON ##ttOrder_det (DD, PrevDD);
CREATE UNIQUE INDEX UX_CTR ON ##ttOrder_det (AnimalID ASC, DetID ASC);
CREATE NONCLUSTERED INDEX NIX_Bi ON ##ttOrder_det (BinInc ASC);
CREATE NONCLUSTERED INDEX NIX_CT ON ##ttOrder_det (XmitID ASC, Technology ASC);

Tagged_Animal

CREATE TABLE [dbo].[Tagged_Animal](
    [DateTagged] [datetime] NULL,
    [AnimalID] [nvarchar](50) NOT NULL,
    [TagSN] [nvarchar](50) NOT NULL,
    [XmitID] [int] NULL,
    [Technology] [varchar](25) NULL,
    [Animal_SubType] [nvarchar](50) NULL,
    [Species] [nvarchar](30) NULL,
    [StudyID] [nvarchar](50) NULL,
    [Weight] [float] NULL,
    [Length] [int] NULL,
    [Length_Type] [nvarchar](50) NULL,
    [Date_Released] [datetime] NULL,
    [Release_Location] [nvarchar](50) NULL,
    [Lat] [float] NULL,
    [Lon] [float] NULL,
    [Course_Dist_km] [float] NULL,
    [Sex] [nvarchar](255) NULL,
    [Life_Stage] [nvarchar](255) NULL,
    [Marking_Method] [nvarchar](255) NULL,
    [Tag_Type] [varchar](30) NULL,
    [Notes] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Tagged_Animal] PRIMARY KEY CLUSTERED 
(
    [AnimalID] ASC,
    [TagSN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [I_TF_TagCode] ON [dbo].[Tagged_Animal] 
(
    [XmitID] ASC,
    [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Detecções_com_Localização

CREATE TABLE [dbo].[Detections_with_Location](
    [AnimalID] [nvarchar](50) NOT NULL,
    [XmitID] [int] NOT NULL,
    [Technology] [varchar](25) NOT NULL,
    [DetectDate] [datetime] NOT NULL,
    [ReceiverID] [int] NOT NULL,
    [Data] [float] NULL,
    [Units] [varchar](50) NULL,
    [Location] [nvarchar](255) NULL,
    [StartD] [datetime] NULL,
    [StopD] [datetime] NULL,
    [fname] [nvarchar](255) NULL,
    [notes] [nvarchar](max) NULL,
 CONSTRAINT [PK_dlwk] PRIMARY KEY CLUSTERED 
(
    [ReceiverID] ASC,
    [Technology] ASC,
    [XmitID] ASC,
    [DetectDate] ASC,
    [AnimalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_VTC] ON [dbo].[Detections_with_Location] 
(
    [ReceiverID] ASC,
    [AnimalID] ASC,
    [XmitID] ASC,
    [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCpi] ON [dbo].[Detections_with_Location] 
(
    [XmitID] ASC,
    [Technology] ASC
)
INCLUDE ( [DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCD] ON [dbo].[Detections_with_Location] 
(
    [AnimalID] ASC,
    [XmitID] ASC,
    [Technology] ASC,
    [DetectDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_F] ON [dbo].[Detections_with_Location] 
(
    [AnimalID] ASC
)
INCLUDE ( [XmitID],
[Technology],
[DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_DSS] ON [dbo].[Detections_with_Location] 
(
    [ReceiverID] ASC,
    [Location] ASC,
    [StartD] ASC,
    [StopD] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Receptor_Locais

CREATE TABLE [dbo].[Receiver_Locations](
    [Region] [nvarchar](50) NULL,
    [Location_Long] [nvarchar](50) NULL,
    [Location] [nvarchar](50) NOT NULL,
    [Lat] [float] NULL,
    [Lon] [float] NULL,
    [Altitude] [float] NULL,
    [Elevation] [float] NULL,
    [RiverKm] [float] NULL,
    [LocationType] [nvarchar](50) NULL,
    [General_Location] [nvarchar](50) NULL,
    [Nearest_Access] [nvarchar](50) NULL,
    [Responsible_Agent] [nvarchar](50) NULL,
    [Agent_Phone] [nvarchar](255) NULL,
    [Agent_Email] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Receiver_Locations] PRIMARY KEY CLUSTERED 
(
    [Location] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Tamanhos de tabela

Tagged_Animals: 10 mil
Detections_with_Location: mais de 46 milhões de entradas
Receiver_Locations: 800

Erros Específicos Recebidos

  1. Não foi possível alocar espaço para o objeto 'dbo.SORT armazenamento de execução temporária: 140737631617024' no banco de dados 'tempdb' porque o grupo de arquivos 'PRIMARY' está cheio. Crie espaço em disco excluindo arquivos desnecessários, descartando objetos no grupo de arquivos, adicionando arquivos adicionais ao grupo de arquivos ou definindo o crescimento automático para arquivos existentes no grupo de arquivos.

  2. O log de transações do banco de dados 'tempdb' está cheio. Para descobrir por que o espaço no log não pode ser reutilizado, consulte a coluna log_reuse_wait_desc em sys.databases (tempdb ACTIVE_TRANSACTION)

  3. Ocorreu um erro ao executar o lote. A mensagem de erro é: Exceção do tipo 'System.OutOfMemoryException' foi lançada --(se estiver fazendo suma seleção direta após cerca de 33 milhões de registros).

(Estimado) Plano de Execução Resumo do código inicial

INSERTcusto 0%
SEQUENCEcusto 0%, mas com base em 9 subetapas diferentes. Essas subetapas (com custos típicos) são um Index Insert(2%), seguido por Sort(8%), seguido por Table Spool(2%). NIX_Atem um custo de 9% para Index Inserte NIX_TCDnem NIX_Ftem Sortdegrau; O Table Spoolpara NIX_Fé grátis.
O Clustered Index Insertcusto é de 10%.
Há também um Sortcusto de 2% e um Parallelismcusto de 1% para distribuição de streams.
Para o SEQUENCEcusto, parece aumentar para 95%, com outras etapas custando 13%, então obviamente há alguns "erros" de arredondamento em algum lugar, provavelmente principalmente nos 14% da sequência Index Insert- Sort- .Table Spool

Notas/Ref.

Implementação LAG/LEAD baseada na entrada do blog SQL Authority
Veja também este Stackexchange Thread

Minhas perguntas

  1. Alguma sugestão para melhorar?

  2. Posso particionar também quando juntar as cópias de s?

  3. As coisas melhorariam se eu scriasse uma tabela temporária discreta e a indexasse apropriadamente?

  4. Seria mais eficiente criar os não- UNIQUEíndices nas tabelas temporárias depois que todas as inserções forem realizadas? Presumo que os índices UNIQUE(e, portanto, PRIMARY KEY) devem ser criados antecipadamente para evitar violações de restrição chave.

Para responder a uma das minhas próprias perguntas

  1. Sim, sim, seria. Depois de mais otimizações
    • 21 minutos para preencher a tabela temporária com dados
    • 1 minuto para indexar

Anteriormente, esse processo durava pelo menos 1,5 horas, apresentava erros e não produzia uma tabela de resultados. Antes de começar a mexer na lógica da consulta, na verdade, levaria mais de 4 horas antes de ocorrer um erro.

Especificações do servidor:

Processador: Xeon E3-1240 V2 a 3,4 GHz (4 núcleos/8 threads)
Memória: 16 GB
Arquivo de paginação: 16 GB em SSD de 111 GB (52 GB grátis)
tempdb + meu banco de dados em SSD de 223 GB (119 GB grátis)

Status atual

Veja minha solução/resposta postada.

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Estou enviando isso como uma resposta, pois atualmente estou evitando erros do tipo falta de memória, bem como reduzi significativamente o tempo de execução (eram mais de 4 horas, terminando em falha; agora é 1,25 horas, terminando em sucesso ). No entanto, tenho certeza de que, após cerca de 10 milhões de registros, esse problema pode ocorrer novamente, portanto, gostaria de receber comentários ou respostas adicionais com o objetivo de tornar isso mais eficiente em termos de memória para o futuro.

    A "solução" para este ponto foi eliminar campos desnecessários e especialmente índices do design das tabelas temporárias. Além disso, a criação de índice para chaves sem restrição foi adiada até que a tabela fosse preenchida.

    Para resolver o problema (apontado primeiro por @MartinSmith) de particionamento que não corresponde ao posterior JOIN, criei um campo em uma subconsulta que usei para PARTITIONing e para JOINing.

    Código para o Q

    set nocount on;
SET XACT_ABORT ON;
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'started' as reason; --last run 2015-12-16 18:22:02
GO

CREATE TABLE ##ttOrder_det (
    AnimalID nvarchar(50) not null,
    DD DateTime not null,
    ReceiverID int NOT NULL,
    Location nvarchar(255) NOT NULL,
    GLocation nvarchar(255) NULL,
    DetID int NOT NULL,
    BinIncORNum int NULL, -- first pass=Inc, second pass=Num
CONSTRAINT [PK_ttRDA] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC, [DD] ASC, ReceiverID ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE UNIQUE INDEX UX_CTR ON ##ttOrder_det (AnimalID ASC, DetID ASC);
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'created first temporary table' as reason;  --last run 2015-12-16 18:22:02
GO
WITH s AS (
    SELECT 
        AnimalID, DetectDate,
        ReceiverID, Location, GLocation,
        ROW_NUMBER() OVER (
            PARTITION BY AnimalID ORDER BY DetectDate ASC, ReceiverID ASC
        ) as DetID
    FROM (
        SELECT 
            COALESCE (
                TF.AnimalID,
                Det.Technology+'-'+cast(Det.XmitID AS nvarchar(10)),
                Det.AnimalID,
                N'BSVALUE999'
            ) as AnimalID,
            DetectDate,
            ReceiverID,
            COALESCE (
                Det.location,
                N'Unknown Location'
            ) as Location,
            COALESCE (
                ML.General_Location,
                N'Invalid General Location - Orphaned Receiver'
            ) as GLocation
        FROM
            Detections_with_Location as Det LEFT OUTER JOIN
            Receiver_Locations as ML ON Det.Location=ML.Location LEFT OUTER JOIN
            Tagged_Animal as TF ON Det.AnimalID=TF.AnimalID
    ) AS T
)
INSERT INTO ##ttOrder_det (AnimalID, DD, ReceiverID, Location, GLocation, DetID, BinIncORNum)
    SELECT 
        s1.AnimalID,
        s1.DetectDate, s1.ReceiverID, s1.Location, s1.GLocation,
        s1.DetID, 
        CASE WHEN sLg.DetectDate IS NULL
            THEN 1 
            ELSE CASE WHEN sLg.GLocation = s1.GLocation
                THEN 0
                ELSE 1
            END
        END AS BinInc
    FROM s as s1
    LEFT OUTER JOIN s AS sLg ON
        s1.AnimalID= sLg.AnimalID AND
        s1.DetID = sLg.DetID + 1
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'filled first temp table' as reason,
   COUNT(*) as SizeOfFirstTemp FROM ##ttOrder_det; --2015-12-16 18:43:03, 46627879
GO
CREATE NONCLUSTERED INDEX NIX_F on ##ttOrder_det (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_R ON ##ttOrder_det (DetID ASC);
--dropped several additional indices: `NIX_`s VTC, TCD, A, DD, Bi
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'indexed first temp table' as reason; --2015-12-16 18:44:12
GO

    Comentários rápidos

    Eu mudei para usar ROW_NUMBERem vez de RANK. Isso não é determinístico, mas pelo menos não resultará em "empates", o que resultaria na quebra da LAGimplementação nesse ponto. Laços não deveriam existir, mas isso é apenas uma proteção futura contra General_Locations que estão muito próximos e co-detectam a mesma transmissão.

    Again, as two users pointed out in the comments above, I'm not using the full PK of the Tagged_Animal table, so there's a possibility that there be a JOIN with an ambiguous AnimalID. However, at present, both AnimalID and TagSN are UNIQUE, albeit unconstrained.

    I was planning on dropping the ReceiverID field in favor of using the Location, but I had a period in which there were two Receivers deployed at the same location (one receiver was presumed lost but later found) which did indeed detect the same animal at the same time

    Follow-up code to complete the task

    CREATE TABLE ##ttOrder_det2 (
    AnimalID nvarchar(50) not null,
    DetID int NOT NULL,
    BinNum int NULL,
CONSTRAINT [PK_ttRDA2] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC, [DetID] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'second temp table created' as reason; --2015-12-16 18:44:15
GO
-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs. 
BEGIN TRANSACTION
  BEGIN TRY
   DECLARE @AnimalID as nvarchar(50);
    DECLARE @DetID as int;
    DECLARE @BinInc as int;
    DECLARE @BinNum as int;
    DECLARE @AnimalVisit as CURSOR;
    SET @AnimalVisit = CURSOR FOR SELECT AnimalID, DetID, BinIncORNum FROM ##ttOrder_det ORDER BY AnimalID, DetID;
    OPEN @AnimalVisit;
    FETCH NEXT FROM @AnimalVisit INTO @AnimalID, @DetID, @BinInc;
    WHILE @@FETCH_STATUS = 0
      BEGIN
        IF (@DetID <= 1) SET @BinNum = 0;
        SET @BinNum += @BinInc;
        INSERT INTO ##ttOrder_det2 (AnimalID, DetID, BinNum) VALUES (@AnimalID, @DetID, @BinNum);
        FETCH NEXT FROM @AnimalVisit INTO @AnimalID, @DetID, @BinInc;
      END
    CLOSE @AnimalVisit;
    DEALLOCATE @AnimalVisit;
  END TRY
  BEGIN CATCH
    exec sp_lock; -- added to display the open locks after the timeout
--    exec sp_who2; -- shows the active processes
    EXECUTE usp_GetErrorInfo;
    --RETURN -- ignoring this error for brevity
        -- Test XACT_STATE:
        -- If 1, the transaction is committable.
        -- If -1, the transaction is uncommittable and should 
        --     be rolled back.
        -- XACT_STATE = 0 means that there is no transaction and
        --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        SELECT
            cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
            'The transaction is in an uncommittable state. Rolling back transaction.' as reason
            SET XACT_ABORT off
            RETURN -- 1 --error
        ROLLBACK TRANSACTION;
    END;
    -- Test whether the transaction is committable.
    IF (XACT_STATE()) = 1
    BEGIN
        SELECT
            cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
            'The transaction is committable. Committing transaction.' as reason
        COMMIT TRANSACTION;   
    END;
  END CATCH;
  IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO
SELECT 
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'filled second temp table' as reason,
    COUNT(*) as Table2Size from ##ttOrder_det2; --2015-12-16 19:11:17, 46627879
GO
CREATE NONCLUSTERED INDEX NIX_CT2 ON ##ttOrder_det2 (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_R2 ON ##ttOrder_det2 (DetID ASC);
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'indexed second temp table' as reason;
--EXEC tempdb.dbo.usp_PruneTemp;
GO
BEGIN TRANSACTION
    BEGIN TRY
        UPDATE a
            SET a.BinIncORNum=b.BinNum
        FROM ##ttOrder_det AS a
        INNER JOIN ##ttOrder_det2 AS b ON 
            a.AnimalID=b.AnimalID AND a.DetID=b.DetID;
        SELECT
            cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
            'backfilled first temp table with info from second table' as reason,
            @@ROWCOUNT as EntriesAffected;  --2015-12-16 19:19:54, 46627879
        DROP TABLE ##ttOrder_det2;
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        SELECT name, log_reuse_wait_desc FROM sys.databases
        SELECT
          cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
          'ERROR: backfilling first temp table, see sys.databases info' as reason;
        EXECUTE usp_GetErrorInfo;
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET XACT_ABORT off
        RETURN --1 --error
    END CATCH
--IF @@TRANCOUNT > 0 COMMIT TRANSACTION
GO
CREATE TABLE derived__AnimalVisits (
    AnimalID nvarchar(50) not null,
    DetectDate DateTime not null,
    ReceiverID int NOT NULL,
    Location nvarchar(255) NOT NULL,
    GeneralLocation nvarchar(255) NULL,
    DetOrder int NOT NULL,
    VisitNum int NOT NULL,
CONSTRAINT [PK_dFV] PRIMARY KEY CLUSTERED 
    (AnimalID ASC, DetectDate ASC, ReceiverID ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE UNIQUE INDEX UX_CTR ON derived__AnimalVisits (AnimalID ASC, DetOrder ASC);
SELECT 
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'created persistent derived table' as reason; --2015-12-16 19:20:53
GO

--DECLARE @FVError as int = 0;
BEGIN TRY
    INSERT INTO derived__AnimalVisits (AnimalID, DetectDate, ReceiverID, Location, GeneralLocation, DetOrder, VisitNum)
        SELECT AnimalID, DD, ReceiverID, Location, GLocation, COALESCE(DetID,0), COALESCE(BinIncORNum,0) FROM ##ttOrder_det;
END TRY
BEGIN CATCH
    SELECT
        cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
        'ERROR: filling persistent derived table' as reason;
    EXECUTE usp_GetErrorInfo;
    SET XACT_ABORT off
    RETURN --1 --error
END CATCH
CREATE NONCLUSTERED INDEX NIX_CT ON derived__AnimalVisits (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_VTC ON derived__AnimalVisits (ReceiverID ASC, AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_TCD ON derived__AnimalVisits (AnimalID ASC, DetectDate ASC);
CREATE NONCLUSTERED INDEX NIX_R ON derived__AnimalVisits (DetOrder ASC);
CREATE NONCLUSTERED INDEX NIX_Bi ON derived__AnimalVisits (VisitNum ASC);
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'indexed / now cleaning up' as reason; --2015-12-16 19:31:18
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[##ttOrder_det2]') AND xtype=N'U')
    DROP TABLE tempdb.[dbo].[##ttOrder_det2];
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[##ttOrder_det]') AND xtype=N'U')
    DROP TABLE tempdb.[dbo].[##ttOrder_det];
SET XACT_ABORT off
--cleaning up of transaction logs, etc done at 2015-12-16 19:39:07
    • 5

relate perguntas