记一次，因表变量导致SQL执行效率变慢记一次，因表变量导致SQL执行

场景
最近工作中，发现某同步JOB在执行中经常抛出SQL执行超时的问题，查看日志发现每次SQL执行的时间都是线性增长的，循环执行50次以后执行时间甚至超过了5分钟

JOB执行流程分析
首先，对于JOB流程进行分析，查看是否是JOB设计上的问题

文章图片

通过对流程的分析，发现每次获取的需要同步的数据最多只有一万条，不存在大数据写入导致超时的问题。
【记一次，因表变量导致SQL执行效率变慢】那么在对获取详细信息这个过程进行分析，发现关联的表中最多的数据已经上亿了，可能是这里导致了整体SQL执行变慢的原因。这里能算可疑点一。
再接着往下一个流程看与表B对比重复数据时，随着循环执行表B的数据会越来越多，那么会不会这里是导致循环执行下执行时间称线性增长的主要原因呢。
逐一排除问题
之前我们通过分析JOB执行流程，发现了两个可疑点，那么现在具体分析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.目标TableA( 字段A, 字段B ) SELECT 字段A, 字段B FROM #TableTemp WITH(NOLOCK) INSERT INTO dbo.目标TableB( 字段A, 字段B, 字段C ) SELECT DISTINCT a.字段A, a.字段B, a.字段C FROM #TableTemp a WITH(NOLOCK) LEFT JOIN dbo.目标TableB b ON a.字段A = b.字段A AND a.字段B = b.字段B WHERE a.PK IS NULL

先来查看可疑点一，是不是这里出了问题。因为表TableC数据已经是几亿的量，但单独将该SQL执行发现，因为索引的存在发现执行并不是特别慢，所以可以排除掉该问题
那么来看看可疑点二呢

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

可以看到该SQL插入的同时还查询了自身是否存在条件下相同的数据，查看表目标TableB发现，该表没有主键也没有索引，再通过DBA那边提供的SQL分析发现，这句SQL对于dbo.目标TableB进行了全表扫描，再加上插入的1W条数据，相当于对于dbo.目标TableB全表扫描了1w次，随着循环的执行该表数据越来越多，执行时间也就越来越长，看来这里就是导致执行时间线性增长的主要原因了。

文章图片

解决问题

根据上面问题的排除，我们已经得知问题的关键所在就是进行了1w次的全表扫描，导致了SQL执行时间过长，那么解决问题的关键所在就是避免这么多次的全表扫描。那么最直接的解决方法，就是建立索引避免全表扫描
1.通过使用临时表代替表变量

文章图片

先来看看，表变量与临时表的区别，可以看到表变量是无法使用索引的，所以我们使用索引避免全表扫描的话必须要代替掉表变量，然后在临时表的字段A上我们创建索引
2.修改目标TableB的写入逻辑
现有写入逻辑会先判断是否在目标TableB中是否存在，不存在时则写入表中，保持业务的情况下，我们稍微修改下逻辑，再写入之前先排除掉与目标TableB中的数据，将剩余数据写入表中，就能避免循环1W次的目标TableB表查询了
通过这两处修改后，再执行该JOB发现问题得到了完美的解决。