sql存储过程比sql语句执行慢很多

Adm1re 2014-08-18 07:46:34
最近发现某几个存储过程执行时间很长，就拿出来分析， 发现sql语句执行居然很快，而存储过程反而很慢。 查询的表是个数据量很大的表，时不时都有数据插入。
我的存储过程写的是，先创建一个临时表 #abc，然后把数据统计到这个临时表，然后再查询出来。
sql版本 08。
求解答~
...全文
336 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Adm1re 2014-08-19
  • 打赏
  • 举报
 回复
恩，谢谢大伙提示，是存储过程机制的问题，也就是你们说的参数嗅探。 
ALTER PROCEDURE SelUserRecordGameScoreInfoByKindID
@varTime1 DATETIME,
@varTime2 DATETIME,
@varKindID INT,
@vartype INT
AS
DECLARE
@TIME1 DATETIME,
@TIME2 DATETIME,
@KindID INT,
@type INT
SET @Time1=@varTime1
SET @Time2=@varTime2
SET @KindID=@varKindID
SET @type=@vartype
改成这样后，速度跟sql一样快了。 谢谢
xiaoxiangqing 2014-08-19
  • 打赏
  • 举报
 回复
要看查询计划，看哪个地方慢
發糞塗牆 2014-08-19
  • 打赏
  • 举报
 回复
参数嗅探的可能性比较大
xzb001 2014-08-18
  • 打赏
  • 举报
 回复
（1）可能是发生了参数嗅探，第一次赋给存储过程的输入参数，会为该存储过程生成一个基于输入参数的执行计划，因此如果第一次输入的参数不具有代表性（例如大部分查询输入的参数都是A值，但第一次执行存储过程时输入的是B值），就有可能比即席查询慢，尽管即席查询需要重新编译执行计划，但选择了更有效率的计划。 尝试使用和即席查询一样的参数，来执行存储过程，然后对比一下两者的执行计划。 （2）通常存储过程最上面有自带的set设置，如set ansi_nulls on，而即席查询通常没有包含，这些set设置也会影响执行计划。 尝试在即席查询中添加上，与存储过程一样的set设置，然后再对比一下执行计划。
Adm1re 2014-08-18
  • 打赏
  • 举报
 回复 

DECLARE
@Time1 DATETIME,
@Time2 DATETIME,
@KindID INT,
@type INT
SET @Time1='2014-8-10' SET @Time2='2014-8-18' SET @KindID=1000 SET @type=2
--0.2元
CREATE TABLE #m(userid INT,money numeric(18,1) DEFAULT ((0)),cgl INT DEFAULT ((0)),sumscore BIGINT DEFAULT ((0)),sumdiamond  BIGINT DEFAULT ((0)))
INSERT #m (userid)
SELECT DISTINCT userid FROM [QPTreasureDB].[dbo].RecordUserScorePerGame (NOLOCK)  a 
WHERE RecordTime > @Time1 AND RecordTime < @Time2 AND userid>20000 AND KindID=@KindID
AND NOT EXISTS(SELECT userid FROM QPGameUserDB.dbo.PlayTest b WHERE a.userid=b.userid)

IF @type=1
BEGIN
IF @KindID=3001 OR @KindID=1002
 BEGIN	
UPDATE #m  SET money=score
FROM (SELECT UserID,(SUM(GoodsCount)*0.2) AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (14,18) and RecordDate>@Time1
  and RecordDate<@Time2  and GoodsID =96 AND KindID=@KindID
  GROUP BY UserID
) AS A WHERE A.UserID=#m.UserID


--1元
UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount) AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18) and GoodsID =31  and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
  GROUP BY UserID
) AS A WHERE A.UserID=#m.UserID

 --5元
UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*5 AS score
  
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =67 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
   ) AS A WHERE A.UserID=#m.UserID
   
    

 --10元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*10 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =37 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
     

 --15元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*15 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse=1  and GoodsID =69 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
  --20元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*20 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =77 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
  --30元
   UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*30 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =91 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --50元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*50 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =78 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --80元
  UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*80 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =71 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --100元
  UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*100 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse =1  and GoodsID =89 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --200元
  UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*200 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse =1  and GoodsID =90 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
     
UPDATE #m  SET cgl=cc
  FROM (SELECT UserID, SUM(GoodsCount) AS cc
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (14,18) and RecordDate>@Time1
  and RecordDate<@Time2  and GoodsID =13  AND KindID=@KindID
  GROUP BY UserID
  )AS A WHERE A.UserID=#m.UserID
  
  END
  
  UPDATE #m  SET sumscore=ss,sumdiamond=sd
  FROM (SELECT UserID, SUM(Score) AS ss,SUM(Diamond) AS sd
  FROM [QPTreasureDB].[dbo].RecordUserScorePerGame a(NOLOCK) 
  where RecordTime>@Time1 AND KindID=@KindID AND Reason IN(1,6,24,21,22,27)
  and RecordTime<@Time2
  GROUP BY UserID
  )AS A WHERE A.UserID=#m.UserID
     
   SELECT  TOP 50 @kindID AS kindid,KindName,Accounts,#m.userid,money,cgl,sumscore,sumdiamond,((money+cgl)*10000)+sumscore+(sumdiamond*10) AS Score  FROM #m,QPGameUserDB.dbo.AccountsInfo b,QPServerInfoDB.dbo.GameKindItem c
   WHERE #m.userid=b.UserID AND c.KindID=@KindID AND ((money+cgl)*10000)+sumscore+(sumdiamond*10) <>0 
   AND b.userid>20000
    GROUP BY KindName,Accounts,#m.userID,money,cgl,sumscore,sumdiamond
   ORDER BY (money+cgl)*10000+sumscore+(sumdiamond*10) DESC
  END
 ELSE
 BEGIN
  IF @KindID=3001 OR @KindID=1002
 BEGIN	
