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