Three implementation methods of the data result set paging function in ase

---------------------------------------------------------------------------

---- This is andkylee's personal originality. Please repost it with respect to the author's Labor achievements;

---- The original source must be specified for reprinting.
:
Http://blog.csdn.net/andkylee

--- 2010-05-31 21:13:58

---- Keyword: ASE pagination top identity set rowcount temp table auto-incrementing temporary and shoddy tables

----------------------------------------------------------------------------

 

Next, I will discuss the paging method in MySQL. This article briefly discusses how to implement paging of data results in Sybase ASE.

This article introduces three methods.

 

First: Use a cursor

Programmers prefer to use cursors, because the "loop" traversal of cursors is similar to the for, while, and loop statement implementation methods in programming languages. The general procedure of using a cursor is to define a cursor for the specified SQL statement, open and move the cursor, and then retrieve data based on the number of rows to be extracted after moving to the record row of the specified row number. On the surface, this solution solves the problem of extracting data within a specified range.
There may be serious performance problems. Creating a cursor consumes a certain amount of system resources. When a table contains tens of millions or even hundreds of millions of data records and requires a large amount of data results, use the cursor to retrieve this row of data every time it moves, then move the cursor. This process will be slow. When using a cursor, the system adds a shared lock to the corresponding table, causing lock competition and seriously affecting the database performance.

 

The implementation method of the cursor is not introduced here. This method is relatively simple.

 

Type 2: use temporary tables and flag Columns

 

In Sybase ase12.5.3 and later versions, we can use the top keyword to limit only the first n rows of data in the returned result set. In versions earlier than ase12.5.3, the set rowcount n method can only be used to "save the nation by curve.

If we want to use top to obtain the data from row n to row N + M of the result set, we can easily think of the following: Execute top twice, two more reverse sorting.

The procedure is as follows:

(1) Select Top N + M * fromTable_name
Where_clause
Order by ID
This result set is derived from table: table_name1

(2) Select top M * fromTable_name1
Order by ID DESC
This result set is derived from table: table_name2

(3) Select * fromTable_name2
Order by ID DESC

The above three statements seem to be able to meet the requirements for returning data from row n to row N + M. However, in Sybase ASE, this requirement cannot be met only by using a derived table instead of a temporary table.

Only the limit "the SQL statement for deriving a derived table (derived table) in ASE cannot contain the order by clause" is enough to make the above method unfeasible. Another restriction is that the ID column is used in the preceding three steps for sorting. If no sorting column is available in the table, the preceding method cannot be used. Fortunately, columns (numeric or date type) can be used as sorting columns for all tables whose result sets are paging ).

 

Continue to find a usable method. The following describes the paging method of general ase. The key to this idea is to generate an identity auto-incrementing column and a temporary table.

In ase, if you find a way to implement paging without using a temporary table, please let me know. I tried it many times and it was not ideal.

 

In summary, there are two main statements:

(1) Select syb = identity (10), *# Temp_table
FromTable_name
Where_clause
Order_by_clause

(2) Select * from# Temp_table
Where_clause
And syb> =N
And syb <=N + m

 

Here is an example:

(1) create a test table: Testa

Create Table TESTA (ID int not null, name varchar (30) null) <br/> go

 

(2) Insert Test Data

Insert into Testa <br/> select 1, 'liuzhenfu' <br/> go <br/> insert into Testa <br/> select 2, 'andkylil' <br/> go <br/>

 

(3) Insert a large number of duplicate data cyclically,

Insert into Testa <br/> select ID + (select max (ID) from Testa), name from Testa <br/> go 15

Insert existing data into the table Testa cyclically. After 15 times, the data in the table Testa reaches 2 ^ 16 = 65536 rows.

 

(4) use a temporary table + auto-incrementing flag column to extract data from 100th rows to 200th rows.

The statement is as follows:

Select syb = identity (10), * into # Tempa from Testa

Select * from
# Tempa where syb> = 100 and syb <= 200

Drop table # Tempa

The returned result is:

