求大牛解决SqlParameter参数化时间超时问题

2015-01-27 10:38:12 346 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

408王道考研【数据结构】思维导图（章节详细可下载xmind文件）由于图片太多，不一一放上来了，还有部分章节【查找、排序、B树B+树等】在百度网盘，可以关注我的公众号领取【完整的图片+xmind文件】，如想要获取xmind文件的同学可以关注我的个人公众号“小柒很爱喵”，回复“数据结构导图”，可获得以上导图原图和xmind文件(可修改导图内容、查看导图隐藏的笔记与备注，即上图带杠杠的地方是我进一步助于理解的解释)。希望对你们... 阅读详情

问题是统计超时



select * from(

select count(0) from tabel1 where time1=@start and time2=@end  as n1

,select count(0) from tabel2 where time1=@start and time2=@end as n2

,select count(0) from tabel3 where time1=@start and time2=@end as n3

)t

这个SQL语句用SqlParameter参数化运行超时，参数类型是datetime



select * from(

select count(0) from tabel1 where time1='2015-01-24' and time2='2015-01-25' as n1

,select count(0) from tabel2 where time1='2015-01-24' and time2='2015-01-25' as n2

,select count(0) from tabel3 where time1='2015-01-24' and time2='2015-01-25' as n3

)t

直接串SQL也超时，把这语句放到SQL查询里也超时

但是



declare @start  datetime

declare @end datetime

set @start  =convert(datetime,'2015-01-24')

set @end =convert(datetime,'2015-01-25')

select * from(

select count(0) from tabel1 where time1=@start and time2=@end  as n1

,select count(0) from tabel2 where time1=@start and time2=@end as n2

,select count(0) from tabel3 where time1=@start and time2=@end as n3

)t

把变量声明到SQL里面，运行却是0秒

请问大牛，为什么C# 的参数化为什么跟SQL里声明变量运行时间相差那么大呢，他们有什么不同吗？
如果要像SQL里声明变量一样的运行效率，C#参数化要怎么写呢

...全文

STM32开发者必看：如何在正点原子探索者开发板上快速移植CANopenNode协议栈本文为STM32开发者提供了在正点原子探索者开发板上快速移植CANopenNode协议栈的实战指南。文章详细介绍了从环境准备、硬件抽象层适配到协议栈初始化与测试的全过程，并分享了PDO配置与性能优化等关键技巧，帮助开发者高效实现CANopen标准化通信。阅读详情

c02645

.NET社区 62,272 成员 668,968 内容

+ 关注 + 加入社区

切换为时间正序

娃都会打酱油了 2015-01-28

打赏
举报

你还是没搞懂问题，这是sql隐式转化导致的性能问题 datediff这东西其实就是把隐藏转换范围限制在了这个函数内部，但datediff这东西会导致索引无效

引用 12 楼 c02645 的回复:

解决了，用datediff解决了，datediff比直接大于等于快多了再笨点就用字符串拼到SQL的变量里

c02645 2015-01-28

打赏
举报

解决了，用datediff解决了，datediff比直接大于等于快多了再笨点就用字符串拼到SQL的变量里

江南小鱼 2015-01-27

打赏
举报

引用楼主 c02645 的回复:

问题是统计超时
select * from(
select count(0) from tabel1 where time1=@start and time2=@end  as n1
,select count(0) from tabel2 where time1=@start and time2=@end as n2
,select count(0) from tabel3 where time1=@start and time2=@end as n3
)t
这个SQL语句用SqlParameter参数化运行超时，参数类型是datetime
select * from(
select count(0) from tabel1 where time1='2015-01-24' and time2='2015-01-25' as n1
,select count(0) from tabel2 where time1='2015-01-24' and time2='2015-01-25' as n2
,select count(0) from tabel3 where time1='2015-01-24' and time2='2015-01-25' as n3
)t
直接串SQL也超时，把这语句放到SQL查询里也超时但是
declare @start  datetime
declare @end datetime
set @start  =convert(datetime,'2015-01-24')
set @end =convert(datetime,'2015-01-25')
select * from(
select count(0) from tabel1 where time1=@start and time2=@end  as n1
,select count(0) from tabel2 where time1=@start and time2=@end as n2
,select count(0) from tabel3 where time1=@start and time2=@end as n3
)t
把变量声明到SQL里面，运行却是0秒请问大牛，为什么C# 的参数化为什么跟SQL里声明变量运行时间相差那么大呢，他们有什么不同吗？如果要像SQL里声明变量一样的运行效率，C#参数化要怎么写呢

@start、@end传8位的字符串试下，是否超时？

