場(chǎng)景

最近工作中，發(fā)現(xiàn)某同步JOB在執(zhí)行中經(jīng)常拋出SQL執(zhí)行超時(shí)的問題，查看日志發(fā)現(xiàn)每次SQL執(zhí)行的時(shí)間都是線性增長(zhǎng)的，循環(huán)執(zhí)行50次以后執(zhí)行時(shí)間甚至超過了5分鐘

JOB執(zhí)行流程分析

首先，對(duì)于JOB流程進(jìn)行分析，查看是否是JOB設(shè)計(jì)上的問題

通過對(duì)流程的分析，發(fā)現(xiàn)每次獲取的需要同步的數(shù)據(jù)最多只有一萬條，不存在大數(shù)據(jù)寫入導(dǎo)致超時(shí)的問題。

那么在對(duì)獲取詳細(xì)信息這個(gè)過程進(jìn)行分析，發(fā)現(xiàn)關(guān)聯(lián)的表中最多的數(shù)據(jù)已經(jīng)上億了，可能是這里導(dǎo)致了整體SQL執(zhí)行變慢的原因。這里能算可疑點(diǎn)一。

再接著往下一個(gè)流程看與表B對(duì)比重復(fù)數(shù)據(jù)時(shí)，隨著循環(huán)執(zhí)行表B的數(shù)據(jù)會(huì)越來越多，那么會(huì)不會(huì)這里是導(dǎo)致循環(huán)執(zhí)行下執(zhí)行時(shí)間稱線性增長(zhǎng)的主要原因呢。

逐一排除問題

之前我們通過分析JOB執(zhí)行流程，發(fā)現(xiàn)了兩個(gè)可疑點(diǎn)，那么現(xiàn)在具體分析SQL的問題

CREATE TABLE #TableTemp (
        字段A int null,
        字段B int null,
        字段C int null
    )
 
    INSERT INTO #TableTemp(
        字段A,
        字段B
    )SELECT
        a.字段A,
        字段B
    FROM ServerA.dbo.TableB a WITH(NOLOCK)
    LEFT JOIN dbo.TableA b WITH(NOLOCK) a.Id = b.Id
 
 
 
    UPDATE a
    SET a.字段C = b.字段D
    FROM #TableTemp a
    LEFT JOIN dbo.TableC b WITH(NOLOCK) ON a.字段A =b.id
 
 
    INSERT INTO dbo.目標(biāo)TableA(
        字段A,
        字段B
    )
    SELECT
        字段A,
        字段B
    FROM #TableTemp WITH(NOLOCK)
 
    INSERT INTO dbo.目標(biāo)TableB(
        字段A,
        字段B,
        字段C
    )
    SELECT DISTINCT        
        a.字段A,
        a.字段B,
        a.字段C
    FROM #TableTemp a WITH(NOLOCK)
    LEFT JOIN dbo.目標(biāo)TableB b ON a.字段A = b.字段A AND a.字段B = b.字段B
    WHERE a.PK IS NULL

先來查看可疑點(diǎn)一，是不是這里出了問題。因?yàn)楸鞹ableC數(shù)據(jù)已經(jīng)是幾億的量，但單獨(dú)將該SQL執(zhí)行發(fā)現(xiàn)，因?yàn)樗饕拇嬖诎l(fā)現(xiàn)執(zhí)行并不是特別慢，所以可以排除掉該問題

那么來看看可疑點(diǎn)二呢

INSERT INTO dbo.目標(biāo)TableB(
        字段A,
        字段B,
        字段C
    )
    SELECT DISTINCT        
        a.字段A,
        a.字段B,
        a.字段C
    FROM #TableTemp a WITH(NOLOCK)
    LEFT JOIN dbo.目標(biāo)TableB b ON a.字段A = b.字段A AND a.字段B = b.字段B
    WHERE a.PK IS NULL

可以看到該SQL插入的同時(shí)還查詢了自身是否存在條件下相同的數(shù)據(jù)，查看表目標(biāo)TableB發(fā)現(xiàn)，該表沒有主鍵也沒有索引，再通過DBA那邊提供的SQL分析發(fā)現(xiàn)，這句SQL對(duì)于dbo.目標(biāo)TableB進(jìn)行了全表掃描，再加上插入的1W條數(shù)據(jù)，相當(dāng)于對(duì)于dbo.目標(biāo)TableB全表掃描了1w次，隨著循環(huán)的執(zhí)行該表數(shù)據(jù)越來越多，執(zhí)行時(shí)間也就越來越長(zhǎng)，看來這里就是導(dǎo)致執(zhí)行時(shí)間線性增長(zhǎng)的主要原因了。

解決問題

根據(jù)上面問題的排除，我們已經(jīng)得知問題的關(guān)鍵所在就是進(jìn)行了1w次的全表掃描，導(dǎo)致了SQL執(zhí)行時(shí)間過長(zhǎng)，那么解決問題的關(guān)鍵所在就是避免這么多次的全表掃描。那么最直接的解決方法，就是建立索引避免全表掃描

1.通過使用臨時(shí)表代替表變量

先來看看，表變量與臨時(shí)表的區(qū)別，可以看到表變量是無法使用索引的，所以我們使用索引避免全表掃描的話必須要代替掉表變量，然后在臨時(shí)表的字段A上我們創(chuàng)建索引

2.修改目標(biāo)TableB的寫入邏輯

現(xiàn)有寫入邏輯會(huì)先判斷是否在目標(biāo)TableB中是否存在，不存在時(shí)則寫入表中，保持業(yè)務(wù)的情況下，我們稍微修改下邏輯，再寫入之前先排除掉與目標(biāo)TableB中的數(shù)據(jù)，將剩余數(shù)據(jù)寫入表中，就能避免循環(huán)1W次的目標(biāo)TableB表查詢了

通過這兩處修改后，再執(zhí)行該JOB發(fā)現(xiàn)問題得到了完美的解決。

總結(jié)

到此這篇關(guān)于因表變量導(dǎo)致SQL執(zhí)行效率變慢的文章就介紹到這了,更多相關(guān)表變量導(dǎo)致SQL執(zhí)行變慢內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

海外穩(wěn)定服務(wù)器

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。