本人学SQLServer时间不长,有个存储过程的问题向各位请教。
问题是这样的,我在一个过程里有很多查询,查询的速度较慢,我为了提高效率将过程里的所有查询都集合一个存储过程里。存储过程大致如下:
create procedure procedurename
(@flag int)
as
begin
declare @sql nvarchar(4000)
set @sql=''
select @sql=
case @flag
when 1 then N'SELECT ......'
when 2 then N'delete ......'
when 3 then N'insert ......'
........
end
exec sp_executesql @sql
end
可是存储过程在运行之后发现与没有采用存储过程直接查询所用的时间几乎相等,没有起到加快查询速度的预期效果。所以向各位高手请教。是不是这样将各个查询集合到一个存储过程有问题?是不是将每条语句独立出来弄个存储过程就能提高效率呢?望各位高手指点指点。
问题是这样的,我在一个过程里有很多查询,查询的速度较慢,我为了提高效率将过程里的所有查询都集合一个存储过程里。存储过程大致如下:
create procedure procedurename
(@flag int)
as
begin
declare @sql nvarchar(4000)
set @sql=''
select @sql=
case @flag
when 1 then N'SELECT ......'
when 2 then N'delete ......'
when 3 then N'insert ......'
........
end
exec sp_executesql @sql
end
可是存储过程在运行之后发现与没有采用存储过程直接查询所用的时间几乎相等,没有起到加快查询速度的预期效果。所以向各位高手请教。是不是这样将各个查询集合到一个存储过程有问题?是不是将每条语句独立出来弄个存储过程就能提高效率呢?望各位高手指点指点。
不影响存储过程的效率,
估计是你相应的语句
SELECT
DELETE
INSERT语句上的问题.
if not exists (select * from sysobjects where xtype='P' and name='MyProc')
exec('
Create Procedure MyProc
(@Flag int=0,
@Dates varchar(50)='''',
@q1 varchar(50)='''',
@f1 float=0,
@f2 float=0,
@f3 float=0,
@f4 float=0)
as
begin
declare @sql nvarchar(4000)
set @sql=''''
select @sql=
case @flag
when 1 then N''Delete From NLdl where Dates=@PDates''
when 2 then N''Insert into NLDL Select Dates,Id,Vol,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 From Stock_Day where dates=@PDates and SubString(id,1,1) not in (''''T'''',''''R'''',''''0'''',''''F'''')''
when 3 then N''SELECT StockId,out_currency FROM Storage Where Sdate=(Select Max(Sdate) From Storage) Order by StockId''
when 4 then N''Update NLdl Set Issue=@Pf1 Where Id=@Pq1 and Dates=@PDates''
when 5 then N''Select Id,Odl_Stock From NLdl where dates=(Select Max(Dates) From NLdl Where Dates<@PDates)''
when 6 then N''Select Id,Tdl_Stock From NLdl where dates=(Select Max(Dates) From NLdl Where Dates<@PDates)''
when 7 then N''Select Id,Fdl_Stock From NLdl where dates=(Select Max(Dates) From NLdl Where Dates<@PDates)''
when 8 then N''Update NLdl Set Issue=@Pf1, Fdl_Stock=@Pf2 Where Id=@Pq1 and Dates=@PDates''
when 9 then N''Update NLdl Set Fdl_Surplus=@Pf1 Where Id=@Pq1 and Dates=@PDates''
when 10 then N''Update NLdl Set Odl_Buy=@Pf1,Odl_Sell=@Pf2,Odl_Surplus=@Pf3,Odl_Stock=@Pf4 Where Id=@Pq1 and Dates=@PDates''
when 11 then N''Update NLdl Set Tdl_Buy=@Pf1,Tdl_Sell=@Pf2,Tdl_Surplus=@Pf3,Tdl_Stock=@Pf4 Where Id=@Pq1 and Dates=@PDates''
when 12 then N''Update NLdl Set Fdl_Surplus=@Pf1,Fdl_Stock=@Pf2 Where Id=@Pq1 and Dates=@PDates''
when 13 then N''Update NLdl Set Tdl_Surplus=@Pf1,Tdl_Stock=@Pf2 Where Id=@Pq1 and Dates=@PDates''
when 14 then N''Update NLdl Set Odl_Surplus=@Pf1,Odl_Stock=@Pf2 Where Id=@Pq1 and Dates=@PDates''
when 15 then
N''Select Id,Issue,ROUND(Fdl_Stock,0) AS Fdl_Stock ,ROUND(Tdl_Stock,0) AS Tdl_Stock,ROUND(Odl_Stock,0) AS Odl_Stock,ROUND(Tdl_Buy,0) AS Tdl_Buy,ROUND(Tdl_Sell,0) AS Tdl_Sell ''
+'',ROUND(Odl_Buy,0) AS Odl_Buy,ROUND(Odl_Sell,0) AS Odl_Sell,ROUND(Fdl_Surplus,0) AS Fdl_Surplus,ROUND(Tdl_Surplus,0) AS Tdl_Surplus ,ROUND(Odl_Surplus,0) AS Odl_Surplus,Vol ''
+'' From NLdl Where Dates=@PDates''
when 16 then N''SELECT DISTINCT ID, DATES FROM DSS_DAY260 WHERE (no1 = 60) order by id''
when 17 then N''Delete From NLdL where Dates<@PDates and id=@Pq1''
when 18 then N''Delete From NLdL where SubString(id,1,1) in (''''F'''',''''T'''',''''R'''',''''0'''')''
when 19 then N''SELECT * FROM NLDL ORDER BY id, Dates DESC''
when 20 then N''SELECT COUNT(DISTINCT id) AS Tcount FROM NLDL''
when 21 then N''SELECT DISTINCT SUBSTRING(Dates, 1, 4) AS Dates FROM (SELECT DISTINCT TOP 60 dates AS Dates FROM STOCK_DAY ORDER BY dates DESC) DERIVEDTBL''
when 22 then N''SELECT Id,Sum(Fdl_Surplus) As Surplus1,Sum(Odl_Surplus) As Surplus2,Sum(Tdl_Surplus) As Surplus3,Sum(Fdl_Surplus+Odl_Surplus+Tdl_Surplus) As Total,Sum(Vol) As Vol FROM NLDL Where Dates Like @PDates Group By Id ORDER BY id''
........
还有很多,省略
........
end
exec sp_executesql @sql,N''@PDates varchar(50),@Pq1 varchar(50),@Pf1 float,@Pf2 float,@Pf3 float,@Pf4 float'',@PDates=@Dates,@Pq1=@q1,@Pf1=@f1,@Pf2=@f2,@Pf3=@f3,@Pf4=@f4
end
')
关键是你的生成的动态语句速度比较慢吧!
如果要提高效率,可以看每个语句是否太慢了,查询语句试着增加些索引
具体可搜索些提高SQL效率的文档,baidu 很多
as
exec('select * from tb')与create proc p
as
select * from tb
二者是有本质区别的。你的过程与第一种写法一致。即,存储过程编译及省去执行计划分析时间的话,只针对主语句, 而动态语句(比如exec里面的语句)它无法分析,只有在运行过程中才会进行执行计划及优化。 也即,在执行前,动态语句未可知。而第二种,在存储过程创建时,它已经知道要执行的是select * from tb了,调用存储过节程时无须再编译。