SQL 指令中的 Select ....into #Temp 中 會產生暫存資料表#Temp； 如果在一道sql 指令中，使用很多暫存資料表， 請問在效能上是否有影響？ 如果有的話，一般各位的處理方式是如何？ 謝謝~!!

Hi, 效能影響狀況得看你的 temp table 裡的資料量, 而且 temp table 一樣可以建立 index, 若是在 temp table 的使用, 有大量資料, 建立 index 一樣是有助益的. 原則上和正常的 table 使用狀況是相同的哦.

TKS!! 但建立temp table本身會不會影響效能?? 例如1： Select * into #T1 from AA Select bb.* from BB b left join #T1 a on a.SYSID=b.SYSID 例如2： Select bb.* from BB b left join (Select * from AA) a on a.SYSID=b.SYSID 類似上述的例子，實際去產生temp table 不會影響效能嗎？ 對硬碟有做存取嗎？ tks

ok, 這個問題是大哉問啊. 其實 sql server 的 temp table 是存放在 tempdb 這個系統資料庫之中, 至於建立資料表對 disk io 的影響, 和一般資料表一樣, 看當時的資料量多寡, 通常是先放在記憶體之中, 到了 check point 時, 才會 flush 進實體硬碟, 何時發生及效能問題, 得看你實際的操作狀況, 不過不需要太擔心這個問題, 若是實際在查詢資料庫時, 必須建立的 temp table, 就去做, 效能問題, 就看實際的狀況再來 tuning. 但可以節省或簡化, 甚至不需要用 temp table 就能做到的操作, 當然就更好. 不知這樣說明是否能解決你的疑問?

