Ramblings from the Creator of WilsonDotNet.com
Oh man, play time tomarrow! Nice work Paul, can't wait to play with this.
Outstanding, Paul! Thank you for your continued support and commitment, as well as a big thanks to all those who have contributed. WilsonORMapper is a brilliant tool.
"Efficient Paging with Row_Number"??? Looking at the execution plan it doesn't seem efficient(at least according to my tests). if you select pageindex 100 with pagesize 10 out of a 10000 rows table, guess how many rows does the sql server scan? Br, Deyan Petrov
You or your DBA are still responsible for proper database indexing. O/R Mappers do NOT remove the need for database optimization -- they free your team from boring CRUD so that they can focus their time where it should be spent.
How would you index the dynamically ROW_NUMBER generated column?? Because afterall it gets down to this, when you want to filter out 1 page out of the 100 pages resultset ... Or you mean the 100 pages resultset should have been narrowed down before doing the ROW_NUMBER to 1 page using other indexes??
The ROW_NUMBER column shouldn't be the problem, at least I don't think so anyhow. The problem you are seeing is most likely the ORDER BY clause, and if you have a WHERE clause then it too possibly.
Congratulations on shipping a new version Paul!
How do you use a variable in the order by clause of the row_number() function?
I don't use a variable for that -- O/R Mappers produce dynamic sql, some of it parameterized and some of it not.