SQL2000升级到了SQL2008遇到的问题之一

由于更换服务器（旧服务器windows2003+sql2000 + 至强5320*2 + 4GB内存，新服务器win2008+sql2008 + E7-4087*2 + 32GB内存)

运行同一个存储过程，如：

CREATE PROCEDURE [dbo].[MSP_XT_QryGetCLByGongChengDan]
AS
BEGIN
	SET NOCOUNT ON;
	CREATE TABLE #TEMP_001(
		MainXH INT,
		ProductionNo VARCHAR(20),
		ProductionAmount INT,
		ClientNo VARCHAR(10),
		GDKHS INT   --同一张工单有两个客户	
	)
	
	--前台将要查询的工程单号码批量的写到 System_TEMP_GongChengDanNo 表中

	INSERT INTO #TEMP_001
	SELECT B.MainXH ,ProductionNo=A.NO,ProductionAmount=SUM(ISNULL(B.ProductionAmount,0)),B.ClientNo,GDKHS = 0
	FROM SC_GongChengGan_Main A INNER JOIN
		SC_GongChengGan_Detail B ON A.XH =B.MainXH
	WHERE EXISTS(SELECT 1 FROM System_TEMP_GongChengDanNo C WHERE C.ProductionNo = A.NO AND ISNULL(ProductionNo,'')>'' )		
	GROUP BY B.MainXH,A.NO,B.ClientNo 	
	
	UPDATE #TEMP_001 SET GDKHS =(SELECT COUNT(0) FROM  #TEMP_001 A WHERE A.MainXH = #TEMP_001.MainXH GROUP BY ProductionNo )

	CREATE TABLE #TEMP_002(
		MainXH INT,
		ChildXH INT,
		SBID INT,
		NO VARCHAR(20),
		CLBH VARCHAR(30),
		LLSL FLOAT,
		ZJXH VARCHAR(10),
		ZJMC VARCHAR(50),
		XM VARCHAR(20)
	)

	INSERT INTO #TEMP_002
	SELECT MainXH = A.XH, B.ChildXH,B.SBID, A.NO,B.CLBH,LLSL=ISNULL(B.LLSL ,0),C.ZJXH,C.ZJMC,XM='部件用料' 
	FROM SC_GongChengGan_Main A INNER JOIN
		SC_GongChengGan_Detail0 C ON A.XH = C.MainXH INNER JOIN
		SC_GongChengGan_Detail0_D2 B ON A.XH = B.MainXH   AND B.ChildXH = C.ChildXH
	WHERE (EXISTS(SELECT 1 FROM #TEMP_001 C WHERE C.MainXH=A.XH)) AND (ISNULL(B.CLBH,'')>'') 
	UNION ALL 
	SELECT MainXH = A.XH,B.ChildXH,SBID=0, A.NO,B.CLBH,LLSL=ISNULL(B.LLSL ,0),ZJXH='',ZJMC='',XM='成品其它用料'
	FROM SC_GongChengGan_Main A INNER JOIN
		SC_GongChengGan_Detail1 B ON A.XH = B.MainXH
	WHERE (EXISTS(SELECT 1 FROM #TEMP_001 C WHERE C.MainXH=A.XH)) AND (ISNULL(B.CLBH,'')>'') 
	UNION ALL 
	SELECT MainXH = A.XH,B.ChildXH,B.SBID, A.NO,B.CLBH,LLSL=ISNULL(B.LLSL ,0),C.ZJXH,C.ZJMC,XM='部件其它用料'
	FROM SC_GongChengGan_Main A INNER JOIN
		SC_GongChengGan_Detail0 C ON A.XH = C.MainXH INNER JOIN
		SC_GongChengGan_Detail0_D5 B ON A.XH = B.MainXH AND B.ChildXH = C.ChildXH
	WHERE (EXISTS(SELECT 1 FROM #TEMP_001 C WHERE C.MainXH=A.XH)) AND (ISNULL(B.CLBH,'')>'')




	SELECT A.* ,KHMC=D.ClientZhongWenMing,B.ChildXH,B.SBID,B.CLBH,B.ZJXH,B.ZJMC,
		C.CLMC,C.CLGG,CLLB =C.LBMC,
		CLKZ=ISNULL(C.CLKZ,0),CLCD=ISNULL(C.CLCD,0),CLKD=ISNULL(C.CLKD,0),  
		B.LLSL,B.XM	,SPBH=ISNULL(C.SPBH,''),HGBH = ISNULL(E.HGBH,''),E.SPMC,
		C.DanWei 

	INTO #TEMP_003
	FROM #TEMP_001 A LEFT JOIN
		#TEMP_002 B ON A.MainXH = B.MainXH   LEFT JOIN
		V_ClZLB C ON C.CLBH = B.CLBH LEFT JOIN
		basic_ClientData_Main D ON A.ClientNo = D.ClientNo  LEFT JOIN
		basic_HGSPDMBE ON C.SPBH = E.SPBH
	ORDER BY A.MainXH


	SELECT * ,LLZL=CASE WHEN DanWei='令' THEN  ROUND(CLKZ * CLCD * CLKD /1000/1000 * LLSL * 500 /1000,4)
			      WHEN DanWei='张' THEN  ROUND(CLKZ * CLCD * CLKD /1000/1000 * LLSL * 1000,4)
			 ELSE NULL END
	FROM #TEMP_003  
END

上面的代码太多，其实不用注意。。。

在旧服务器中，运行效率比新服务器高很多。
所以，旧服务器整个过程只要 8秒，而新服务器要 40秒左右

UPDATE #TEMP_001 SET GDKHS =(SELECT COUNT(0) FROM  #TEMP_001 A WHERE A.MainXH = #TEMP_001.MainXH GROUP BY ProductionNo )

换成：

SELECT COUNT(0) AS  GDKHS,MainXH INTO #TEMP_KHS FROM  #TEMP_001 A  GROUP BY ProductionNo,MainXH  
UPDATE #TEMP_001 SET GDKHS = 0 FROM #TEMP_KHS A WHERE A.MainXH = #TEMP_001.MainXH 

效率就有很大的提升了。

旧服务器运行整个过程，要3.6秒，得是新服务器，只要1.5秒。

得到的结论：
1、在SQL语句中使用 嵌套 = 欠扁
2、SQL2008 对嵌套语句的支持比在SQL2000中低得多。