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

[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#参数化要怎么写呢

[c02645]

引用 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里都是超时

[Giacinta_zw]

。。。混个水

[blue_apple2006]

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

[c02645]

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

[blue_apple2006]

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

[於黾]

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

[S314324153]

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

[娃都会打酱油了]

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

[c02645]

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

[娃都会打酱油了]

引用 6 楼 c02645 的回复:

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

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

[江南小鱼]

引用 楼主 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

[c02645]

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

[娃都会打酱油了]

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

引用 12 楼 c02645 的回复:

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