select * from(
select count(0) from tabel1 where convert(varchar(8),time1,112)=@start and time2=@end  as n1
,select count(0) from tabel2 where convert(varchar(8),time1,112)=@start and time2=@end as n2
,select count(0) from tabel3 where convert(varchar(8),time1,112=@start and time2=@end as n3
)t

另外，看下执行计划，看慢在哪儿了

select * from(
select count(0) from tabel1 where time1='2015-01-24' and time2='2015-01-25' as n1
,select count(0) from tabel2 where time1='2015-01-24' and time2='2015-01-25' as n2
,select count(0) from tabel3 where time1='2015-01-24' and time2='2015-01-25' as n3
)t

娃都会打酱油了 2015-01-27

打赏
举报

引用 6 楼 c02645 的回复:

你们在百度“SqlParameter超时”，大把人有这问题，又不止我一人在说，数据库字段是datetime，查询直接字符串拼起来超时，SqlParameter参数化超时，declare声明变量就是0秒

http://blog.csdn.net/starfd/article/details/42004605 如果问题不是偶尔出现，而是必然出现，那问题很可能就是类型转化导致的，这个问题上面链接对应的英文网站中有原因描述，然后上面那个连接对应的问题是参数化声明为nvarchar，但实际数据库是varchar时，查询时超慢的问题

c02645 2015-01-27

打赏
举报

你们在百度“SqlParameter超时”，大把人有这问题，又不止我一人在说，数据库字段是datetime，查询直接字符串拼起来超时，SqlParameter参数化超时，declare声明变量就是0秒

娃都会打酱油了 2015-01-27

打赏
举报

在table后面加with(nolock)看看呗

S314324153 2015-01-27

打赏
举报

除了数据类型暂时没看到什么别的问题，会不会你调程序时，正好数据表被锁了

於黾 2015-01-27

打赏
举报

数据类型问题吧时间字段是时间型,你在数据库里定义成时间型,比较就会很快而你传入字符串型,数据库要先把每个字段里的时间型转成字符串再做字符串比较

blue_apple2006 2015-01-27

打赏
举报

应该没有什么区别，估计是你的sql写错了了条件吧，你检查下。另外你的n1,n2,n3,最好用 union 连接一下。不要用逗号。

c02645 2015-01-27

打赏
举报

哦，这里手打SQL写错了，是>=和<=

blue_apple2006 2015-01-27

打赏
举报

你的sql从哪里写出来的？你试过可以运行吗，语法都不对。

Giacinta_zw 2015-01-27

打赏
举报

。。。混个水

c02645 2015-01-27

打赏
举报

引用 8 楼 lovelj2012 的回复:

请问大牛，为什么C# 的参数化为什么跟SQL里声明变量运行时间相差那么大呢，他们有什么不同吗？如果要像SQL里声明变量一样的运行效率，C#参数化要怎么写呢

@start、@end传8位的字符串试下，是否超时？

select * from(
select count(0) from tabel1 where convert(varchar(8),time1,112)=@start and time2=@end  as n1
,select count(0) from tabel2 where convert(varchar(8),time1,112)=@start and time2=@end as n2
,select count(0) from tabel3 where convert(varchar(8),time1,112=@start and time2=@end as n3
)t

另外，看下执行计划，看慢在哪儿了

select * from(
select count(0) from tabel1 where time1='2015-01-24' and time2='2015-01-25' as n1
,select count(0) from tabel2 where time1='2015-01-24' and time2='2015-01-25' as n2
,select count(0) from tabel3 where time1='2015-01-24' and time2='2015-01-25' as n3
)t

[/quote]

select * from(
(select count(0) from tabel1 where convert(varchar(8),time1,112)<=@start and time2>=@end)  as n1
,(select count(0) from tabel2 where convert(varchar(8),time1,112)<=@start and time2>=@end) as n2
,(select count(0) from tabel3 where convert(varchar(8),time1,112<=@start and time2>=@end) as n3
)t

超时


select * from(
(select count(0) from tabel1 where time1<='2015-01-24' and time2>='2015-01-25') as n1
,(select count(0) from tabel2 where time1<='2015-01-24' and time2>='2015-01-25') as n2
,(select count(0) from tabel3 where time1<='2015-01-24' and time2>='2015-01-25') as n3
)t

这个也超时，直接查询分析器运行也超时


declare @start  datetime
declare @end datetime
set @start  =convert(datetime,@BeginTime)
set @end =convert(datetime,@EndTime)
select * from(
(select count(0) from tabel1 where time1<=@start and time2>=@end)  as n1
,select count(0) from tabel2 where time1<=@start and time2>=@end as n2
,select count(0) from tabel3 where time1<=@start and time2>=@end as n3
)t

SqlParameter[] parameters = { new SqlParameter("@BeginTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime)}; parameters[0].Value = time1.Value; parameters[1].Value = time2.Value; 反而这样运行是0秒，把SqlParameter指到SQL的变量最快，其它拼接SQL，参数放到SQL里都是超时

求大牛解决SqlParameter参数化时间超时问题

相关推荐