代码改变世界

MS SQL 技巧系列(二)SQL查询的性能大PK之:or vs. union

2010-03-18 12:06  Virus-BeautyCode  阅读(3407)  评论(11编辑  收藏  举报

  在sql查询的where条件语句中,可以使用and和or实现逻辑的判断。如果where比较复杂的话,就会产生and 和 or的嵌套使用,写起来会很费力气,看起来就更是一头雾水了。

  于是有人就想起了union,其实它是可以替代or的,反正就是把结果串联起来,貌似应该可以。而且,写起来更加容易,看起来也很清晰。但是不知道两个的性能如何呢?下面我就做一个比较,建立三张表,分别插入10万,100万和1000万的数据,每张表格都有8个字段,然后在三种数据量下,做三个字段的or和union,五个字段的or和union,然后通过查询时间比较一下他们的效率吧。

  硬件环境:Q8200  4GB 内存

  操作系统:Windows2003 R2

  数据库:SQL SERVER 2005

代码
create database test
go
use test
go
--建立测试表1
create table table1
(
  col1 
varchar(20),
  col2 
varchar(20),
  col3 
varchar(20),
  col4 
varchar(20),
  col5 
varchar(20),
  col6 
varchar(20),
  col7 
varchar(20),
  col8 
varchar(20)
)
go
--插入10万数据
declare @i int
set @i=1
while(@i<100000)

  begin
    insert into table1 values('123','123','123','123','123','123','123','123')
    set @i=@i+1
  end

go
--建立测试表2
create table table2
(
  col1 
varchar(20),
  col2 
varchar(20),
  col3 
varchar(20),
  col4 
varchar(20),
  col5 
varchar(20),
  col6 
varchar(20),
  col7 
varchar(20),
  col8 
varchar(20)
)
go
--插入100万数据
declare @i int
set @i=1
while(@i<1000000)

  begin
    insert into table2 values('123','123','123','123','123','123','123','123')
    set @i=@i+1
  end

go
--建立测试表3
create table table3
(
  col1 
varchar(20),
  col2 
varchar(20),
  col3 
varchar(20),
  col4 
varchar(20),
  col5 
varchar(20),
  col6 
varchar(20),
  col7 
varchar(20),
  col8 
varchar(20)
)
go
--插入1000万数据
declare @i int
set @i=1
while(@i<1000000)

  begin
    insert into table3 values('123','123','123','123','123','123','123','123')
    set @i=@i+1
  end

go
--耗时4秒
select * from table1 
where col1='123' or col2='123' or col3='123' 
go
--耗时11秒
select * from table1
where col1='123' 
union all
select * from table1
where col2='123' 
union all
select * from table1
where col3='123' 
go
--耗时4秒
select * from table1 
where col1='123' or col2='123' or col3='123' or col4='123' or col5='123' 
go
--耗时19秒
select * from table1
where col1='123' 
union all
select * from table1
where col2='123' 
union all
select * from table1
where col3='123' 
union all
select * from table1
where col4='123' 
union all
select * from table1
where col5='123' 
go

--耗时37秒
select * from table2 
where col1='123' or col2='123' or col3='123' 
go
--耗时1分53秒
select * from table2
where col1='123' 
union all
select * from table2
where col2='123' 
union all
select * from table2
where col3='123' 
go
--耗时38秒
select * from table2 
where col1='123' or col2='123' or col3='123' or col4='123' or col5='123' 
go
--耗时2分24秒
select * from table2
where col1='123' 
union all
select * from table2
where col2='123' 
union all
select * from table2
where col3='123' 
union all
select * from table2
where col4='123' 
union all
select * from table2
where col5='123' 
go


drop table table1
drop table table2
drop table table3
drop database test

 

  从上面的可以看出来使用or和union连接where条件的话,数据10w和100w没有差距,只是在1000w的时候急速增大,但是同等数据量的话,使用or和union就表现了很大的差距,尽管union写起来和看起来都比较好理解。

  结论:我想是因为每次使用union都会扫描一次表结构,or虽然难些难看,但是只扫描一次表结构,所以数据量上去的话,就会体现出来更大的优势。

  结论仅供参考,欢迎大家一起讨论。

  刚才看见一篇极限挑战—100万条数据导入SQL SERVER数据库仅用4秒 (附源码),好文章,转载一下地址。感谢作者的无私分享。