【asp源码栏目提醒】:以下是网学会员为您推荐的asp源码-大数据量分页存储过程效率测试附代码 ASP ne - 技术总结,希望本篇文章对您学习有所帮助。
大数据量分页存储过程效率测试附代码
ASP ne 大数据量分页存储过程效率测试附代码在项目中我们经常遇到或用到分页那么在大数据量百万级以上下哪种分页算法效率最优呢我们不妨用事实说话。
测试环境 硬件CPU酷睿双核T5750
内存2G
软件Windows server
2003Sql server 2005 OK我们首先创建一数据库data_Test并在此数据库中创建一表tb_TestTable 1createdatabase data_Test--创建数据库data_Test 2GO 3use data_Test 4GO 5createtable tb_TestTable--
创建表 6 7 idintidentity11primarykey 8 userNamenvarchar20notnull 9 userPWDnvarchar20notnull 10 userEmailnvarchar40null 11 12GO 然后
我们在数据表中插入2000000条数据 1--插入数据 2setidentity_insert tb_TestTableon 3declarecountint 4setcount1 5whilecount
2000000 6begin 7insertinto tb_TestTableiduserNameuserPWDuser
Emailvaluescountadminadmin888lli
0077yahoo.com.cn 8setcountcount1 9end 10setidentity_insert tb_TestTableoff 我首先写了五个
常用存储过程 1利用select top和select not in进行分页具体代码如下 1createprocedure proc_paged_with_notin--利用select top and select not in 2 3pageIndexint--页索引 4pageSizeint--每页记录数 5 6as 7begin 8set nocounton 9declaretimediffdatetime--耗时 10declaresqlnvarchar500 11selecttimediffGetdate 12setsqlselect topstrpageSizefrom tb_TestTable whereID not inselect topstrpageSizepageIndexid from tb_TestTable order by ID ASCorder by ID 13executesql--因select top后不支技
直接接参数所以写成了字符串sql 14selectdatediffmstimediffGetDateas耗时 15set nocountoff 16end 2利用select top和select max列键 1createprocedure proc_paged_with_selectMax--利用select top and select max列 2 3pageIndexint--页索引 4pageSizeint--页记录数 5 6as 7begin 8set nocounton 9declaretimediffdatetime 10declaresqlnvarchar500 11selecttimediffGetdate 12set
sqlselect topstrpageSizeFrom tb_TestTable whereIDselect maxidFromselect topstrpageSizepageIndexid From tb_TestTable order by IDas TempTableorder by ID 13executesql 14selectdatediffmstimediffGetDateas耗时 15set nocountoff 16end 3利用select top和中间变量--此方法因网上有人说效果最佳所以贴出来一同测试 1createprocedure proc_paged_
with_Midvar--利用ID最大ID值和中间变量 2 3pageIndexint 4pageSizeint 5 6as 7declarecountint 8declareIDint 9declaretimediffdatetime 10declaresqlnvarchar500 11begin 12set nocounton 13selectcount0ID0timediffgetdate 14selectcountcount1IDcasewhencountpageSizepageIndexthen IDelseIDendfrom tb_testTableorderby id 15set
sqlselect topstrpageSizefrom tb_testTable where IDstrID 16executesql 17selectdatediffmstimediffgetdateas耗时 18set nocountoff 19end 20 4利用Row_number此方法为SQL server 2005中新的
方法利用Row_number给数据行加上索引 1createprocedure proc_paged_with_Rownumber--利用SQL 2
005中的Row_number 2 3pageIndexint 4pageSizeint 5 6as 7declaretimediffdatetime 8begin 9set nocounton 10selecttimediffgetdate 11selectfromselectRow_numberoverorderby IDascas IDRankfrom tb_testTableas IDWithRowNumberwhere IDRankpageSizepageIndexand IDRankpageSizepageIndex1 12selectdatediffmstimediffgetdateas耗时 13set nocountoff 14end 155利用临时表及Row_number 1createprocedure proc_CTE--利用临时表及Row_number 2 3pageIndexint--页索引 4pageSizeint--页记录数 5 6as 7set nocounton 8declarec
testrnvarchar400 9declarestrSqlnvarchar400 10declaredatediffdatetime 11begin 12selectdatediffGetDate 13setctestrwith Table_CTE as 14select ceilingRow_numberoverorder by ID ASC/strpageSizeas page_numfrom tb_TestTable 15setstrSqlctestrselectFrom Table_CTE where page_numstrpageIndex 16end 17begin 18execute sp_executesqlstrSql 19selectdatediffmsdatediffGetDate 20set nocountoff 21end 22 OK至此
存储过程创建完毕我们分别在每页10条数据的情况下在第2页第1000页第10000页第100000页第199999页进行测试耗时单位ms每页测试5次取其平均值 存过第2页耗时第1000页耗时第10000页耗时第100000页耗时第
199999页耗时效率排行 1用not in0ms16ms47ms475ms953ms3 2用select max5ms16ms35ms325ms623ms1 3中间变量966ms970ms960ms945ms933ms5 4row_number0ms0ms34ms365ms710ms2 4临时表780ms796ms798ms780ms805ms4 测试结果显示select max row_number not in临时表中间变量 于是我对
效率最高的select max方法用2分法进行了扩展代码取自互
联网我修改了ASC排序时取不到值的BUG测试结果 2分法156ms156ms180ms470ms156ms1 从测试结果来看使用2分法确实可以提高效率并使效率更为稳定我又增加了第159999页的测试用时仅296ms
效果相当
的不错 下面是2分法使用select max的代码已相当
完善。
1--/---存储过程分页处理孙伟2005-03-28创建---/ 2--/---存储过程分页处理浪尘2008-9-1
修改--/ 3--/---对数据进行了2分处理使查询前半部分数据与查询后半部分数据
性能相同---/ 4 5alter
PROCEDURE proc_paged_2part_selectMax 6 7tblNamenvarchar200--要显示的表或多个表的
连接 8fldNamenvarchar500--要显示的字段列表 9pageSizeint10--每页显示的
记录个数 10pageint1--要显示那一页的记录 11fldSortnvarchar200null--排序字段
列表或条件 12Sortbit0--排序方法0为升序1为降序如果是多字段排列Sort指代最后一个排序字段的排列顺序最后
一个排序字段不加排序标记--
程序传参如SortA AscSortB DescSortC 13strConditionnvarchar1000null--查询条件不需where 14IDnvarchar150--主表的主键 15Distbit0--
是否添加查询字段的DISTINCT默认0不添加/1添加 16pageCountint1 output--查询结果分页
后的总页数 17Countsint1 output--查询到的记录数 18 19AS 20SET NOCOUNTON 21DeclaresqlTmpnvarchar1000--存放
动态生成的SQL语句 22DeclarestrTmpnvarchar1000--存放取得查询结果总数的查询语句 23DeclarestrIDnvarchar1000--存放取得
查询开头或
结尾ID的查询语句 24 25DeclarestrSortTypenvarchar10--数据排序规则A 26DeclarestrFSortTypenvarchar10--数据排序
规则B 27 28DeclareSqlSelectnvarchar50--对含有DISTINCT的查询
进行SQL构造 29DeclareSqlCoun tsnvarchar50--对含有DISTINCT的总数查询进行SQL构造 30 31declaretimediffdatetime--耗时测试
时间差 32selecttimediffgetdate 33 34ifDist0 35begin 36setSqlSelectselect 37setSqlCountsCount 38end 39else 40begin 41setSqlSelectselect distinct 42setSqlCountsCountDISTINCTID 43end 44 45 46ifSort0 47begin 48setstrFSortTypeASC 49setstrSortTypeDESC 50end 51else 52begin 53setstrFSortTypeDESC 54setstrSortTypeASC 55end 56 57 58 59--
生成查询语句-- 60--此处strTmp为取得查询结果数量的语句 61ifstrConditionisnullorstrCondition--没有设置
显示条件 62begin 63setsqlTmpfldNameFromtblName 64setstrTmpSqlSelectCountsSqlCountsFROMtblName 65setstrIDFromtblName 66end 67else 68begin 69setsqlTmpfldNameFromtblNamewhere1 0strCondition 70setstrTmpSqlSelectCountsSqlCountsFROMtblNamewhere1 0strCondition 71setstrIDFromtblNamewhere1 0strCondition 72end 73 74--取得查询结果总数量--- 75
exec sp_executesqlstrTmpNCounts int outCounts out 76declaretmpCountsint 77ifCounts0 78settmpCounts1 79else 80settmpCountsCounts 81 82--取得分页总数 83setpageCounttmpCountspageSize-1/pageSize 84 85///
当前页大于总页数取
最后一页/ 86ifpagepageCount 87setpagepageCount 88 89--/---数据分页2分处理---/ 90declarepageIndexint--总数/页大小 91declarelastcountint--总数页大小 92 93setpageIndextmpCounts/pageSize 94setlastcounttmpCountspageSize 95iflastcount 0 96setpageIndexpageIndex1 97else 98setlastcountpagesize 99 100--//显示分页 101ifstrConditionisnullorstrCondition--
没有设置显示条件 102begin 103ifpageIndex 2orpagepageIndex/2pageIndex2--前半部分数据处理 104begin 105ifpage1 106setstrTmpSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 107order byfldSortstrFSortType 108else 109begin 110ifSort1 111begin 112setstrTmpSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 113whereIDselect minIDfromSqlSelecttopCASTpageSizepage-1asVarchar20IDfromtblName 114order byfldSortstrFSortTypeAS TBMinID 115order byfldSortstrFSortType 116end 117else 118begin 119setstrTmpSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 120whereIDselect maxIDfromSqlSelecttopCASTpageSizepage-1asVarchar20IDfromtblName 121order byfldSortstrFSortTypeAS TBMinID 122order byfldSortstrFSortType 123end 124end 125end 126else 127begin 128setpagepageIndex-page1--后半部分数据处理 129ifpage1--最后一页数据显示 130setstrTmpSqlSelectfromSqlSelecttopCASTlastcountasVARCHAR4fldNamefromtblName 131order byfldSortstrSortTypeAS TempTBorder byfldSortstrFSortType 132else 133ifSort1 134begin 135setstrTmpSqlSelectfromSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 136whereIDselect maxIDfromSqlSelecttopCASTpageSizep age-2lastcountasVarchar20IDfromtblName 137order byfldSortstrSortTypeAS TBMaxID 138order byfldSortstrSortTypeAS TempTBorder byfldSortstrFSortType 139end 140else 141begin 142setstrTmpSqlSelectfromSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 143whereIDselect minIDfromSqlSelecttopCASTpageSizepage-2lastcountasVarchar20IDfromtblName 144order byfldSortstrSortTypeAS TBMaxID 145order byfldSortstrSortTypeAS TempTBorder byfldSortstrFSortType 146end 147end 148end 149 150else--有查询条件 151begin 152ifpageIndex 2orpagepageIndex/2pageIndex2--前半部分数据处理 153begin 154ifpage1 155setstrTmpSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 156where 11strConditionorder byfldSortstrFSortType 157elseifSort1 158begin 159setstrTmpSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 160whereIDselect minIDfromSqlSelecttopCASTpageSizepage-1asVarchar20IDfromtblName 161where11strConditionorder byfldSortstrFSortTypeAS TBMinID 162strConditionorder byfldSortstrFSortType 163end 164else 165begin 166setstrTmpSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 167whereIDselect maxIDfromSqlSelecttopCASTpageSizepage-1asVarchar20IDfromtblName 168where11strConditionorder byfldSortstrFSortTypeAS TBMinID 169strConditionorder byfldSortstrFSortType 170end 171end 172else 173begin 174setpagepageIndex-page1--后半部分数据处理 175ifpage1--最后一页数据显示 176setstrTmpSqlSelectfromSqlSelecttopCASTlastcountasVARCHAR4fldNamefromtblName 177where11strConditionorder byfldSortstrSortTypeAS TempTBorder byfldSortstrFSortType 178elseifSort1 179setstrTmpSqlSelectfromSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 180whereIDselect maxIDfromSqlSelecttopCASTpageSizepage-2lastcountasVarchar20IDfromtblName 181where11strConditionorder byfldSortstrSortTypeAS TBMaxID 182strConditionorder byfldSortstrSortTypeAS TempTBorder byfldSortstrFSortType 183else 184setstrTmpSqlSelectfromSqlSelecttopCASTpageSizeasVARCHAR4fldNamefromtblName 185whereIDselect minIDfromSqlSelecttopCASTpageSizepage-2lastcountasVarchar20IDfromtblName 186where11strConditionorder byfldSortstrSortTypeAS TBMaxID 187strConditionorder byfldSortstrSortTypeAS TempTBorder byfldSortstrFSortType 188end 189end 190 191--返回查询结果--- 192exec sp_executesqlstrTmp 193selectdatediffmstimediffgetdateas耗时 1 94--printstrTmp 195SET NOCOUNTOFF 196GO 197 执行示例exec proc_paged_2part_selectMaxtb_testTableIDuserNameuserPWDuserEmail10100000ID0nullID0 这种测试只在单机进行并且没有在实际开发W
EB项目中分页测试测试项也
比较单一所以不够全面
系统但从其效率相比上我们
可以在数据库分页
算法上进行
有效的控制。
引文来源大数据量分页存储过程效率测试附代码-
ASP.netC-
博客园