Question

如何提高千万条数据分页查询效率

Answer 1

SQL语句如下:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH OrderedResults AS
( SELECT R.MyUserID,R.Pername,R.Sex,R.Birthday,workedcomnumber,workedyear,workedmonth, R.Hometown,R.Location,R.updateDate,R.photoflag,[PhotoName], R.selfdescription,R.ResumeStatus,R.checkflag, isnull(R.joblocation1,'') as joblocation1,isnull(R.joblocation2,'') as joblocation2,isnull(R.joblocation3,'') as joblocation3, R.JobFunction1,R.JobFunction2,R.JobFunction3, isnull(R.JobSeeking1,'') as JobSeeking1,isnull(R.JobSeeking2,'') as JobSeeking2,isnull(R.JobSeeking3,'') as JobSeeking3, [dbo].[F_GetMyWorkExp](R.MyUserID) as workExp, [dbo].[F_GetMyEducation](R.MyUserID) as schools, ROW_NUMBER() OVER (order by ResID) as RowNumber
FROM wuerbajob.dbo.My_Resume R
WHERE 1=1  and checkflag=2
and (ResumeStatus=0 or ResumeStatus=2)
and R.updatedate between '2002-01-01 00:00' and  '2012-08-28 17:02' )

select * from OrderedResults orderR left outer join weibo.dbo.v_getAllUserLabels userLabel on orderR.myuserid=userLabel.userid where  orderR.RowNumber between 1 and 10000

测试结果:
单线程环境下 花费时间3分钟,去掉left outer join weibo.dbo.v_getAllUserLabels,效率差不多

已做如下操作:
表my_resume 已根据resID分为10个分区表(测试表明加了分区,只快了1分钟左右)
resID为索引