cte和临时表性能分析.docxVIP

CTE和临时表性能比较CTE最优秀的地方是在实现递归操作，和替代绝大部分游标的功能，但是对于大数据量，由于cte不能建索引，所以明显比临时表差。例如：当需要查询大数据时，临时表的性能远比CTE要高，以下两个查询结果相同，但是CTE性能明显不如临时表CTE查询，耗时60SifOBJECT_ID(tempdb.dbo.#POUnsign)isnotnulldroptable#POUnsign;;withcte_POUnsignWFas(selectdistincta.ApplicationidcollateSQL_Latin1_General_CP1_CI_ASasApplicationid--必须使用AS 重命名字段，否则cte查询的结果中没有列名,a.ApplicantcollateSQL_Latin1_General_CP1_CI_ASasApplicant,f.ApprovercollateSQL_Latin1_General_CP1_CI_ASasApprover,u.UserManagercollateSQL_Latin1_General_CP1_CI_ASasUserManager,f.FlowDescriptioncollateSQL_Latin1_General_CP1_CI_ASasFlowDescriptionfrom[OAWF2].[QSMCWF].[dbo].[WFApprove]awith(nolock)innerjoin[OAWF2].[QSMCWF].[dbo].[WFFlow]fwith(nolock)ona.ApplicationID=f.ApplicationIDanda.FormID=f.FormIDanda.CurrentFlowNo=f.FlowNoinnerjoindbo.tscUseruwith(nolock)ona.Applicant=u.UserNamecollateSQL_Latin1_General_CP1_CI_ASwherea.FormID=WF0032andf.ApproveStatus=4anda.ApplyStatus=0andf.SequenceNo=1 )selectdistinctpu.Applicant,pu.Approver,pu.UserManager,pu.FlowDescriptioninto#POUnsignfromcte_POUnsignWFpuinnerjoindbo.tdsPoSignInfopswith(nolock)onpu.Applicationid=ps.ApplicationIDcollateSQL_Latin1_General_CP1_CI_ASinnerjoindbo.tdsPoHeaderphwith(nolock)onph.Ebeln=ps.EbelncollateSQL_Latin1_General_CP1_CI_ASleftjoindbo.tdsAupoawith(nolock)ona.EBELN=ps.EbelncollateSQL_Latin1_General_CP1_CI_ASanda.EBELP=00010collateSQL_Latin1_General_CP1_CI_ASwhereph.Sexkzin(0,1)anda.DECLITEMisnullselect*from#POUnsigndroptable#POUnsign临时表查询：耗时8sifOBJECT_ID(tempdb.dbo.#POUnsign)isnotnulldroptable#POUnsign;ifOBJECT_ID(tempdb.dbo.#POUnsignWF)isnotnulldroptable#POUnsignWF;selectdistincta.ApplicationidcollateSQL_Latin1_General_CP1_CI_ASasApplicationid--必须使用AS 重命名字段，否则查询的结果中没有列名，不能创建临时表,a.ApplicantcollateSQL_Latin1_General_CP1_CI_ASasApplicant,f.ApprovercollateSQL_Latin1_General_CP1_CI_ASasApprover,u.UserManagercollateSQL_Latin1_General_CP1_CI_ASasUserManager,f.FlowDescriptioncollateSQL_Latin1_General_CP1_CI_ASasFlowDescriptioninto#POUnsignWFfrom[OAWF2].[QSMCWF].[dbo].[WFApprove]awith