大家好,一条sql语句select @t_yearaop=isnull(sum(test1),0.00) from test_test
where aa=@name
and bb=@t_ipdwcode
and testdate=year(getdate());索引为组合索引:aa,bb
这条语句在查询分析器中执行时间是0.1秒,但在存储过程中执行是50秒钟,请问原因是什么?
where aa=@name
and bb=@t_ipdwcode
and testdate=year(getdate());索引为组合索引:aa,bb
这条语句在查询分析器中执行时间是0.1秒,但在存储过程中执行是50秒钟,请问原因是什么?
当你用存储过程执行时,采用的可能是上一次的执行计划,所以效率上会有差别尝试指定WITH RECOMPILE 试试.CREATE PROC spxxx
WITH RECOMPILE
AS
......
我已经exec sp_recompile 过程,或者重建过程,太无效.
select @t_yearaop=isnull(sum(test1),0.00) from test_test
where aa=@name
and bb=@t_ipdwcode
and testdate=year(getdate()); 是要sum,如果没有查完数据,怎么能统计出结果了?
DBCC DROPCLEANBUFFERS
select @t_yearaop=isnull(sum(test1),0.00) from test_test
where aa=@name
and bb=@t_ipdwcode
and testdate=year(getdate());
LZ能不能试试在你的存储过程中使用本地变量, 然后看看运行时间. 存储过程应该象:create.....
as
declare @name_local ---数据类型跟@name一样
declare @t_ipdwcode_local ---数据类型跟@t_ipdwcode一样select @t_yearaop=isnull(sum(test1),0.00) from test_test
where aa=@name_local
and bb=@t_ipdwcode_local
and testdate=year(getdate());
...
请一定告诉偶运行时间是快了还是慢了. 谢谢!!!!
(如果运行时间变了, 偶再来解释.....*_*)
set @name_local = @name
set @t_ipdwcode_local = @t_ipdwcode
....
http://topic.csdn.net/u/20080717/08/e7abcc5d-6984-47a8-87c0-c10dd58272d6.html