非常感謝timhuang大大的說明， 我有些了解了： 以下是我在系統下的sql command... 請問這樣是否會影響效能，或者是該如何改進？ 謝謝！！ DECLARE @VCUS_ID VARCHAR(08), @VSSN_ID VARCHAR(20), @VTYP_ID VARCHAR(01), @VPRG_ID VARCHAR(20) SELECT @VCUS_ID=:CUS_ID SELECT @VSSN_ID=:SSN_ID SELECT @VTYP_ID=:TYP_ID SELECT @VPRG_ID=:PRG_ID select b.ORD_NO, b.WRK_NO, b.MAT_01, b.COL_NO, b.SIZ_DR, sum(isnull(b.PUR_QT,0)) as PUR_QT, b.UNT_PU into #T12 from BBBBB b left join AAAAA a on a.PUR_NO=b.PUR_NO where a.CUS_ID=@VCUS_ID and a.SSN_ID=@VSSN_ID and a.TYP_ID=@VTYP_ID and a.PRG_ID=@VPRG_ID and b.PUR_QT > 0 Group by b.ORD_NO, b.WRK_NO, b.MAT_01, b.COL_NO, b.SIZ_DR, b.UNT_PU --???? select a.ORD_NO, a.WRK_NO, xa.MAT_01, mt.MAT_NM, mt.WID_TH, mt.YRD_GM, xa.COL_NO, cr.COL_NM, xa.SIZ_DR, sum(isnull(xa.SIZ_QT*pb.ORD_QT,0)*isNull(pa.UNT_FM,1) ) as REQ_QT, mt.UNT_RQ, mt.UNT_PU, mt.TRN_RT into #TA from PPPBB pb left join PPPAA pa on pa.CUS_ID=pb.CUS_ID and pa.SSN_ID=pb.SSN_ID and pa.REF_NO=pb.REF_NO and pa.STY_NO=pb.STY_NO left join DDDDA a on a.CUS_ID=pb.CUS_ID and a.SSN_ID=pb.SSN_ID and a.ORD_NO=pa.ORD_NO left join EXPANDSTYLE xa on xa.CUS_ID=pb.CUS_ID and xa.SSN_ID=pb.SSN_ID and xa.STY_NO=pb.STY_NO and xa.COL_WY=pb.COL_NO and xa.SIZ_NM=pb.SIZ_NM left join CCC01A cr on cr.CUS_ID=pb.CUS_ID and cr.SSN_ID=pb.SSN_ID and cr.COL_NO=xa.COL_NO left join CCC02A mt on mt.CUS_ID=pb.CUS_ID and mt.SSN_ID=pb.SSN_ID and mt.MAT_01=xa.MAT_01 and mt.TYP_ID=xa.TYP_ID --Left join CCC05A b5 on b5.UNT_01=mt.UNT_RQ -- and b5.UNT_02=mt.UNT_PU where a.PRG_ID=@VPRG_ID and isnull(pb.ORD_QT,0) > 0 and a.CUS_ID=@VCUS_ID and a.SSN_ID=@VSSN_ID and xa.TYP_ID=@VTYP_ID and (isnull(xa.REF_NO,'')='' or xa.REF_NO=pb.REF_NO) Group by a.ORD_NO, a.WRK_NO, xa.MAT_01, mt.MAT_NM, mt.WID_TH, mt.YRD_GM, xa.COL_NO, cr.COL_NM, xa.SIZ_DR, mt.UNT_RQ, mt.UNT_PU, mt.TRN_RT -- ???? select c1.COP_NO, c1.COP_DT, c1.MAK_ID, c2.MAT_01, c2.COL_NO, c2.SIZ_DR, c1.CUR_DR as CUR_D1, c2.UNT_PR, c2.MIN_QT, c2.UNT_P1, c2.LTM_QT, a1.MAK_NM, a1.CUR_DR, a1.PAY_NM, a1.PAY_WY, a1.PUR_NM, a1.TAX_YN, a1.TAX_RT, convert(varchar(900),a1.PUR_MM) as PUR_MM into #TX_T1 from CCC13B c2 left join CCC13A c1 on c1.SYS_ID=c2.CCC13A_SYS_ID left join CCC09A a1 on c1.MAK_ID=a1.MAK_ID and a1.TYP_ID='S' where c1.CUS_ID=@VCUS_ID and c1.SSN_ID=@VSSN_ID and c2.TYP_ID=@VTYP_ID SELECT DISTINCT COP_NO,MAK_ID, COP_DT INTO #TX_T2 FROM #TX_T1 SELECT MAX(COP_NO) AS COP_NO,MAK_ID, COP_DT INTO #TX_T3 FROM #TX_T2 GROUP BY MAK_ID, COP_DT SELECT A.* INTO #TX FROM #TX_T1 A LEFT JOIN #TX_T3 B ON B.COP_NO=A.COP_NO WHERE B.COP_NO IS NOT NULL DROP TABLE #TX_T1 DROP TABLE #TX_T2 DROP TABLE #TX_T3 SELECT * INTO #TX_COL_YES FROM #TX WHERE isnull(COL_NO,'') <> '' SELECT * INTO #TX_COL_NO FROM #TX WHERE isnull(COL_NO,'') = '' SELECT a.* into #TX_COL_NOT_NULL from #TX_COL_YES a where a.COP_DT = (select MAX(COP_DT) from #TX_COL_YES b where b.MAT_01=a.MAT_01 and b.COL_NO=a.COL_NO) SELECT a.* into #TX_COL_NULL from #TX_COL_NO a where a.COP_DT = (select MAX(COP_DT) from #TX_COL_NO b where b.MAT_01=a.MAT_01 ) -- ?? Select tA.ORD_NO, tA.WRK_NO, a.WRK_DT, a.STK_ID, tA.MAT_01, tA.MAT_NM, tA.WID_TH, tA.YRD_GM, tA.COL_NO, tA.COL_NM, tA.SIZ_DR, tA.REQ_QT, tA.TRN_RT, tA.UNT_RQ, tA.UNT_PU, convert(Numeric(9,2),0) as SUG_QT, convert(Numeric(9,2),0) as STK_QT, t12.PUR_QT, -- WITH COL_NO TX_COL_NOT_NULL.COP_NO AS A_COP_NO, TX_COL_NOT_NULL.MAK_ID AS A_MAK_ID, TX_COL_NOT_NULL.CUR_DR AS A_CUR_DR, TX_COL_NOT_NULL.UNT_PR AS A_UNT_PR, TX_COL_NOT_NULL.UNT_PR as A_PUR_PR, TX_COL_NOT_NULL.MIN_QT AS A_MIN_QT, TX_COL_NOT_NULL.UNT_P1 AS A_UNT_P1, TX_COL_NOT_NULL.LTM_QT AS A_LTM_QT, TX_COL_NOT_NULL.MAK_NM AS A_MAK_NM, TX_COL_NOT_NULL.CUR_D1 AS A_CUR_D1, TX_COL_NOT_NULL.PAY_NM AS A_PAY_NM, TX_COL_NOT_NULL.PAY_WY AS A_PAY_WY, TX_COL_NOT_NULL.PUR_NM AS A_PUR_NM, TX_COL_NOT_NULL.TAX_YN AS A_TAX_YN, TX_COL_NOT_NULL.TAX_RT AS A_TAX_RT, TX_COL_NOT_NULL.PUR_MM AS A_PUR_MM, -- WITHOUT COL_NO TX_COL_NULL.COP_NO AS B_COP_NO, TX_COL_NULL.MAK_ID AS B_MAK_ID, TX_COL_NULL.CUR_DR AS B_CUR_DR, TX_COL_NULL.UNT_PR AS B_UNT_PR, TX_COL_NULL.UNT_PR as B_PUR_PR, TX_COL_NULL.MIN_QT AS B_MIN_QT, TX_COL_NULL.UNT_P1 AS B_UNT_P1, TX_COL_NULL.LTM_QT AS B_LTM_QT, TX_COL_NULL.MAK_NM AS B_MAK_NM, TX_COL_NULL.CUR_D1 AS B_CUR_D1, TX_COL_NULL.PAY_NM AS B_PAY_NM, TX_COL_NULL.PAY_WY AS B_PAY_WY, TX_COL_NULL.PUR_NM AS B_PUR_NM, TX_COL_NULL.TAX_YN AS B_TAX_YN, TX_COL_NULL.TAX_RT AS B_TAX_RT, TX_COL_NULL.PUR_MM AS B_PUR_MM into #TCX from #TA tA left join #TX_COL_NOT_NULL TX_COL_NOT_NULL on isNull(TX_COL_NOT_NULL.MAT_01,'')=isNull(tA.MAT_01,'') and isNull(TX_COL_NOT_NULL.COL_NO,'')=isNull(tA.COL_NO,'') and isNull(TX_COL_NOT_NULL.SIZ_DR,'')=isNull(tA.SIZ_DR,'') left join #TX_COL_NULL TX_COL_NULL on isNull(TX_COL_NULL.MAT_01,'')=isNull(tA.MAT_01,'') and isNull(TX_COL_NULL.SIZ_DR,'')=isNull(tA.SIZ_DR,'') left join #T12 t12 on isnull(t12.MAT_01,'')=isnull(tA.MAT_01,'') and isnull(t12.COL_NO,'')=isnull(tA.COL_NO,'') and isnull(t12.SIZ_DR,'')=isnull(tA.SIZ_DR,'') and isnull(t12.ORD_NO,'')=isNull(tA.ORD_NO,'') left join DDDD6D a on tA.WRK_NO=a.WRK_NO Select ORD_NO, WRK_NO, WRK_DT, STK_ID, MAT_01, MAT_NM, WID_TH, YRD_GM, COL_NO, COL_NM, SIZ_DR, REQ_QT, TRN_RT, UNT_RQ, UNT_PU, SUG_QT, STK_QT, PUR_QT, -- WITH COL_NO Case when A_COP_NO > ' ' then A_COP_NO else B_COP_NO End as COP_NO, Case when A_COP_NO > ' ' then A_MAK_ID else B_MAK_ID End as MAK_ID, Case when A_COP_NO > ' ' then A_CUR_DR else B_CUR_DR End as CUR_DR, Case when A_COP_NO > ' ' then A_UNT_PR else B_UNT_PR End as UNT_PR, Case when A_COP_NO > ' ' then A_PUR_PR else B_PUR_PR End as PUR_PR, Case when A_COP_NO > ' ' then A_MIN_QT else B_MIN_QT End as MIN_QT, Case when A_COP_NO > ' ' then A_UNT_P1 else B_UNT_P1 End as UNT_P1, Case when A_COP_NO > ' ' then A_LTM_QT else B_LTM_QT End as LTM_QT, Case when A_COP_NO > ' ' then A_MAK_NM else B_MAK_NM End as MAK_NM, Case when A_COP_NO > ' ' then A_CUR_D1 else B_CUR_D1 End as CUR_D1, Case when A_COP_NO > ' ' then A_PAY_NM else B_PAY_NM End as PAY_NM, Case when A_COP_NO > ' ' then A_PAY_WY else B_PAY_WY End as PAY_WY, Case when A_COP_NO > ' ' then A_PUR_NM else B_PUR_NM End as PUR_NM, Case when A_COP_NO > ' ' then A_TAX_YN else B_TAX_YN End as TAX_YN, Case when A_COP_NO > ' ' then A_TAX_RT else B_TAX_RT End as TAX_RT, Case when A_COP_NO > ' ' then A_PUR_MM else B_PUR_MM End as PUR_MM Into #TC From #TCX order by ORD_NO, MAT_01, COL_NO, SIZ_DR Update #TC set SUG_QT= case when UNT_PU=UNT_RQ then REQ_QT when Convert(FLOAT,isnull(TRN_RT,0)) > 0 then REQ_QT/Convert(FLOAT,TRN_RT) else 0 end select CTT_DR= case isnull(a.COP_NO,'') when '' then 'N' else 'Y' end, a.ORD_NO, a.WRK_NO, a.WRK_DT, a.WRK_DT as DUE_DT, a.STK_ID, a.MAT_01, a.MAT_NM, a.WID_TH, a.YRD_GM, a.COL_NO, a.COL_NM, aa.COL_CF, a.SIZ_DR, convert(numeric(9,2),a.REQ_QT) as REQ_QT, a.TRN_RT, a.UNT_RQ, a.UNT_PU, a.SUG_QT, convert(numeric(9,2),a.PUR_QT) as PUR_QT, a.COP_NO, a.MAK_ID, a.CUR_DR, a.UNT_PR, a.UNT_PR as PUR_PR, a.MIN_QT, a.UNT_P1, a.LTM_QT, a.CUR_D1, a.MAK_NM, a.TAX_YN, a.TAX_RT, a.PAY_NM as PAY_NM, a.PAY_WY, a.PUR_NM as PUR_NM, a.PUR_MM as REM_MM, bb.STK_QT, bb.STK_DR, (Select Top 1 CPN_ID from AAA01A) as CPN_ID from #TC a left join CCC14A aa on aa.CUS_ID=@VCUS_ID and aa.SSN_ID=@VSSN_ID and aa.TYP_ID=@VTYP_ID and aa.MAT_01=a.MAT_01 and aa.COL_NO=a.COL_NO left join BBBBB bb on bb.MAT_01=Null where isnull(a.SUG_QT,0) - isnull(a.PUR_QT,0) > 0 order by a.CTT_DR, a.MAT_01, a.COL_NO, a.SIZ_DR, a.ORD_NO DROP TABLE #TA DROP TABLE #TX_COL_NO DROP TABLE #TX_COL_YES DROP TABLE #TX_COL_NULL DROP TABLE #TX_COL_NOT_NULL DROP TABLE #TCX DROP TABLE #TX DROP TABLE #TC DROP TABLE #T12

老實話那麼長一串的SQL語法, 沒有看完, 我的直覺反應, 似乎用SQL來寫一般的應用程式, 效能已經與使用暫存表沒有那麼大的關係了. 我的建議是 1.先將內容合理化,看看是否有步驟可省略. 2.看看是否可運用演算法計算其次數. 3.SQL語法最佳化.