SQL 优化实例

SQL 优化实例

将两张表要 join 的几个字段组合成一个 varchar类型的 字符串, 放到临时表里, 临时表可以带有索引, 再去join

CREATE TABLE #Remove_Plan_Detail
        (ID BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL
        ,Plan_Detail_ID BIGINT)

    CREATE INDEX ix_Plan_Detail_ID ON #Remove_Plan_Detail(
        Plan_Detail_ID ASC
    )

    CREATE TABLE #audit(
        comkey NVARCHAR(255) not null
    )
    CREATE INDEX ix_audit_comkey ON #audit(
        comkey ASC
    )
    
    CREATE TABLE #plandetail(
        comkey NVARCHAR(255) not null,
        Plan_Detail_ID BIGINT not null
    )
    CREATE INDEX ix_plandetail ON #plandetail(
        comkey ASC
    )


    --combine the column together so the join can be faster
    INSERT INTO #audit
    SELECT
     cast(isnull(Source_ID,-1) AS NVARCHAR(10))
    +cast(isnull(PCP_ID,-1) AS NVARCHAR(10))
    +cast(isnull(PCI_ID,-1) AS NVARCHAR(10))
    +cast(isnull(SIT_ID,-1) AS NVARCHAR(10))
    +Source_Record_KEY_NAME
    FROM dbo.Plan_Detail_Audit
    
    INSERT INTO #plandetail
    SELECT 
     cast(isnull(Source_ID,-2) AS NVARCHAR(10))
    +cast(isnull(PCP_ID,-2) AS NVARCHAR(10))
    +cast(isnull(PCI_PCI_ID,-1) AS NVARCHAR(10))
    +cast(isnull(SIT_SIT_ID,-1) AS NVARCHAR(10))
    +Source_Record_KEY_NAME
    ,Plan_Detail_ID
    FROM [$(ODS_Prod)].dbo.Plan_Detail

Chunk Delete, 分块删除:

WHILE 1=1
    BEGIN
    DELETE TOP(10000) pid
    FROM [$(ODS_Prod)].dbo.Plan_Detail pid
    WHERE pid.Plan_Detail_ID IN (SELECT Plan_Detail_ID FROM #Remove_Plan_Detail)
    IF @@rowcount < 10000 BREAK;
    END