select count(*) from record where date >
''19991201'' and date < ''19991214''and amount >
2000 (25秒)
select date,sum(amount) from record group by date
(55秒)
select count(*) from record where date >
''19990901'' and place in (''BJ'',''SH'') (27秒)
select count(*) from record where date >
''19991201'' and date < ''19991214'' and amount >
2000 (14秒)
select date,sum(amount) from record group by date
(28秒)
select count(*) from record where date >
''19990901'' and place in (''BJ'',''SH'')(14秒)
select count(*) from record where date >
''19991201'' and date < ''19991214'' and amount >
2000 (26秒)
select date,sum(amount) from record group by date
(27秒)
select count(*) from record where date >
''19990901'' and place in (''BJ, ''SH'')(< 1秒)
select count(*) from record where date >
''19991201'' and date < ''19991214'' and amount >
2000(< 1秒)
select date,sum(amount) from record group by date
(11秒)
select count(*) from record where date >
''19990901'' and place in (''BJ'',''SH'')(< 1秒)
select * from record where
substring(card_no,1,4)=''5378''(13秒)
select * from record where
amount/30< 1000(11秒)
select * from record where
convert(char(10),date,112)=''19991201''(10秒)
select * from record where card_no like
''5378%''(< 1秒)
select * from record where amount
< 1000*30(< 1秒)
select * from record where date= ''1999/12/01''
(< 1秒)
---- 你会发现SQL明显快起来!
---- 2.例:表stuff有200000行,id_no上有非群集索引,请看下面这个SQL:
select count(*) from stuff where id_no in(''0'',''1'')
(23秒)
select count(*) from stuff where id_no=''0''
select count(*) from stuff where id_no=''1''
---- 得到两个结果,再作一次加法合算。因为每句都使用了索引,执行时间只有3秒,在620000行下,时间也只有4秒。或
者,用更好的方法,写一个简单的存储过程:
create proc count_stuff as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no=''0''
select @b=count(*) from stuff where id_no=''1''
end
select @c=@a+@b
select @d=convert(char(10),@c)
print @d