Geek Answers Handbook

What is an efficient swap method through very large result sets in SQL Server 2005?

EDIT: I'm still waiting for an answer. Thanks!

In SQL 2000 days, I used the temp table method, where you create a temporary table with a new identifier column and primary key, then select where the identifier column is between A and B.

When SQL 2005 appeared , I found out about Row_Number(), and I have used it since ...

But now I have found a serious performance issue Row_Number(). It works very well when you work with not-so-huge sets of results and sort by identification column. However, it works very poorly when you work with large result sets , such as more than 10,000 records, and sorting it by the non-identity column . Row_Number()performs poorly even if sorting is done by identifier column if the result set contains more than 250,000 records. For me, it got to the point that it throws an error, " team timeout! "

What do you use to create a large result set in SQL 2005? In this case, is the temp table method even better? I'm not sure if this method using a temporary table with SET ROWCOUNT will work better ... But some say that there is a problem with the wrong row number if you have a multi-column primary key.

In my case, I need to be able to sort the result set by a date type column ... for my production web application.

Let me know what you use for high-performance pagination in SQL 2005 . And I would also like to know a smart way to create indexes. I suspect that choosing the right primary keys and / or indexes (cluster / non-clustered) will play a big role here.

.

P.S. - , stackoverflow?

: ...

SELECT postID, postTitle, postDate
FROM
   (SELECT postID, postTitle, postDate, 
         ROW_NUMBER() OVER(ORDER BY postDate DESC, postID DESC) as RowNum
    FROM MyTable
   ) as DerivedMyTable
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

postID: Int, Identity (),

postDate: DateTime

: Row_Number()?

+5
2

, ROW_COUNT , PostDate. , , , PostDate, , . temp ( , / ).

, -.

: PostDate DESC, , ROW_NUMBER - (ORDER BY PostDate DESC,...)

, , SQL Server 2000 ROW_COUNT, , , . ( ). .

.

0

row_number() . 100 000 .

row_number(), :

SELECT column_list
FROM
   (SELECT column_list
         ROW_NUMBER() OVER(ORDER BY OrderByColumnName) as RowNum
    FROM MyTable m
   ) as DerivedTableName
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

... column_list / "OrderByColumnName"?

+7

More articles:


All Articles