我们在使用SQL查询时，经常会写一些非常复杂的组合子查询，虽然可以一条语句完成操作，但是由于结构复杂，对今后查看进行修改造成困难不说，而且SQL Server也无法对查询进行优化，以下就以实际工作中碰到的一个例子加以说明。
对用户授权数判断的处理，原语句如下：
SELECT COUNT(*)
FROM (FROM (SELECT v_ur.application
FROM myuserrights v_ur INNER JOIN
myuser u ON v_ur.userguid = u.userguid
WHERE u.isadmin = 0 AND (isdisabeld = 0 OR
isdisabeld IS NULL) AND v_ur.userguid NOT IN
(SELECT DISTINCT (su.userguid)
FROM mystationuser su
WHERE su.stationguid IN (......))
GROUP BY v_ur.application, v_ur.userguid) AS TEMP
GROUP BY TEMP .application) temp2 RIGHT JOIN
myapplication a ON a.application = temp2.application
WHERE a. LEVEL = 1 AND a.application IN
(SELECT application
FROM myfunction
WHERE functioncode IN (......)
GROUP BY application) AND (CASE WHEN temp2.usercount IS NULL THEN 333 + 7 ELSE temp2.usercount + 333 + 7 END) > Isnull(a.licenseusercount, 0)

执行一次大约要70秒左右，性能非常低
查看了一下执行计划，如图，其中最慢的部分是在“表假脱机”占了70%
[upload=jpg]682/2008-7/200872911181884279.jpg[/upload]
通过上图的执行计划查出执行时间最长的一段查询如下：
SELECT v_ur.application
FROM myuserrights v_ur INNER JOIN
myuser u ON v_ur.userguid = u.userguid
WHERE u.isadmin = 0 AND (isdisabeld = 0 OR
isdisabeld IS NULL) AND v_ur.userguid NOT IN
(SELECT DISTINCT (su.userguid)
FROM mystationuser su
WHERE su.stationguid IN (......))
GROUP BY v_ur.application, v_ur.userguid
修改此部分查询，将其结果放在临时表中，然后再使用临时表与其它表进行关联查询，调整如下：
查询1
IF OBJECT_ID(''tempdb..#temp2'') IS NOT NULL
DROP TABLE #temp2

SELECT TEMP.application, COUNT(*) AS usercount
INTO #temp2
FROM (SELECT v_ur.application
FROM myuserrights v_ur INNER JOIN
myuser u ON v_ur.userguid = u.userguid
WHERE u.isadmin = 0 AND (isdisabeld = 0 OR
isdisabeld IS NULL) AND v_ur.userguid NOT IN
(SELECT DISTINCT (su.userguid)
FROM mystationuser su
WHERE su.stationguid IN (......))
GROUP BY v_ur.application, v_ur.userguid) AS TEMP
GROUP BY TEMP .application
查询1执行计划如下：
[upload=jpg]682/2008-7/200872911215982468.jpg[/upload]

查询2
SELECT COUNT(*)
FROM #temp2 temp2 RIGHT JOIN
myapplication a ON a.application = temp2.application
WHERE a. LEVEL = 1 AND a.application IN
(SELECT application
FROM myfunction
WHERE functioncode IN (''01010303'', ''01010303'')
GROUP BY application) AND (CASE WHEN temp2.usercount IS NULL
THEN 333 + 7 ELSE temp2.usercount + 333 + 7 END) > Isnull(a.licenseusercount, 0)

查询2执行计划如下
[upload=jpg]682/2008-7/200872911222272662.jpg[/upload]
经测试，修改后的SQL执行时间在10秒左右，语句上没有进行优化，也没有建立索引，只是将一段子查询结果单独放在临时表中，为什么影响就会这么大呢？
从上图分析出，原SQL语句由于太过复杂，通过SQL Server生成临时数据时不能进行优化，所以在循环处理中多次生成临时数据，占用了大量计算时间。
在我们现在的业务查询中，可能也存在类似这种多层嵌套而且非常复杂的查询，这也是需要进行优化的，首先要尽可能把查询语句写简单，不要多重嵌套查询，如果生成临时数据可能会占用较长时间的话，可以尝试使用临时表存放，对于SQL Server 2005可以尝试使用WITH语句。