存储过程在查询分析器中的执行速度很快,在程序中的执行速度很慢的诡异问题

关在笼子里的老男人 2013-08-26 05:59:54
首先请大神！
如题，之前有一个存储过程，后来可能是改了一下，然后程序中运行起来特别的慢，但是我调试的过程中发现他在查询分析器中运行的速度惊人，程序中大概要执行50s，而在查询分析器中执行<=1s，我就震惊了。然后我就各种在网上找相关的问题，网上也有好多人遇到过类似的问题，但是我按照他们给的思路重做了之后无济于事，这下我彻底狂乱了，这是到这公司的第一天上班，搞杂了，明天继续。有没有高手能帮忙解决下这个问题了。
小弟拜上！
...全文
546 21 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiaohua1001 2015-02-04
  • 打赏
  • 举报
 回复
我也遇过同样的问题，后来我参数直接拼接在sql语句中替换原来的问号，问题就解决了，超快
mfkdwfgsou 2013-09-10
  • 打赏
  • 举报
 回复 
ALTER  PROCEDURE [dbo].[SqlCtRecord]
    @tableName    varchar(255)         -- 表名
AS

if(object_id('temptable') is not null)----创建临时表
 begin 
  drop table temptable
 end

begin
exec('select * into temptable from (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from '+@tableName+' where id in (select max(ID) from '+@tableName+' as tb group by cid))t')

select tr1.*,'jxsname' as ac from (select JXSname from temptable group by JXSname having (count(JXSname)>1 and JXSname<>'' and JXSname is not null))t1 
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by JXSname))tr1 on t1.JXSname = tr1.JXSname 
union all select tr2.*,'name' as ac from (select ObjName from temptable group by ObjName having (count(ObjName)>1 and ObjName<>'' and ObjName is not null))t2 
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ObjName))tr2 on t2.ObjName = tr2.ObjName 
union all select tr3.*,'num' as ac from (select ObjNum from temptable group by ObjNum having (count(ObjNum)>1 and ObjNum<>'' and ObjNum is not null))t3 
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ObjNum))tr3 on t3.ObjNum = tr3.ObjNum 
union all select tr4.*,'ttnum' as ac from (select ttNum from temptable group by ttNum having (count(ttNum)>1 and ttNum<>'' and ttNum is not null))t4 
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ttNum))tr4 on t4.ttNum = tr4.ttNum 
union all select tr5.*,'ObjJGListNum' as ac from (select ObjJGListNum from temptable group by ObjJGListNum having (count(ObjJGListNum)>1 and ObjJGListNum<>'' and ObjJGListNum is not null))t5 
left join (select [id],[cid],[JXSname],[ObjName],[ObjNum],[ttNum],
[ObjJGListNum] from [TTobjinfo] where id in (select max(ID) from temptable group by ObjJGListNum))tr5 on t5.ObjJGListNum = tr5.ObjJGListNum
end

drop table  temptable        --销毁临时表
在查询分析器中只要2S，在程序中就超时。。。。
mfkdwfgsou 2013-09-10
  • 打赏
  • 举报
 回复
我现在也遇到这样的问题，求帮助！
关在笼子里的老男人 2013-08-27
  • 打赏
  • 举报
 回复
引用 13 楼 clark_kidd 的回复:
[quote=引用 2 楼 Mairk_Love 的回复:] [quote=引用 1 楼 guwei4037 的回复:] 在程序中单步调试，看究竟是哪一步执行慢，就知道问题在哪了。没有代码，只能给你这点参考。
我调试过，就是在SqlDataAdapter的Fill的方法的时候，这个过程持续了有95%以上的时间，但是我的这个存储过程在SQL Server查询分析器里面的执行速度是很快的。我刚开始以为是数据填充的是很有问题所以我采用了SqlCommand的ExccuteNonQuery()方法执行，但还是同样很慢，没有什么改变。 明天我过去贴上代码，然后各位大神看看。[/quote] 依我的猜测，SqlDataAdapter的Fill慢，应该是你的存储过程返回的数据集非常的大，你确认一下，50s后，你收到的结果集有多少条记录[/quote] 和查询分析器中查询出来的条数是一样的， 一两百条数据。
qldsrx 2013-08-27
  • 打赏
  • 举报
 回复
