If you use hundreds of thousands of row data, you can check it out.
Your stored procedure is more than NOT IN pagination, example: SELECT TOP 10 * from Customers Where Customerid Not in (SELECT TOP 20 Customerid from Customers)
Declare @sqlstr varchar (8000) set @ SQLSTR = 'SELECT TOP' CAST (@ @ size as varchar) '* FROM table Where primary key column Not in (SELECT TOP' CAST (@ @ @ 每 * @ @ Page As Varchar) 'Primary List of FROM Table)' EXEC (@SQLSTR)
I personally think the best paging method is: select top 10 * from table where id> 200 is written into a stored procedure, the above statement wants to spell a SQL statement, to get the last greater than which ID number
That way of using the cursor, only suitable for small data volume, if the table is above 10,000 lines, it is badly
You can check the analyzer in the SQL query, look at the execution time. Display execution plan, display server tracking statistics, display customer statistics
Here is the way I use the stored procedure paging, paginating a table of 200,000, the result is displayed in the web page, you can take a look at the speed:
http://www.bizlands.com/trade/search.asp?type=s&sortid=15
http://www.yibiz.com/trade/search.asp?sortid=5&type= Supply & Page = 3 & Country = & sf = & cs = & txtitle = & datetime =
Http://www.yibiz.com/trade/search.asp?sortid=7&type= supply
In ASP.NET, more than 10,000 lines of data are not recommended to bring a paging method with DataGrid, I usually write SP yourself.
I have been a long time, I said to use SQL's cursors.
The cursor is stored in memory and is very consumed.
The cursor is built, and the relevant record is locked until the cursor is canceled.
The cursor provides a means of scanning a row of row by line, generally uses a cursor to cross data, and perform different operations depending on the different data conditions. The cycle of the cursor (large data set) defined in multi-table and big tables is easy to enter a long wait or even crash.
So, my personal experience is 10,000 upstairs tables, no cursor. Small data volume, you can use a cursor
Because of the cursor, it is still a good way to traverse small data volume!
I personally think the best paging method is: select top 10 * from table where id> 200 is written into a stored procedure, the above statement wants to spell a SQL statement, to get the last greater than which ID number
