62,243
社区成员




declare @bd datetime ='2017-07-06'
select isnull(sum(b.score),0) as SumScore,c.kindid,c.kindName from [thtreasuredb].[dbo].RecordDrawInfo a with(nolock) left join thtreasuredb.dbo.RecordDrawScore b with(nolock) on a.drawid=b.drawid left join [THPlatformDB].[dbo].[GameKindItemWeb] c with(nolock) on a.kindid=c.kindid where userid in(select userid from thaccountsdb.dbo.accountsinfo with(nolock) where isandroid=0 and nullity=0 and userid not in (select account from [THTreasureDB].dbo.web_serialnumlog where type=7)) and b.inserttime>@bd group by c.kindid,c.kindName
exec sp_executesql N'select isnull(sum(b.score),0) as SumScore,c.kindid,c.kindName from [thtreasuredb].[dbo].RecordDrawInfo a with(nolock) left join thtreasuredb.dbo.RecordDrawScore b with(nolock) on a.drawid=b.drawid left join [THPlatformDB].[dbo].[GameKindItemWeb] c with(nolock) on a.kindid=c.kindid where userid in(select userid from thaccountsdb.dbo.accountsinfo with(nolock) where isandroid=0 and nullity=0 and userid not in (select account from [THTreasureDB].dbo.web_serialnumlog where type=7)) and b.inserttime>@bd group by c.kindid,c.kindName',N'@bd datetime',@bd=N'2017-07-06'
执行上面的没有问题,但是使用参数化查询执行下面的却有问题,上面的要2s,下面的要17sdeclare @bd datetime ='2017-07-06'
select isnull(sum(b.score),0) as SumScore,a.kindid,c.kindName from [thtreasuredb].[dbo].RecordDrawInfo a with(nolock) left join thtreasuredb.dbo.RecordDrawScore b with(nolock) on a.drawid=b.drawid left join [THPlatformDB].[dbo].[GameKindItemWeb] c with(nolock) on a.kindid=c.kindid where userid in(select userid from thaccountsdb.dbo.accountsinfo with(nolock) where isandroid=0 and nullity=0 and userid not in (select account from [THTreasureDB].dbo.web_serialnumlog where type=7)) and b.inserttime>@bd group by a.kindid,c.kindName
select isnull(sum(b.score),0) as SumScore,c.kindid,c.kindName from [thtreasuredb].[dbo].RecordDrawInfo a with(nolock) left join thtreasuredb.dbo.RecordDrawScore b with(nolock) on a.drawid=b.drawid left join [THPlatformDB].[dbo].[GameKindItemWeb] c with(nolock) on a.kindid=c.kindid where userid in(select userid from thaccountsdb.dbo.accountsinfo with(nolock) where isandroid=0 and nullity=0 and userid not in (select account from [THTreasureDB].dbo.web_serialnumlog where type=7)) and b.inserttime>='2017/7/6' group by c.kindid,c.kindName
程序中解决方案是,之前使用参数化查询,是使用 SqlHelper.ExecuteDataset(sql,sqlparameter数组)来进行。
直接使用拼接sql来进行是不行的,因为效率跟上面是一样的,需要拼接成下面的样式:
declare @bd datetime ='2017-07-06'
select isnull(sum(b.score),0) as SumScore,a.kindid,c.kindName from [thtreasuredb].[dbo].RecordDrawInfo a with(nolock) left join thtreasuredb.dbo.RecordDrawScore b with(nolock) on a.drawid=b.drawid left join [THPlatformDB].[dbo].[GameKindItemWeb] c with(nolock) on a.kindid=c.kindid where userid in(select userid from thaccountsdb.dbo.accountsinfo with(nolock) where isandroid=0 and nullity=0 and userid not in (select account from [THTreasureDB].dbo.web_serialnumlog where type=7)) and b.inserttime>@bd group by a.kindid,c.kindName
防止sql注入,是使用的格式强转来实现,比如保证datetime一定能转换为datetime类型。