数据是否正确，你可以执行下就知道了。但是他这里用的临时表还真多，创建了都不删除的，很难保证下次是否还会创建相同的临时表。
clark_kidd 2013-08-27
  • 打赏
  • 举报
 回复
引用 2 楼 Mairk_Love 的回复:
[quote=引用 1 楼 guwei4037 的回复:] 在程序中单步调试，看究竟是哪一步执行慢，就知道问题在哪了。没有代码，只能给你这点参考。
我调试过，就是在SqlDataAdapter的Fill的方法的时候，这个过程持续了有95%以上的时间，但是我的这个存储过程在SQL Server查询分析器里面的执行速度是很快的。我刚开始以为是数据填充的是很有问题所以我采用了SqlCommand的ExccuteNonQuery()方法执行，但还是同样很慢，没有什么改变。 明天我过去贴上代码，然后各位大神看看。[/quote] 依我的猜测，SqlDataAdapter的Fill慢，应该是你的存储过程返回的数据集非常的大，你确认一下，50s后，你收到的结果集有多少条记录
关在笼子里的老男人 2013-08-27
  • 打赏
  • 举报
 回复
我自己来顶一下，顺便问问有没有人遇到过类似的问题。我今天以为是我在创建游标的时候没有进行判断，所以我又加了IF Cursor_status('global','存储过程的名称')= 1 这样来判断，但还是不行。小弟在线等啊。
关在笼子里的老男人 2013-08-27
  • 打赏
  • 举报
 回复
引用 8 楼 sp1234 的回复:
你的代码太复杂了不好确定，何况我们根本不写存储过程。 你可以把返回变为 select count(*) from .......，这样再来客观地比较程序和查询分析器得到结果的速度。如果还是50s对1s的差距，那么真的很奇怪了。
这种方法我试过了，但他的执行速度是不变的，这个具他们以前管理的人说是好像改了什么表结构之后就变这样子了，先开始我是以为表主键丢失导致的，但也排除掉了。
leeya66 2013-08-27
  • 打赏
  • 举报
 回复
50s肯定有问题， 我是业余的程序员，说点自己的看法， 你在查询分析器里面执行快是因为存储过程的内存还没有释放， 就好比其他的查询，第一次查询会有点慢，第二次就会很快，这个大家应该都有感觉的吧？ 所以问题出在你的存储过程上面，做的太复杂 你要想办法简化问题， 可以先创建一些视图 或者可以去C#里面处理逻辑判断之类的动作，
以专业开发人员为伍 2013-08-27
  • 打赏
  • 举报
 回复
临时不用删除。临时表在本次会话中有效，出了会话就自动消失了，删除它反而是画蛇添足。（除非是全局临时表，以两个 ## 号开头的那种临时表）
以专业开发人员为伍 2013-08-27
  • 打赏
  • 举报
 回复
你的代码太复杂了不好确定，何况我们根本不写存储过程。 你可以把返回变为 select count(*) from .......，这样再来客观地比较程序和查询分析器得到结果的速度。如果还是50s对1s的差距，那么真的很奇怪了。
关在笼子里的老男人 2013-08-27
  • 打赏
  • 举报
 回复
首先感谢朋友们的关注，我问题是解决了，首先说的是犯了一个很低级的错误。我的存储过程中引用了别的数据库的存储过程，我也不知道什么时候别人改了，但是没有预编译。我刚开始时候是这么想的，但是我只是找到了一个存储过程预编译了一下，没有出现结果。到了后面我也不清楚我预编译了几个存储过程。今天我重新把几个存储过程进行了预编译之后就正常了。我这个汗啊。
关在笼子里的老男人 2013-08-27
  • 打赏
  • 举报
 回复
