ADO.net超时，查询分析器很快的问题

卧_槽 2017-01-04 01:59:46
一条稍微复杂的Update语句。有一些avg计算什么的。

用查询分析器是毫秒级的执行速度。到ADO.net里面就会超时，超过1分钟。

谁碰到过，怎么解决的？
...全文
688 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2017-01-18
  • 打赏
  • 举报
 回复
引用 23 楼 yuwenge 的回复:
[quote=引用 21 楼 yenange 的回复:] [quote=引用 20 楼 sp1234 的回复:] 你就看看你有多少个“from TipsterFilterObject where FilterType=@FilterType”吧！ 还有，谁告诉你说可以滥用“with (nolock)”的了？
DECLARE @FilterType NVARCHAR
SET @FilterType = '1'
;WITH t AS (
SELECT 
	STDEV(hitrate) AS hitrate_STDEV
	,AVG(hitrate)AS hitrate_AVG
	,STDEV(profit) AS profit_STDEV
	,AVG(profit)AS profit_AVG
	,STDEV(profitbillity) AS profitbillity_STDEV
	,AVG(profitbillity)AS profitbillity_AVG
	,STDEV(tipscount) AS tipscount_STDEV
	,AVG(tipscount)AS tipscount_AVG
	,STDEV(redtimescount) AS redtimescount_STDEV
	,AVG(redtimescount)AS redtimescount_AVG
   FROM   TipsterFilterObject
   WHERE  FilterType         = @FilterType
)
UPDATE TipsterFilterObject
SET    CalculatePoints = IIF(t.hitrate_STDEV= 0,0, 0.3 * 1000 * ( hitrate -t.hitrate_AVG) / t.hitrate_STDEV)
       + IIF(t.profit_STDEV= 0,0, 0.3 * 1000 * ( profit -t.profit_AVG) / t.profit_STDEV)
       + IIF(t.profitbillity_STDEV= 0,0, 0.1 * 1000 * ( profitbillity - t.profitbillity_AVG) / t.profitbillity_STDEV)
       + IIF(t.tipscount_STDEV= 0,0, 0.1 * 1000 * ( tipscount -t.tipscount_AVG) / t.tipscount_STDEV)
       + IIF(t.redtimescount_STDEV= 0,0, 0.2 * 1000 * ( redtimescount -t.redtimescount_AVG) / t.redtimescount_STDEV)
FROM t
WHERE  FilterType= @FilterType
楼主换这个试试[/quote] 多谢，后来改了with as。但是之前加了几个索引之后，性能问题解决了。所以也不知道到底是with 起作用了，还是索引起作用了。[/quote] 可以删除索引， 再试下效果。 没事就结贴吧。
卧_槽 2017-01-17
  • 打赏
  • 举报
 回复
引用 21 楼 yenange 的回复:
[quote=引用 20 楼 sp1234 的回复:] 你就看看你有多少个“from TipsterFilterObject where FilterType=@FilterType”吧！ 还有，谁告诉你说可以滥用“with (nolock)”的了？
DECLARE @FilterType NVARCHAR
SET @FilterType = '1'
;WITH t AS (
SELECT 
	STDEV(hitrate) AS hitrate_STDEV
	,AVG(hitrate)AS hitrate_AVG
	,STDEV(profit) AS profit_STDEV
	,AVG(profit)AS profit_AVG
	,STDEV(profitbillity) AS profitbillity_STDEV
	,AVG(profitbillity)AS profitbillity_AVG
	,STDEV(tipscount) AS tipscount_STDEV
	,AVG(tipscount)AS tipscount_AVG
	,STDEV(redtimescount) AS redtimescount_STDEV
	,AVG(redtimescount)AS redtimescount_AVG
   FROM   TipsterFilterObject
   WHERE  FilterType         = @FilterType
)
UPDATE TipsterFilterObject
SET    CalculatePoints = IIF(t.hitrate_STDEV= 0,0, 0.3 * 1000 * ( hitrate -t.hitrate_AVG) / t.hitrate_STDEV)
       + IIF(t.profit_STDEV= 0,0, 0.3 * 1000 * ( profit -t.profit_AVG) / t.profit_STDEV)
       + IIF(t.profitbillity_STDEV= 0,0, 0.1 * 1000 * ( profitbillity - t.profitbillity_AVG) / t.profitbillity_STDEV)
       + IIF(t.tipscount_STDEV= 0,0, 0.1 * 1000 * ( tipscount -t.tipscount_AVG) / t.tipscount_STDEV)
       + IIF(t.redtimescount_STDEV= 0,0, 0.2 * 1000 * ( redtimescount -t.redtimescount_AVG) / t.redtimescount_STDEV)
FROM t
WHERE  FilterType= @FilterType
楼主换这个试试[/quote] 多谢，后来改了with as。但是之前加了几个索引之后，性能问题解决了。所以也不知道到底是with 起作用了，还是索引起作用了。
卧_槽 2017-01-17
  • 打赏
  • 举报
 回复
