Tested C # + Stored Procedure million-level data Paging

To find an efficient stored procedure, the following stored procedure pages in C # + MSSQL are tested. Test conditions:

Environment: vs2008 + mssq2000

Data volume: 2 million

Test the following two stored procedures:

First:

Create procedure getpagenostr (@ SQL nvarchar (2000), @ currentpage Int = 1, @ pagesize Int = 20, @ order nvarchar (1000) = NULL) asdeclare @ P1 int declare @ sql1 nvarchar (4000) set @ sql1 = stuff (@ SQL, 7, charindex ('from', @ SQL)-7, 'count (*) ') if (@ order <> ''and @ order is not null) set @ SQL = @ SQL + 'ORDER BY' + @ order -- // note: @ scrolopt = 1 the total number of rows when the SELECT statement is obtained exec sp_cursoropen @ P1 output, @ SQL, @ scrolopt = 2if (@ P1! = 0) Begin set @ currentpage = (@ currentpage-1) * @ pagesize + 1 exec sp_cursorfetch @ P1, 32, @ currentpage, @ pagesize exec sp_cursorclose @ P1 endexec (@ sql1) go

Second:

Create procedure [DBO]. [getpagestr] (@ table_name varchar (5000), -- table name @ sign_record varchar (50), -- primary key @ filter_condition varchar (1000), -- filter condition, without where @ page_size int, -- page size @ page_index int, -- page index @ taxisfield varchar (1000), -- sorting field @ taxis_sign int, -- sorting method 1 is desc, 0 is ASC @ brief varchar (1000), -- the searched field @ record_count int -- total number of records) asbegin declare @ start_number intdeclare @ end_number intdeclare @ topn_number int declare @ ssql varchar (8000) if (@ find_recordlist = '') Begin select @ find_recordlist = '*' End select @ start_number = (@ Page_Index-1) * @ page_sizeif @ start_number <= 0 select @ start_number = 0 select @ end_number = @ start_number + @ page_sizeif @ end_number> @ record_count select @ end_number = @ record_count select @ topn_number = @ end_number- @ start_number if @ topn_number <= 0 select @ topn_number = 0 print @ topn_number print @ start_number print @ end_number print @ record_count if @ taxisfield = ''begin select @ taxisfield = @ sign_record end if @ taxis_sign = 0 begin if @ filter_condition = ''in in select @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name + 'where' + @ sign_record + 'in (select top '+ Cast (@ topn_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'where' + @ sign_record + 'in (select top '+ Cast (@ end_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'ORDER BY' + @ taxisfield + ') Order by' + @ taxisfield + 'desc) order by '+ @ taxisfield endelsebeginselect @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name + 'where' + @ sign_record + 'in (select top' + Cast (@ topn_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'where' + @ sign_record + 'in (select top '+ Cast (@ end_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'where' + @ filter_condition + 'ORDER BY' + @ taxisfield + ') and '+ @ filter_condition + 'ORDER BY' + @ taxisfield +' DESC) and '+ @ filter_condition + 'ORDER BY' + @ taxisfield endendelsebeginif @ filter_condition = 'beginselect @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name + 'where' + @ sign_record + 'in (select top '+ Cast (@ topn_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'where' + @ sign_record + 'in (select top '+ Cast (@ end_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'ORDER BY' + @ taxisfield + 'desc) Order by' + @ taxisfield + ') order by '+ @ taxisfield + 'desc' endelsebeginselect @ ssql = 'select' + @ find_recordlist + 'from' + @ table_name + 'where' + @ sign_record +' in (select top '+ Cast (@ topn_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'where' + @ sign_record + 'in (select top '+ Cast (@ end_number as varchar (10 )) + ''+ @ sign_record + 'from' + @ table_name + 'where' + @ filter_condition + 'ORDER BY' + @ taxisfield + 'desc) and '+ @ filter_condition + 'ORDER BY' + @ taxisfield +') and '+ @ filter_condition + 'ORDER BY' + @ taxisfield + 'desc' endendexec (@ ssql) if @ error <> 0 return-3 return 0 end print @ ssqlgo

 

The data volume is 2 million pieces of test data. During the test, the vs2008 + mssql2000 + aspnetpager is used for paging. The test is as follows:

First storage process execution time: 0.2624258

 

Second storage process execution time: 0.001699

 

Therefore, the second storage process is more efficient. This article is for reference only. writing this article only marks the late-night testing work.