sqlserver中使⽤row_number进⾏分页,效率很⾼!速度很
快!
最近,童鞋要做⼀个分页,数据量很⼤,⼏⼗万条数据,更坑爹的是,还是要经过表连接进⾏查询,速度慢得像
乌龟!!
怎么进⾏优化,可以让逻辑更清晰,效率更⾼呢?我试探的过程⼤概分为以下⼏个步骤:
1.⾸先因为要进⾏多张表连接,所以在数据库中先做了⼀个视图,把表连接起来。(但是这样查询速度和直接在
sql语句中写表连接差不多,但是如果把表连接写在视图中,程序中的sql语句将变得简洁,思路更清晰)
建⽴视图的sql语句如下:视图名为searchJCR
SELECT dbo.JCR_Table.JournalID, dbo.JCR_Table.JournalFullTitle, dbo.JCR_Table.JournalISOTitle, dbo.JCR_Table.JournalJCRTitle,
dbo.JCR_Table.JournalISSN, dbo.JCR_Table.JournalLanguage, dbo.JCR_Table.JournalUpdateYear, dbo.SubjectCategories.TotalJouranls, dbo.SubjectCategories.JournalRank, dbo.SubjectCategories.JournalQuartile, dbo.JCRCategory_Table.CategoriesEName,
dbo.JCRCategory_Table.CategoriesCName, dbo.JCRIF_Table.YearNum, dbo.JCRIF_Table.IFScore, dbo.JCRCategory_Table.CategoriesID FROM dbo.JCR_Table INNER JOIN
dbo.JCRIF_Table ON dbo.JCR_Table.JournalID = dbo.JCRIF_Table.JournalID INNER JOIN
dbo.SubjectCategories ON dbo.JCR_Table.JournalID = dbo.SubjectCategories.JournalID AND
dbo.SubjectCategories.YearNum = dbo.JCRIF_Table.YearNum INNER JOIN
dbo.JCRCategory_Table ON dbo.SubjectCategories.CategoriesID = dbo.JCRCategory_Table.CategoriesID
2.然后再程序中使⽤传统⽅法进⾏分页。
这时候出现了⼀个问题,使⽤的视图查询出来的结果⼜很多JournalID重复的记录,这样使⽤JournalID进⾏分页就不可⾏了。所以需要做的就是为每⼀条查询记录指定唯⼀⼀个标识,在oracle中有伪列rowid,可以⽤于区分每⼀条记录,在sql server2005之前,没有办法区分,但是在sql2005之后,提供了⼀个ROW_NUMBER() OVER(PARTITION BY
COLUMN ORDER BY COLUMN)函数⽤于实现类似于伪列的效果。
3.然后在程序中使⽤row_number()over()进⾏分页。语句如下
select top 20 * from (select row_number()over(order by YearNum DESC,JournalID ASC) as rowid,sJCR.* from searchJCR
查询速度很慢。后来做了优化(CTE,关于CTE的介绍将读者参阅),优化后的代码如下:
with t_rowtable
as
(
select row_number()over(order by YearNum DESC,JournalID ASC) as row_number,* from [JournalDB]. [dbo].[searchJCR] where 1=1
)
select * from t_rowtable where row_number>100
and row_number <= 60000
row_number函数的⽤途是⾮常⼴泛,这个函数的功能是为查询出来的每⼀⾏记录⽣成⼀个序号,⽣成的机制是,按
column函数怎么使用over(排序条件)中的排序条件对每条记录顺序⽣成⼀个rowid,然后将记录按select的order by顺序显⽰出来。其
中“where row_number>100
and row_number <= 60000” 可以⽤来控制每页的显⽰的记录,可以⽤来分页,“where row_number>(page-
1)*pageSize
and row_number <= page*pageSize”,并且只需要将筛选记录的条件拼接在where 1=1之后,这样即使上⼗⼏万的数据都可以在3s中之内查询出来,效率很⾼。例如:
row_number列是由row_number函数⽣成的序号列。在使⽤row_number函数是要使⽤over⼦句选择对某⼀列进⾏排序,然后才能⽣成序号。
实际上,row_number函数⽣成序号的基本原理是先使⽤over⼦句中的排序语句对记录进⾏排序,然后按着这个顺序⽣成序号。over⼦句中的order by⼦句与SQL语句中的order by⼦句没有任何关系,这两处的order by 可以完全不同,如下⾯的SQL语句所⽰:
select row_number() over(order by field2 desc) as row_number,*from t_table order by field1 desc
上⾯的SQL语句的查询结果如图所⽰。
另外要注意的是,如果将row_number函数⽤于分页处理,over⼦句中的order by 与排序记录的order by 应相同,否则⽣成的序号可能不是有续的。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论