20.SqlServer多条件查询性能测试

最近刷博客，听说union all的性能远高于or，正好又遇上又类似业务要做，索性就做了一个比较，因为是正式项目，所以没有模拟很大的数据量，直接用生产数据测试的。

数据总量为5483，从中筛选出需要的1051条数据。

1. 表结构

CREATE TABLE [dbo].[Device] (
  [uuid] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [device_type_uuid] varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [product_uuid] varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [project_uuid] varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [company_uuid] varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [device_code] varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [imei] varchar(100) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [iccard_code] varchar(100) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [device_name] varchar(100) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [device_status] int DEFAULT ((0)) NULL,
  [telecom_status] int DEFAULT ((0)) NULL,
  [fix_place] varchar(500) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [remark] varchar(max) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [insert_date] datetime DEFAULT (getdate()) NULL,
  [third_platform] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [third_device_id] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [telecom_time] datetime  NULL
)

2. or查询

declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
select * from Device where product_uuid='tuopu-temperature-hunidity' or product_uuid = 'tuopu-outdoor-bolt' or product_uuid = 'tuopu-water-level' or product_uuid = 'tuopu-water-pressure'
/*你的SQL脚本结束*/
select [语句or执行花费时间(毫秒)]=datediff(ms,@d,getdate())

3. like查询

declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
select * from Device where product_uuid like 'tuopu%'
/*你的SQL脚本结束*/
select [语句like执行花费时间(毫秒)]=datediff(ms,@d,getdate())

4. in查询

declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
select * from Device where product_uuid in ('tuopu-temperature-hunidity','tuopu-outdoor-bolt','tuopu-water-level','tuopu-water-pressure') 
/*你的SQL脚本结束*/
select [语句in执行花费时间(毫秒)]=datediff(ms,@d,getdate())

5. union查询

declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
select * from Device where product_uuid='tuopu-temperature-hunidity'
union all
select * from Device where product_uuid='tuopu-outdoor-bolt'
union all
select * from Device where product_uuid='tuopu-water-level'
union all
select * from Device where product_uuid='tuopu-water-pressure'

/*你的SQL脚本结束*/
select [语句union执行花费时间(毫秒)]=datediff(ms,@d,getdate())

6. 测试结果