对存取过程编译的一个疑惑
下面2段代码 第一个在没有 into #temp 没有结果集的时候就会变慢,于是 我在第二段加入了一个if exists(select * from #temp),但是出现了一个很奇怪的问题
如果第一次运行存取过程的时候 没有结果集, 那么以后执行的情况没有结果集的情况非常快,有结果集的非常慢(可以慢到2,3秒), 如果第一次运行有结果集 ,那么情况反过来了,有结果集的非常快,没有结果集的变慢(接近1秒)
这是什么情况?
- SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CarVehicleData_tbl_GetMaxData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[CarVehicleData_tbl_GetMaxData]GO--------------------------------------用途：查询记录信息 --项目名称：--说明：--时间：2012/5/12 15:37:58------------------------------------CREATE PROCEDURE CarVehicleData_tbl_GetMaxData@C_ID int AS select top 1000 * into #temp from [CarVehicleData_tbl] where c_id = @c_id order by [CSN_UpdateTime] descselect c_id,[CSN_Part],CSN_NumberDataOriginal = max(case csn_type when 0 then [CSN_DataOriginal] end),CSN_NumberUpdateTime = max(case csn_type when 0 then [CSN_UpdateTime] end),CSN_StatusDataOriginal = max(case csn_type when 1 then [CSN_DataOriginal] end),CSN_StatusDataUpdateTime = max(case csn_type when 1 then [CSN_UpdateTime] end)from (select *,(select top 1 [CSN_DataOriginal] from #temp a where b.csn_updateTime = a.csn_updateTime and b.csn_type = a.csn_type and b.csn_part = a.csn_part and a.C_ID = @C_ID order by csn_updateTime desc) as [CSN_DataOriginal] from (select max([CSN_ID]) as [CSN_ID],c_id,[CSN_Part],[CSN_Type],max([CSN_UpdateTime]) as [CSN_UpdateTime] from #temp c group by c_id,[CSN_Part],[CSN_Type]) b) dgroup by c_id,[CSN_Part]drop table #tempGO
- SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CarVehicleData_tbl_GetMaxData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[CarVehicleData_tbl_GetMaxData]GO--------------------------------------用途：查询记录信息 --项目名称：--说明：--时间：2012/5/12 15:37:58------------------------------------CREATE PROCEDURE CarVehicleData_tbl_GetMaxData@C_ID int AS select top 1000 * into #temp from [CarVehicleData_tbl] where c_id = @c_id order by [CSN_UpdateTime] descif exists(select * from #temp)beginprint '结果非空！'select c_id,[CSN_Part],CSN_NumberDataOriginal = max(case csn_type when 0 then [CSN_DataOriginal] end),CSN_NumberUpdateTime = max(case csn_type when 0 then [CSN_UpdateTime] end),CSN_StatusDataOriginal = max(case csn_type when 1 then [CSN_DataOriginal] end),CSN_StatusDataUpdateTime = max(case csn_type when 1 then [CSN_UpdateTime] end)from (select *,(select top 1 [CSN_DataOriginal] from #temp a where b.csn_updateTime = a.csn_updateTime and b.csn_type = a.csn_type and b.csn_part = a.csn_part and a.C_ID = @C_ID order by csn_updateTime desc) as [CSN_DataOriginal] from (select max([CSN_ID]) as [CSN_ID],c_id,[CSN_Part],[CSN_Type],max([CSN_UpdateTime]) as [CSN_UpdateTime] from #temp c group by c_id,[CSN_Part],[CSN_Type]) b) dgroup by c_id,[CSN_Part]endelsebeginprint '结果为空！'enddrop table #tempGO
[解决办法]
- SQL code
--try 没发现什么问题呀......感觉是对的。。。。。。;with T as (select max([CSN_ID]) as [CSN_ID],max(c_id) as c_id,[CSN_Part],[CSN_Type],max([CSN_UpdateTime]) as [CSN_UpdateTime], replace(max(convert(char(19),csn_updateTime,120)+ CSN_DataOriginal), max(convert(char(19),csn_updateTime,120)),'') AS CSN_DataOriginalfrom #temp cgroup by [CSN_Part],[CSN_Type])select max(c_id) as c_id,[CSN_Part], CSN_NumberDataOriginal = max(case csn_type when 0 then [CSN_DataOriginal] else null end), CSN_NumberUpdateTime = max(case csn_type when 0 then [CSN_UpdateTime] else null end), CSN_StatusDataOriginal = max(case csn_type when 1 then [CSN_DataOriginal] else null end), CSN_StatusDataUpdateTime = max(case csn_type when 1 then [CSN_UpdateTime] else null end)from Tgroup by [CSN_Part]