引用 19 楼 sp1234 的回复:
[quote=引用 7 楼 yuwenge 的回复:] 

 declare @FilterType nvarchar
 set @FilterType='1'
 update TipsterFilterObject set CalculatePoints=
 iif((select STDEV(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType )=0,0, 0.3*1000*(hitrate-(select AVG(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.3*1000*(profit-(select AVG(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(profitbillity) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.1*1000*(profitbillity-(select AVG(profitbillity)  from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(profitbillity)  from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(tipscount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.1*1000*(tipscount-(select AVG(tipscount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(tipscount) from TipsterFilterObject with(NOLOCK) where  FilterType=@FilterType))
 +iif((select STDEV(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.2*1000*(redtimescount-(select AVG(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)) where  FilterType=@FilterType
脚本如上，NOLOCK是后加的，然并卵。
这么多个子查询？我从来没见过程序员能写出这么糟糕的 sql，你是怎么做的？[/quote] 因为整张表只有2000行数据，所以就这么写的。 大婶还是一如既往的光埋汰人，不帮助解决实际问题啊。后来改了with as ,也木有什么卵用。
吉普赛的歌 2017-01-10
  • 打赏
  • 举报
 回复
引用 20 楼 sp1234 的回复:
你就看看你有多少个“from TipsterFilterObject where FilterType=@FilterType”吧！ 还有，谁告诉你说可以滥用“with (nolock)”的了？
DECLARE @FilterType NVARCHAR
SET @FilterType = '1'
;WITH t AS (
SELECT 
	STDEV(hitrate) AS hitrate_STDEV
	,AVG(hitrate)AS hitrate_AVG
	,STDEV(profit) AS profit_STDEV
	,AVG(profit)AS profit_AVG
	,STDEV(profitbillity) AS profitbillity_STDEV
	,AVG(profitbillity)AS profitbillity_AVG
	,STDEV(tipscount) AS tipscount_STDEV
	,AVG(tipscount)AS tipscount_AVG
	,STDEV(redtimescount) AS redtimescount_STDEV
	,AVG(redtimescount)AS redtimescount_AVG
   FROM   TipsterFilterObject
   WHERE  FilterType         = @FilterType
)
UPDATE TipsterFilterObject
SET    CalculatePoints = IIF(t.hitrate_STDEV= 0,0, 0.3 * 1000 * ( hitrate -t.hitrate_AVG) / t.hitrate_STDEV)
       + IIF(t.profit_STDEV= 0,0, 0.3 * 1000 * ( profit -t.profit_AVG) / t.profit_STDEV)
       + IIF(t.profitbillity_STDEV= 0,0, 0.1 * 1000 * ( profitbillity - t.profitbillity_AVG) / t.profitbillity_STDEV)
       + IIF(t.tipscount_STDEV= 0,0, 0.1 * 1000 * ( tipscount -t.tipscount_AVG) / t.tipscount_STDEV)
       + IIF(t.redtimescount_STDEV= 0,0, 0.2 * 1000 * ( redtimescount -t.redtimescount_AVG) / t.redtimescount_STDEV)
FROM t
WHERE  FilterType= @FilterType
楼主换这个试试
以专业开发人员为伍 2017-01-09
  • 打赏
  • 举报
 回复
你就看看你有多少个“from TipsterFilterObject where FilterType=@FilterType”吧！ 还有，谁告诉你说可以滥用“with (nolock)”的了？
以专业开发人员为伍 2017-01-09
  • 打赏
  • 举报
 回复
引用 7 楼 yuwenge 的回复:

 declare @FilterType nvarchar
 set @FilterType='1'
 update TipsterFilterObject set CalculatePoints=
 iif((select STDEV(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType )=0,0, 0.3*1000*(hitrate-(select AVG(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.3*1000*(profit-(select AVG(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(profitbillity) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.1*1000*(profitbillity-(select AVG(profitbillity)  from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(profitbillity)  from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(tipscount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.1*1000*(tipscount-(select AVG(tipscount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(tipscount) from TipsterFilterObject with(NOLOCK) where  FilterType=@FilterType))
 +iif((select STDEV(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.2*1000*(redtimescount-(select AVG(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)) where  FilterType=@FilterType
脚本如上，NOLOCK是后加的，然并卵。
这么多个子查询？我从来没见过程序员能写出这么糟糕的 sql，你是怎么做的？
卧_槽 2017-01-09
  • 打赏
  • 举报
 回复
引用 17 楼 D56233577 的回复:
[quote=引用 16 楼 yuwenge 的回复:] 就是 Microsoft SQL Server Management Studio 毫秒级就是执行时间显示0s
你用ADO.NET执行SELECT NEWID()多久返回？[/quote] 秒回。
D56233577 2017-01-04
  • 打赏
  • 举报
 回复
引用 16 楼 yuwenge 的回复:
就是 Microsoft SQL Server Management Studio 毫秒级就是执行时间显示0s
你用ADO.NET执行SELECT NEWID()多久返回？
卧_槽 2017-01-04
  • 打赏
  • 举报
 回复
引用 15 楼 D56233577 的回复:
你说的查询分析器是指？你这个毫秒级不靠谱吧。
就是 Microsoft SQL Server Management Studio 毫秒级就是执行时间显示0s
D56233577 2017-01-04
  • 打赏
  • 举报
 回复
你说的查询分析器是指？你这个毫秒级不靠谱吧。
卧_槽 2017-01-04
  • 打赏
  • 举报
 回复
引用 10 楼 Chinajiyong 的回复:
ADO.NET 改用调存储过程
改成存储过程之后，在查询分析器里执行存储过程都需要30s了。
卧_槽 2017-01-04
  • 打赏
  • 举报
 回复
引用 11 楼 starfd 的回复:
https://lostechies.com/jimmybogard/2012/07/18/troubleshooting-sql-index-performance-on-varchar-columns/ 估计你就是因为类似这种的隐式转换问题吧 我反正就碰到过数据库是Varchar，我指定的param参数是String，也就是NVarchar，然后查询起码也有1分钟的样子，而在查询分析器里面就是秒查，改成一致后，查询就秒查了
我的其实是int。。。无论改什么都一样。
sy401042879 2017-01-04
  • 打赏
  • 举报
 回复
sqlhelper底层那块应该有点问题。看看是否close了，实在测试不出来，就先不用你们的底层，自己简单的update下，看看执行效率如何。
娃都会打酱油了 2017-01-04
  • 打赏
  • 举报
 回复
https://lostechies.com/jimmybogard/2012/07/18/troubleshooting-sql-index-performance-on-varchar-columns/ 估计你就是因为类似这种的隐式转换问题吧 我反正就碰到过数据库是Varchar，我指定的param参数是String，也就是NVarchar，然后查询起码也有1分钟的样子，而在查询分析器里面就是秒查，改成一致后，查询就秒查了
EnForGrass 2017-01-04
  • 打赏
  • 举报
 回复
ADO.NET 改用调存储过程
正怒月神 2017-01-04
  • 打赏
  • 举报
 回复
excute只支持单条命令，而你的语句时批处理的。 所以应该 调用存储过程 的方式来执行
正怒月神 2017-01-04
  • 打赏
  • 举报
 回复
将这段sql作为存储过程，然后程序里执行存储过程。
卧_槽 2017-01-04
  • 打赏
  • 举报
 回复 

 declare @FilterType nvarchar
 set @FilterType='1'
 update TipsterFilterObject set CalculatePoints=
 iif((select STDEV(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType )=0,0, 0.3*1000*(hitrate-(select AVG(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(hitrate) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.3*1000*(profit-(select AVG(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(profit) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(profitbillity) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.1*1000*(profitbillity-(select AVG(profitbillity)  from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(profitbillity)  from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))
 +iif((select STDEV(tipscount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.1*1000*(tipscount-(select AVG(tipscount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(tipscount) from TipsterFilterObject with(NOLOCK) where  FilterType=@FilterType))
 +iif((select STDEV(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)=0,0, 0.2*1000*(redtimescount-(select AVG(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType))/(select STDEV(redtimescount) from TipsterFilterObject with(NOLOCK) where FilterType=@FilterType)) where  FilterType=@FilterType
脚本如上，NOLOCK是后加的，然并卵。
卧_槽 2017-01-04
  • 打赏
  • 举报
 回复
引用 5 楼 hanjun0612 的回复:
不应该啊。你跨库操作了吗？
没有，而且只对一张表操作，操作数据只有2000行
正怒月神 2017-01-04
  • 打赏
  • 举报
 回复
不应该啊。你跨库操作了吗？
加载更多回复（4）
。net图书管理系统设计方案
后台页面将显示用户还未归还书籍的相关信息，管理员也可以通过模糊或精确查询查询有关用户未还书的信息，可以查看具体借书的信息及超时，罚款等信息。 建立应用程序层次结构 在介绍系统中各个主要功能实现模块之前，...
JAVA上百实例源码以及开源项目
5个目标文件，演示Address EJB的实现，创建一个EJB测试客户端，得到名字上下文，查询jndi名，通过强制转型得到Home接口，getInitialContext()函数返回一个经过初始化的上下文，用client的getHome()函数调用Home接口...
JAVA上百实例源码以及开源项目源代码
5个目标文件，演示Address EJB的实现，创建一个EJB测试客户端，得到名字上下文，查询jndi名，通过强制转型得到Home接口，getInitialContext()函数返回一个经过初始化的上下文，用client的getHome()函数调用Home接口...
细说ADO.NET数据库连接池
通过前几章的学习，不知道大家对ADO.NET有一定的了解了没有。撇开文章质量不讲，必须肯定的是，我是用心去写每一篇文章的。无论是是在排版上，还是在内容选取上我都花了不少心思。我希望通过本系列文章，无论是新手...
【重读MSDN之ADO.NET】ADO.NET连接
连接到ADO.NET中的数据源 在 ADO.NET 中，通过在连接字符串中提供必要的身份验证信息，使用 Connection 对象连接到特定的数据源。使用的 Connection 对象取决于数据源的类型。随 .NET Framework 提供的每个 .NET ...
C#

110,534

社区成员

642,575

社区内容

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

加载中

查看更多榜单
社区公告

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

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

+ 用AI写文章