建议:
1. count(*) 改为 count(1) 应该会快点,,,
2.子查询改为连接查询,,,
1. count(*) 改为 count(1) 应该会快点,,,
2.子查询改为连接查询,,,
解决方案 »
- VBA编程直接写数据库如何提高写入速度?
- 远端数据库下 无项目
- otl_stream查询产生大量数据 构造函数第一个参数buffer_size设置成多少合适? 在线
- 存储过程出错* ,case when len这句有错
- SQL2000的中值与平均值问题
- 谁能帮我解释一下下面这段语句,马上给他100分!!!!!!
- 删除一个数据库用户时提示:因为选定的用户拥有对象,所以无法除去该用户
- 数据库查询统计的问题
- 如何向SQL SERVER中已经存在的一个数据库中添加数据表?
- 有给DropDownListBox动态添加新单元的方法或原程序请发给我一个email如下
- SQL事务问题,再线等!!!!!!!
- 高手帮忙!这样的存储过程怎么写
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31 这里出了问题,用下面的方法会很快,但是不知道如何实现上面出来的效果,500W数据查询时间大约在2分钟左右set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[gryb]
@stime datetime,
@usercode varchar(50),
@cdid varchar(32)
as
declare @i int,@dt datetime,@ts int
set @ts=datediff(day,convert(char(8),min(@stime),120)+'1',convert(char(8),dateadd(month,1,max(@stime)),120)+'1')
select @dt=convert(char(8),min(@stime),120)+'1'
,@i=isnull(datediff(day,@dt,convert(char(8),dateadd(month,1,max(@stime)),120)+'1'),@ts)
from Comego_charge
where roadway=@cdid and user_code=@usercode
if @@rowcount<=0 return
set rowcount @i
select id=identity(int,0,1) into #t from syscolumns a,syscolumns b
set rowcount 0
select day=convert(varchar(10),@dt+b.id,120),(select count(id) from comego_charge where roadway=@cdid and user_code=@usercode and car_type='1' and addtime>=day and addtime<dateadd(day,1,day)) 一型车数量
from(
select convert(varchar(10),addtime,120) day
from comego_charge
where roadway=@cdid and user_code=@usercode
group by convert(varchar(10),addtime,120),roadway
)a right join #t b on datediff(day,@dt,a.day)=b.id
order by b.id
drop table #t
建议在存储数据的时候定义好格式,尽量不要对数据列AddTime使用函数,因为这样很影响查询性能。
http://blog.csdn.net/dutguoyi/archive/2006/01/10/575617.aspx
@stime datetime,
@usercode varchar(50),
@cdid varchar(32)
as
begin
declare @n table([id] int identity,flg varchar(1))
insert into @n(flg) select top 31 0 from syscolumns a,syscolumns b
select right(CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120),5) as 日期 ,
(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and (money_sort='普通收费' or car_type='统缴车型') and user_code=@usercode and roadway=@cdid) as 当前日期过车总数量,
(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='1' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 一型车数量,
(select isnull(sum(money),0) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='1' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 一型车金额,(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='2' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 二型车数量,
(select isnull(sum(money),0) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='2' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 二型车金额,(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='3' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 三型车数量,
(select isnull(sum(money),0) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='3' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 三型车金额,(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='4' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 四型车数量,
(select isnull(sum(money),0) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='4' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 四型车金额,
(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='5' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 五型车数量,
(select isnull(sum(money),0) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='5' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 五型车金额,
(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='6' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 六型车数量,
(select isnull(sum(money),0) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='6' and money_sort='普通收费' and user_code=@usercode and roadway=@cdid) as 六型车金额,
(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and car_type='统缴车型' and user_code=@usercode and roadway=@cdid) as 统缴车数量,
(SELECT isnull(SUM(Money),0) FROM Comego_charge WHERE CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and user_code=@usercode and roadway=@cdid and (id IN (SELECT MAX(id) AS Expr1 FROM Comego_charge WHERE (Car_Type = '统缴车型') GROUP BY Money_Sort)) ) as 统缴车金额,
(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and money_sort='月票' and user_code=@usercode and roadway=@cdid) as 月票车数量,
(select count(*) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and money_sort='免征车辆' and user_code=@usercode and roadway=@cdid) as 免征车数量,
(select isnull(sum(money),0) from comego_charge where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and money_sort='免征车辆' and user_code=@usercode and roadway=@cdid) as 免征车金额,
(select count(*) from comego_flux where CONVERT(char(10), AddTime, 120)=CONVERT(char(10),(min(dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))),120) and ifwork='闯道' and uid=@usercode and cdid=@cdid) as 闯道车数量,
(select user_name from comego_user where user_code=@usercode) as 收费员姓名,@usercode as 收费员工号,@stime as 所查询月份,@cdid as 车道 from comego_charge right join
(
select [id] from @n
) N
on datediff(day,dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)),comego_charge.addtime)=0
where datediff(month,dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)),@stime)>=0
group by datediff(day,0,dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))
order by datediff(day,0,dateadd(day,n.i,dateadd(month,datediff(month,0,@stime),0)))
end