引用 15 楼 qldsrx 的回复:
如果是普通的查询，速度是否正常，比如你直接对某个表获取几百条数据，如果正常，问题就是这个存储过程里的了。 有可能是临时表的使用问题，正常情况下，临时表是在当前连接中使用的，但是他在exec里面创建了临时表，因此不好判断，我从未这么写过，要知道，exec sp_executesql是会新开数据库连接的，这就涉及到跨连接获取数据。另外临时表对同一个连接来说是会永远存在的，但是对exec sp_executesql的情况我又不清楚了，而C#会缓存链接，即使你关闭了连接，连接还是没断开，因此对C#来说，临时表是不会自动删除的。各种复杂情况很难断言，实际写这种复杂SQL的人我很“佩服”，我宁可使用程序来控制，也不会写到存储过程里，可读性太糟糕了。 给个额外的建议，create table #table语句其实用declare @table table来替代是更好的，变量的作用域只有一次执行过程，而不是一次连接，不存在销毁的问题。
非常感谢你的回答，其实我好多的地方都会用到临时表，这个sp_executesql就是用来动态的执行SQL语句，我现在的问题应该就是出在这个存储过程当中，因为其他的查询功能都是正常的。
qldsrx 2013-08-27
  • 打赏
  • 举报
 回复
如果是普通的查询，速度是否正常，比如你直接对某个表获取几百条数据，如果正常，问题就是这个存储过程里的了。 有可能是临时表的使用问题，正常情况下，临时表是在当前连接中使用的，但是他在exec里面创建了临时表，因此不好判断，我从未这么写过，要知道，exec sp_executesql是会新开数据库连接的，这就涉及到跨连接获取数据。另外临时表对同一个连接来说是会永远存在的，但是对exec sp_executesql的情况我又不清楚了，而C#会缓存链接，即使你关闭了连接，连接还是没断开，因此对C#来说，临时表是不会自动删除的。各种复杂情况很难断言，实际写这种复杂SQL的人我很“佩服”，我宁可使用程序来控制，也不会写到存储过程里，可读性太糟糕了。 给个额外的建议，create table #table语句其实用declare @table table来替代是更好的，变量的作用域只有一次执行过程，而不是一次连接，不存在销毁的问题。
关在笼子里的老男人 2013-08-26
  • 打赏
  • 举报
 回复
贴上我的SQL语句。这问题纠结了一天了，我现在有点怀疑我这SQL语句是否是正确的。
关在笼子里的老男人 2013-08-26
  • 打赏
  • 举报
 回复 

ALTER PROCEDURE [dbo].[ERP_Report_Graph_P] 
@ERPMenuID int,
@Graph int,
@Parameter nvarchar(100),
@PrimaryKeyValue nvarchar(max)
AS
BEGIN
	
declare @Str nvarchar(max),
		@Host_Name nvarchar(50),
		@ProcedureName nvarchar(100),
		@TableName nvarchar(50),
		@TimePrimaryKey nvarchar(50),
		@Loop int,
		@ProcedureID int,
		@ERP_Report_Type int

select	@Host_Name=b.[Host_Name],
		@ProcedureID=b.ID,
		@ProcedureName=b.U_Name,
		@TableName=b.ERPTableName,
		@TimePrimaryKey=b.ERPTableTimePrimaryKey,
		@ERP_Report_Type=a.ERP_Report_Type
from ERP_Report_Menu_T a inner join 
	 ERP_Procedure_T b 
		on b.ID=a.ERP_Procedure_ID and b.IsValid=1
where a.IsValid=1 and a.ID=@ERPMenuID


if @ERP_Report_Type<>3 
begin
	select '报表类型不是图表展示，系统报错！请与系统管理员联系解决...' [提示：]
	return
end

set @Str='create table #table('
select @Str=@Str+(case U_Order 
						when 1 then '' 
						else ',' 
				  end)+
				  Column_Name+' '+Column_DataType
from ERP_Procedure_Column_T
where ERP_Procedure_ID=@ProcedureID
order by U_Order

set @Str=@Str+')insert #table '

