SQL Server 性能提升

1> not in用not exists 替换

例：

delete from DBTABLE where Column1 not in (select Column1 from DBTABLE)
改为：
select Column1 into #TEMPORARYTABLE from ( 
select Column1 from DBTABLE) TEMPORARYTABLE
delete from DBTABLE where NOT EXISTS (select Column1 from #TEMPORARYTABLE where Column1 = #temporaryTABLE.Column1)

例：

LTRIM(A.列1+A.列2) NOT IN(SELECT LTRIM(列1 + 列2) FROM 表) 
改为：
NOT EXISTS (SELECT 'X' FROM 表 WHERE LTRIM(A.列1+A.列2) = LTRIM(列1 + 列2))

 

2> 大数据表与子查询结果集做连接查询速度慢，改为使用临时表

3> 更新表中有用inner join关联的情况影响更新速度慢，可以把关联的表用临时表查出来在进行更新。

例：

UPDATE G SET 
 更新列1 = G.列1, 
 更新列2 = G.列2, 
 更新列3 = G.列3 
FROM 更新表 AS G 
INNER JOIN
表1 AS S 
ON G.列ID1 = S.列ID1
INNER JOIN
表2 AS B 
ON S.列ID2 = B.列ID2
WHERE G.更新列4 <> 'AAA' 
AND   G.更新列5 = 0 
AND   G.更新列6 <> '' 
AND   B.查询列1 = 'BBB' 
AND   S.查询列1 = 'CCC'
AND   S.查询列2 = 'DDD'

改为：

SELECT 列ID1 INTO #临时表1 FROM( 
SELECT S.列ID1 FROM
表1 AS S 
INNER JOIN 
表2 AS B 
ON S.列ID2 = B.列ID2
WHERE S.查询列1 = 'CCC'
AND   S.查询列2 = 'DDD'
AND   B.查询列1 = 'BBB' 
)临时表1 

SELECT 列主键KEY INTO #临时表2 FROM( 
SELECT G.列主键KEY
FROM 更新表 AS G 
INNER JOIN 
#临时表1 AS S 
ON G.列ID1 = S.列ID1
)临时表2 

UPDATE G SET 
 更新列1 = G.列1, 
 更新列2 = G.列2, 
 更新列3 = G.列3 
FROM GENDAT AS G 
INNER JOIN
#临时表2 AS S 
ON G.列主键KEY = S.列主键KEY 
WHERE G.更新列4 <> 'AAA' 
AND   G.更新列5 = 0 
AND   G.更新列6 <> ''