UPDATE #m  SET money=score
FROM (SELECT UserID,(SUM(GoodsCount)*0.2) AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (14,18) and RecordDate>@Time1
  and RecordDate<@Time2  and GoodsID =96 AND KindID=@KindID
  GROUP BY UserID
) AS A WHERE A.UserID=#m.UserID


--1元
UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount) AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18) and GoodsID =31  and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
  GROUP BY UserID
) AS A WHERE A.UserID=#m.UserID

 --5元
UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*5 AS score
  
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =67 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
   ) AS A WHERE A.UserID=#m.UserID
   
    

 --10元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*10 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =37 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
     

 --15元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*15 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse=1  and GoodsID =69 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
  --20元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*20 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =77 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
  --30元
   UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*30 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =91 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --50元
 UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*50 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =78 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --80元
  UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*80 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (1,14,18)  and GoodsID =71 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2  AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --100元
  UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*100 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse =1  and GoodsID =89 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
    ) AS A WHERE A.UserID=#m.UserID
  --200元
  UPDATE #m  SET money= money+score
FROM (SELECT  UserID,SUM(GoodsCount)*200 AS score
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse =1  and GoodsID =90 AND KindID<>0 and RecordDate>@Time1
  and RecordDate<@Time2 AND KindID=@KindID
    GROUP BY UserID
     ) AS A WHERE A.UserID=#m.UserID
     
UPDATE #m  SET cgl=cc
  FROM (SELECT UserID, SUM(GoodsCount) AS cc
  FROM [QPTreasureDB].[dbo].[RecordUserGoods] a
  where GetOrUse in (14,18) and RecordDate>@Time1
  and RecordDate<@Time2  and GoodsID =13  AND KindID=@KindID
  GROUP BY UserID
  )AS A WHERE A.UserID=#m.UserID
  
  END
  --这里就是统计大量数据表的
  UPDATE #m  SET sumscore=ss,sumdiamond=sd
  FROM (SELECT UserID, SUM(Score) AS ss,SUM(Diamond) AS sd
  FROM [QPTreasureDB].[dbo].RecordUserScorePerGame a(NOLOCK) 
  where RecordTime>@Time1 AND KindID=@KindID AND Reason IN(1,6,24,21,22,27)
  and RecordTime<@Time2
  GROUP BY UserID
  )AS A WHERE A.UserID=#m.UserID
     
   SELECT  TOP 50 @kindID AS kindid,KindName,Accounts,#m.userid,money,cgl,sumscore,sumdiamond,((money+cgl)*10000)+sumscore+(sumdiamond*10) AS Score  FROM #m,QPGameUserDB.dbo.AccountsInfo b,QPServerInfoDB.dbo.GameKindItem c
   WHERE #m.userid=b.UserID AND c.KindID=@KindID AND ((money+cgl)*10000)+sumscore+(sumdiamond*10) <>0 
   AND b.userid>20000
    GROUP BY KindName,Accounts,#m.userID,money,cgl,sumscore,sumdiamond
   ORDER BY (money+cgl)*10000+sumscore+(sumdiamond*10) ASC
   END
   DROp TABLE #m
   GO
--这是存储过程了，虽然#m临时表多次查询，但是不太影响效率（这要统计每个道具对应的价值。。。） --直接执行很快，改成存储过程就很慢
shoppo0505 2014-08-18
  • 打赏
  • 举报
 回复
给临时表#abc添加索引
霜寒月冷 2014-08-18
  • 打赏
  • 举报
 回复
贴出存储过程才可以分析
oracle存储过程比直接执行慢,SQL语句直接执行和在存储过程中速度的有关问题....
SQL语句直接执行和在存储过程中速度的问题......同一个SQL语句,放在存储过程中执行比直接执行差了100倍以上的速度,很不解啊...这个有什么具体的方法来优化么?数据库版本是9i......貌似在10g上就没这么大差别..但是由于2个库的数据量是不一样的,所以没什么参考价值SQL语句:SQL codeSQL_INSERT_RL_INFO_AREA CONSTANT varchar2(1000...
Oracle存储过程(附带SQL语句完整过程及示例)
前言： 转载请附上连接,本帖原创请勿照抄。 存储过程用于完成特定功能的SQL语句集合，编译完后存储在数据库中，不同的程序在执行同一个SQL的时候可以直接调用存储过程无需重新写sql语句。 优点: 模块化程序、需只要调用无需传输SQL,加快SQL执行速度,提高安全性,减少流量,提高可移植性,维护方便。 缺点: 存储过程建立复杂的SQL业务进行过程化执行会导致执行速度变慢,需要应用缓存不太合适...
SQL之存储过程
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。特点封装，复用可以接收参数，也可以返回数据减少网络交互，效率提升用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。赋值。
为什么存储过程比sql语句效率高？
存储过程经过预编译处理 而SQL查询没有 SQL语句需要先被数据库引擎处理成低级的指令 然后才执行 ------------------------------------------------------- 如果是只执行一次的话,存储过程不一定比SQL语句效率高.因为存储过程有个预先编译的过程.就是.启动服务器后或者第一次执行后(可以设置).就可以把存储过程加载到高速缓存中,这样
存储过程执行比单独执行慢
如果语句单独执行很快，但是放到存储过程中很慢，那可能是执行计划搞的鬼试试 WITH RECOMPILE
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章