1> select syb = identity (10 ), * Into # Tempa from Testa <br/> 2> select * from # Tempa where syb> = 100 and syb <= 200 <br/> 3> go <br/> (65536 rows affected) <br/> syb ID name <br/> --------------------- ------------------------- <br/> 100 100 andkylee <br/> 101 101 liuzhenfu <br/> 102 102 andkylee <br/> 103 103 liuzhenfu <br/> 104 104 105 andkylee <br/> 105 106 liuzhenfu <br/> 106 107 107 andkylee <br/> 108 108 liuzhenfu <br/> andkylee <br/> 109 109 liuzhenfu <br/> 110 110 andkylee <br/> 111 111 liuzhenfu <br/> 112 112 andkylee <br/> 113 113 liuzhenfu <br/> 114 114 andkylee <br/> 115 115 liuzhenfu <br/> 116 116 andkylee <br/> 117 117 liuzhenfu <br/> 118 118 119 andkylee <br/> 119 120 liuzhenfu <br/> 120 andkylee <br/> 121 121 liuzhenfu <br/> 122 122 andkylee <br/> 123 123 124 liuzhenfu <br/> 124 125 andkylee <br/> 125 liuzhenfu <br/> 126 126 andkylee <br/> 127 127 liuzhenfu <br/> 128 128 andkylee <br/> 129 129 130 liuzhenfu <br/> 130 131 andkylee <br/> 131 liuzhenfu <br /> 132 132 andkylee <br/> 133 133 liuzhenfu <br/> 134 134 andkylee <br/> 135 135 136 liuzhenfu <br/> 136 137 andkylee <br/> 137 liuzhenfu <br/> 138 138 andkylee <br/> 139 139 liuzhenfu <br/> 140 140 andkylee <br/> 141 141 liuzhenfu <br/> 142 142 andkylee <br/> 143 143 liuzhenfu <br/> 144 144 145 andkylee <br/> 145 146 liuzhenfu <br/> 146 147 147 andkylee <br/> 148 148 liuzhenfu <br/> andkylee <br/> 149 149 liuzhenfu <br/> 150 150 andkylee <br/> 151 151 liuzhenfu <br/> 152 152 andkylee <br/> 153 153 liuzhenfu <br/> 154 154 andkylee <br/> 155 155 liuzhenfu <br/> 156 156 andkylee <br/> 157 157 liuzhenfu <br/> 158 158 159 andkylee <br/> 159 160 liuzhenfu <br/> 160 andkylee <br/> 161 161 liuzhenfu <br/> 162 162 andkylee <br/> 163 163 164 liuzhenfu <br/> 164 165 andkylee <br/> 165 liuzhenfu <br/> 166 166 andkylee <br/> 167 167 liuzhenfu <br/> 168 168 andkylee <br/> 169 169 170 liuzhenfu <br/> 170 171 andkylee <br/> 171 liuzhenfu <br /> 172 172 andkylee <br/> 173 173 liuzhenfu <br/> 174 174 andkylee <br/> 175 175 176 liuzhenfu <br/> 176 177 andkylee <br/> 177 liuzhenfu <br/> 178 178 andkylee <br/> 179 179 liuzhenfu <br/> 180 180 andkylee <br/> 181 181 liuzhenfu <br/> 182 182 andkylee <br/> 183 183 liuzhenfu <br/> 184 184 185 andkylee <br/> 185 186 liuzhenfu <br/> 186 187 187 andkylee <br/> 188 188 liuzhenfu <br/> andkylee <br/> 189 189 liuzhenfu <br/> 190 190 andkylee <br/> 191 191 liuzhenfu <br/> 192 192 andkylee <br/> 193 193 liuzhenfu <br/> 194 194 andkylee <br/> 195 195 liuzhenfu <br/> 196 196 andkylee <br/> 197 197 liuzhenfu <br/> 198 198 199 andkylee <br/> 199 200 liuzhenfu <br/> 200 andkylee <br/> (101 rows affected)

 

Replace the asterisk * in select * from # Tempa with the column name to be returned.

 

Continue ....

 

When data from rows 100th to 200th that meet the requirements of name = 'andkylile' is returned

Select syb = identity (10), * into # Tempa from Testa whereName = 'andkylil'

Select * from # Tempa where syb> = 100 and syb <= 200

 

Drop table # Tempa

 

 

 

Third: Use rowcount

This method is a bit inadequate: Must be usedColumns that can be sorted
Sorts result sets.

In the preceding test table Testa, if 10 rows of data are selected starting from row 9,000th, the statement is as follows:

Declare @ id1 int
Set rowcount 9000
Select @ id1 = ID from Testa order by ID
Set rowcount 10
Select * From Testa where ID >=@ id1 order by ID
Set rowcount 0
Go

 

In this method, the core statement is select @ id1 = ID from Testa order by ID. When querying the table Testa, each read row will assign the value of the ID column to the variable @ id1, which continues until the last row. The variable @ id1 is repeatedly refreshed by the id value of the next row, only the last same ID value is returned. If the rowcount limit is added before the SELECT statement, the @ id1 variable can be used to obtain the id value of the row rowcount. Then, the starting point of the returned range result set is obtained.

Set rowcount 10

Select * From Testa where ID >=@ id1 order by ID

These two statements can be replaced by a select top 10 * From Testa where ID> = @ id1 order by ID.

 

In this way, two different implementation forms are:

Declare @ id1 int
Set rowcount 9000
Select @ id1 = ID from Testa order by ID
Set rowcount 0
Select top 10 * From Testa where ID >=@ id1 order by ID
Go

 

Let's take a look at the execution results.