if dbo.U_IsNull(@TableName)=1
	select @Str=@Str+'exec '+
					 b.LocalURL+
					 '[Sys_CommonTable_DataSource_P] '+
					 @TableName+','+
					 @TimePrimaryKey+','+
					 @Parameter+','+
					 (case dbo.U_IsNull(@PrimaryKeyValue) 
						when 0 then '''''' 
						else ''''''+@PrimaryKeyValue+'''''' 
					  end)
from ERP_Report_Menu_T a inner join 
	 ERP_Report_Dept_T b 
		on b.ID=a.ERP_Dept_ID
where a.ID=@ERPMenuID
else if dbo.U_IsNull(@Host_Name)=0
	select @Str=@Str+'exec '+
					 @ProcedureName +' 1,' +
					 @Parameter+','+
					 convert(nvarchar(50),a.ERP_Dept_ID)
from ERP_Report_Menu_T a 
where a.ID=@ERPMenuID
else
	select @Str=@Str+'exec '+
					 d.LocalURL+
					 @Host_Name+' '+
					 @Parameter+
					 (case 
						when exists(select c.ID 
									from ERP_Procedure_Parameter_T c 
									where c.IsValid=1 and 
										  c.ERP_Procedure_ID=@ProcedureID and 
										  c.Parameter='@Where')
						then ',''''' 
						else '' 
					  end)
	from ERP_Report_Menu_T a inner join 
		 ERP_Report_Dept_T d 
			on d.ID=a.ERP_Dept_ID
	where a.ID=@ERPMenuID


set @Str=@Str+'select '

select @Str=@Str+(case U_Order 
					when 1 then ''
					else ',' 
				  end)+'['+
				  Column_Name+
				  '] as ['+
				  MatchField+']'
from ERP_Procedure_Column_T
where U_Visible=1
order by U_Order

select @Str=@Str+' into #table1 from #table'+
				 (case (select Direction 
						from ERP_Report_Graph_T 
						where ID=@Graph) 
					when 1 then 'declare @Loop nvarchar(100),
										 @Order int
								 select distinct([sort]) as [TabPage],
										U_Order 
								 into #table2 
								 from #table1 
								 order by [U_Order]
								 
								 select Tabpage,
										row_number()over(order by U_Order,Tabpage) as [U_Order] 
								 from #table2
								 declare _cursor cursor 
								 for 
									select distinct([sort]),
										   U_Order 
									from #table1 
									order by [U_Order]
								open _cursor
								fetch next from _cursor into @Loop,@Order
								while @@fetch_status=0 
								begin
									select ordinate,
										   [sort],
										   abscissa 
									from #table1 
									where [sort]=@Loop
								    fetch next from _cursor into @Loop,@Order
								end
								close _cursor
								deallocate _cursor' 
					when 2 then 'declare @Loop nvarchar(100)
								 select distinct([ordinate]) as [TabPage] 
								 into #table2 
								 from #table1
								 select Tabpage,
										row_number()over(order by Tabpage) as [U_Order] 
								 from #table2
								 declare _cursor cursor 
								 for 
									select distinct([ordinate]) 
									from #table1
								 open _cursor
								 fetch next from _cursor into @Loop
								 while @@fetch_status=0 
								 begin
									select [sort] as ordinate,ordinate as [sort],
										   abscissa 
								    from #table1 
								    where [ordinate]=@Loop
								    fetch next from _cursor into @Loop
								 end
							     close _cursor
								 deallocate _cursor' 
				    else 
				    	'select ''单表'' [TabPage],
				    		    1 [U_Order]
						 select ordinate,
							    [sort],
							    abscissa 
					     from #table1'
		         end)

-- print @Str
if dbo.U_IsNull(@Str)=1	exec sp_executesql @Str
-- return dbo.U_IsNull(@Str)
END
关在笼子里的老男人 2013-08-26
  • 打赏
  • 举报
 回复
引用 3 楼 sp1234 的回复:
你的所谓存储过程，返回给你的程序（不是查询分析器）多少条记录？
就是现在很郁闷，我的这个存储过程只有几百条数据回来。如果数据多的话时间长一点也就没有什么关系了。
以专业开发人员为伍 2013-08-26
  • 打赏
  • 举报
 回复
你的所谓存储过程，返回给你的程序（不是查询分析器）多少条记录？
关在笼子里的老男人 2013-08-26
  • 打赏
  • 举报
 回复
引用 1 楼 guwei4037 的回复:
在程序中单步调试，看究竟是哪一步执行慢，就知道问题在哪了。没有代码，只能给你这点参考。
我调试过，就是在SqlDataAdapter的Fill的方法的时候，这个过程持续了有95%以上的时间，但是我的这个存储过程在SQL Server查询分析器里面的执行速度是很快的。我刚开始以为是数据填充的是很有问题所以我采用了SqlCommand的ExccuteNonQuery()方法执行，但还是同样很慢，没有什么改变。 明天我过去贴上代码，然后各位大神看看。
全栈极简 2013-08-26
  • 打赏
  • 举报
 回复
在程序中单步调试，看究竟是哪一步执行慢，就知道问题在哪了。没有代码，只能给你这点参考。
同样的SQL语句在查询分析器执行很快，但是网站上执行超时的诡异问题
同样的SQL语句在查询分析器执行很快，但是网站上执行超时，这个问题以前遇到过，解决办法是重新启动服务器，但过一段时间后（时间长短不一定，一般为一天后），这次又出现了，不能总是重新启动服务器了事吧，决定探个究竟。 首先，打开SQLSERVER 事务探查器，找到那个执行超时的SQL语句： execsp_executesqlN' ...
SQL 语句在查询分析器执行很快，程序 Dapper 参数化查询就很慢（parameter-sniffing）（转）...
SQL 语句在查询分析器执行很快，程序 Dapper 参数化查询就很慢（parameter-sniffing） 这个问题困扰我好长时间了，使用SQLSERVER 事务探查器找到执行超时的SQL语句，参数查询都是通过执行exe sp_executesql 的存储过程调用，因为它能够分析并缓存查询计划，从而优化查询效率，但是现在反而很慢。本地调试没有问题，开始上线也没有问题，但是运行一...
改善C#程序，提高程序运行效率的50种方法
改善C#程序，提高程序运行效率的50种方法 转自：http://blog.sina.com.cn/s/blog_6f7a7fb501017p8a.html一、用属性代替可访问的字段 　　1、.NET数据绑定只支持数据绑定，使用属性可以获得数据绑定的好处； 　　2、在属性的get和set访问器重可使用lock添加多线程的支持。 　　二、readonly（运行时常量）和const（编译时常量）
慢查询的重构和优化特定类型的查询
目录为什么查询速度会慢如何找到慢查询慢查询原因偶尔很慢的情况访问数据过多是否向数据库请求了不需要的数据MySQL是否在扫描额外的记录重构查询的方式一个复杂查询还是多个简单查询切分查询分解关联查询慢查询总结优化特定类型的查询优化COUNT()查询（重要）COUNT()的作用关于MylSAM的神话简单的优化使用近似值写更复杂的优化优化关联查询（重要）优化子查询优化GROUP BY和DISTINCT优化GROUP BY WITH ROLLUP优化LIMIT分页（重要）优化SQL_CALC_FOUND_ROWS优化
MySQL高级特性三：在MySQL内部存储代码
MySQL允许通过触发器、存储过程、函数的形式来存储代码。从MySQL5.1开始，还可以在定时任务中存放代码，这个定时任务也被称为事件。存储过程和存储函数都被同城为存储程序。 这四种存储代码都使用特殊的sql语句扩展，它包含了很多过程处理语法，例如循环和条件分支等。不同类型的存储代码的主要却别在于其执行的上下文；也就是其输入和输出。存储过程和存储函数都可以接受参数然后返回值，但是触发器和事件却不...
C#

111,098

社区成员

642,554

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • AIGC Browser
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

让您成为最强悍的C#开发者

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

+ 用AI写文章