双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

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

2017-01-04 01:59:46 893 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

卧_槽墨衍会员 · AI 创作全网分发关注 + 加入社区

CSDN第一期总结之二：ADO.NET + DataGrid的问题现在写程序要完全不沾数据库的边恐怕很难，那么用C#写程序也是一样，会经常遇到数据库方面的问题。在C#中操作数据库是使用ADO.Net来完成，而显示数据这一方面常常要用到的是DataGrid这个组件，因此这两个问题也是论坛中问得最多的。对于ADO.Net来说，涉及的问题大致分为如下几类：第一类，数据库的连接问题；第二类，数据库操作方面的问题，查询、更新等等；第三类，对数据集的操阅读详情

一条稍微复杂的Update语句。有一些avg计算什么的。

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

谁碰到过，怎么解决的？

...全文

【硬件开源实战】STM32G070RBT6开发板从设计到避坑全解析本文全面解析了STM32G070RBT6开发板从硬件设计到避坑的实战经验。详细介绍了电源设计、时钟电路、PCB布局等关键环节，分享了成本控制、焊接测试、软件开发和外设驱动的实用技巧，帮助开发者高效完成项目开发。阅读详情

卧_槽

C# 111,129 成员 642,540 内容

+ 关注 + 加入社区

切换为时间正序

吉普赛的歌 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）

✦双11狂欢大促✦

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

相关推荐

双11狂欢大促