1> declare @ id1 int <br/> 2> set rowcount 9000 <br/> 3> select @ id1 = ID from Testa order by ID <br/> 4> set rowcount 10 <br/> 5> select * From Testa where ID >=@ id1 order by ID <br/> 6> set rowcount 0 <br/> 7> go <br/> (9000 rows affected) <br/> ID name <br/> ----------- ---------------------------- <br/> 9000 andkylee <br/> 9001 liuzhenfu <br/> 9002 andkylee <br/> 9003 liuzhenfu <br/> 9004 andkylee <br/> 9005 liuzhenfu <br/> 9006 andkylee <br/> 9007 liuzhenfu <br/> 9008 andkylee <br/> 9009 liuzhenfu <br/> (10 rows affected) <br/> 1>

Result of the second method:

1> declare @ id1 int <br/> 2> set rowcount 9000 <br/> 3> select @ id1 = ID from Testa order by ID <br/> 4> set rowcount 0 <br/> 5> select top 10 * From Testa where ID >=@ id1 order by ID <br/> 6> go <br/> (9000 rows affected) <br/> ID name <br/> ----------- ---------------------------- <br/> 9000 andkylee <br/> 9001 liuzhenfu <br/> 9002 andkylee <br/> 9003 liuzhenfu <br/> 9004 andkylee <br/> 9005 liuzhenfu <br/> 9006 andkylee <br/> 9007 liuzhenfu <br/> 9008 andkylee <br/> 9009 liuzhenfu <br/> (10 rows affected) <br/> 1>

Of course, the two results are exactly the same.

 

 

Finally, we test that the order value of the ID column in the testa table is disordered to see the execution of the preceding statements. Run:

Update Testa Set ID = ID + Cast (RAND () * 65536 as INT)

 

After the ID column value is disrupted, the data in the first 100 rows is:

1> select Top 100 * From Testa <br/> 2> go <br/> ID name <br/> -------------------------------------- <br/> 51366 liuzhenfu <br/> 33573 andkylee <br/> 19447 liuzhenfu <br/> 19408 andkylee <br/> 57839 liuzhenfu <br/> 18817 andkylee <br/> ............. ......... <br/> 19075 liuzhenfu <br/> 17081 andkylee <br/> 26444 liuzhenfu <br/> 6620 andkylee <br/> 52344 liuzhenfu <br/> 49348 andkylee <br/> (100 rows affected)

 

 

We need to return 10 rows of data starting from the first row that meet name = 'andkylile.

Declare @ id1 int
Set rowcount 9000
Select @ id1 = ID from Testa where name = 'andkylil' order by ID
Set rowcount 10
Select * From Testa where name = 'andkylil' and ID >=@ id1 order by ID
Set rowcount 0
Go

 

Result:

1> declare @ id1 int <br/> 2> set rowcount 9000 <br/> 3> select @ id1 = ID from Testa where name = 'andkylil' order by ID <br/> 4> set rowcount 10 <br/> 5> select * From Testa where name = 'andkylee 'and ID >=@ id1 order by ID <br/> 6> set rowcount 0 <br/> 7> go <br/> (9000 rows affected) <br/> ID name <br/> ----------- ---------------------------- <br/> 48639 andkylee <br/> 48639 andkylee <br/> 48641 andkylee <br/> 48641 andkylee <br/> 48642 andkylee <br/> 48643 andkylee <br/> 48644 andkylee <br/> 48644 andkylee <br/> 48650 andkylee <br/> 48650 andkylee <br/> (10 rows affected)

 

 

If the ID column is not sorted, the following SQL statement is available:

Declare @ id1 int
Set rowcount 9000
Select @ id1 = ID from Testa where name = 'andkylil'
Set rowcount 10
Select * From Testa where name = 'andkylee 'and ID >=@ id1
Set rowcount 0
Go

 

The corresponding result set is:

1> declare @ id1 int <br/> 2> set rowcount 9000 <br/> 3> select @ id1 = ID from Testa where name = 'andkylil' <br/> 4> set rowcount 10 <br/> 5> select * From Testa where name = 'andkylee 'and ID >=@ id1 <br/> 6> set rowcount 0 <br/> 7> go <br/> (9000 rows affected) <br/> ID name <br/> ----------- ---------------------------- <br/> 74076 andkylee <br/> 74514 andkylee <br/> 74053 andkylee <br/> 74385 andkylee <br/> 74339 andkylee <br/> 74792 andkylee <br/> 74794 andkylee <br/> 74984 andkylee <br/> 75052 andkylee <br/> 74138 andkylee <br/> (10 rows affected) <br/> 1>

 

The results of the two statements are different.

 

I think since all requests require the return of the result set of the specified range, there must be a basis for sorting. Otherwise, how can I know which range to return?

 

In addition, the third method I provided can get the correct result even if no sorting is specified during table scanning. Because during table scanning, results may be returned based on the physical location of the table data on the physical page.

 

Let's introduce it here first. It may be supplemented as needed in the future.