Sql Server 中使用临时表优化大表连接查询

最近项目中,有个功能出现点问题,定时任务的excel数据未能发送到客户的email中,经检查原因在于查询超时导致CLR取消了该进程,其中有两张表有近2亿的数据量,而且业务相对比较复杂。

查询一些资料以及和DBA探讨了优化方案,决定先使用临时表试试。本来打算直接切换到clickhouse查询,但时间比较紧急。

总结来说就是:

1.创建临时表:

根据你的大表查询需要的字段建立临时表

比如:

CREATE TABLE  #COMMENT

(

    score varchar(10),
    realOrNOT varchar(10),
    Reason varchar(10),
    rid varchar(10),
    dateline datetime

)

2.如果临时表中使用inner join , group by , order by 或 where，要确保临时表有聚集索引或非聚集索引,创建临时表索引

CREATE NONCLUSTERED INDEX IDX_tme
ON  TEMPDB.DBO. #COMMENT  (rid)
INCLUDE (score,realOrNOT,Reason)

这个好处是遇到大量数据进入临时表的时候，使用起来查询较快，如果是较少的数据的话 就没有必要了,include的字段是查询需要但不在连表或者筛选条件中的字段.

3.将数据插入到临时表中

4.可以将整个查询放到with as语句中,进一步做一个优化。

SQL Server中with as使用介绍

本文借鉴以下博客:

SQL Server 性能优化之——T-SQL 临时表、表变量

sqlserver_给临时表创建索引