SQL 分页查询效率

用T-SQL做数据库分页查询也有好几年了，但对于各种查询方法的写法一至都没怎么去理会，最近参与了几个项目的开发，几个项目中分页查询的写法也不相同，这也让我产生了确认几种写法效率问题的兴趣。（注：我所说的几种写法都是需要返回总记录数的分页）

 

1、生成测试数据

select a.* into test from sys.columns a,sys.columns b

本机生成5564881行数据，相信也够多了吧。

 

主要说两种写法：

第一种:  分两个查询，先按查询条件查询出总记录数，再写一相同查询取出页数据；

第二种：将查询结果写入临时表，然后统计总记录数，再根据分页索引取出页数据； 

 

第一种写法SQL：

select count(*) from test where [name] like 'ActivityID' and max_length>1
 select * from(
 select a.[object_id],row_number() over(order by object_id) as rowIndex from test a
   where [name] = 'ActivityID' and max_length>1
 )t
 where rowIndex>100 and rowIndex<200

 

第二种写法SQL：

select a.[object_id],row_number() over(order by object_id) as rowIndex into #t from test a
  where [name] = 'ActivityID' and max_length>1
 
 select * from #t where rowIndex>100 and rowIndex<200
 select count(*) from #t
 drop table #t

 

测试结果：

当where 条件只有一个是，查询一性能优于查询二；但增加一条件后查询二性能优于查询一；

 

总结一下：

1、第一种写法优势在于I/O开销小于第二种写法，由其在满足条件返回数据越多的情况下性能越优；

2、第二种写法在查询条件复杂查询下性能优于第一种写法；

 

另外在使用Like 和=时一定要注意，=性能高于Like，在不使用通配符的情况下应使用=;（汗一个自己，以前习惯性的使用Like，这个习惯要好好的改一下了。）