H
H. Williams
I have been trying to determine the fastest way to implement database paging
for an Access database with an ASP.NET front-end. I have a difficult
database because the user can change the sorting and the fields are not
unique, which excludes the "Select TOP" approach. Also the database has
about 60,000 records and is growing. The user has to always have current
data which excludes caching.
I've tried the following approaches:
1. Using an OleDBDataAdapter to fill a datatable and then displaying the
paged records from the table.
2. Using an OleDBDataAdapter to partially fill a dataset with the paged
records and then running a second command to retrieve to the total records.
3. Looping through an OleDBDataReader until I get to the paged records and
a second command to retrieve to the total records.
4. Looping through an OleDBDataReader to get the paged records and then
completing the loop to count the total records.
All 4 methods return the current total records which I need to set up my
paging menu.
Approach #1 if by far the slowest (up to 4 times slower) in every scenario.
Approach #2 is the fastest most of the time. The Microsoft documentation
warns that this approach is inefficient, but I have not found this to be the
case. The only case where this approach faults is when the query's WHERE
clause is very complicated. However, when there is no WHERE statement or
ORDER BY and the page is near the start of the records then this approach is
about 4 times faster than the next fastest.
Approach #3 is slightly slower than #2 and also faults when the WHERE
statement is complex.
Approach #4 is also only slightly slower than #2, and it is not slowed down
by a complex WHERE statement.
My conclusion is that either #2 or #4 is best. Any suggestions would be
appreciated.
for an Access database with an ASP.NET front-end. I have a difficult
database because the user can change the sorting and the fields are not
unique, which excludes the "Select TOP" approach. Also the database has
about 60,000 records and is growing. The user has to always have current
data which excludes caching.
I've tried the following approaches:
1. Using an OleDBDataAdapter to fill a datatable and then displaying the
paged records from the table.
2. Using an OleDBDataAdapter to partially fill a dataset with the paged
records and then running a second command to retrieve to the total records.
3. Looping through an OleDBDataReader until I get to the paged records and
a second command to retrieve to the total records.
4. Looping through an OleDBDataReader to get the paged records and then
completing the loop to count the total records.
All 4 methods return the current total records which I need to set up my
paging menu.
Approach #1 if by far the slowest (up to 4 times slower) in every scenario.
Approach #2 is the fastest most of the time. The Microsoft documentation
warns that this approach is inefficient, but I have not found this to be the
case. The only case where this approach faults is when the query's WHERE
clause is very complicated. However, when there is no WHERE statement or
ORDER BY and the page is near the start of the records then this approach is
about 4 times faster than the next fastest.
Approach #3 is slightly slower than #2 and also faults when the WHERE
statement is complex.
Approach #4 is also only slightly slower than #2, and it is not slowed down
by a complex WHERE statement.
My conclusion is that either #2 or #4 is best. Any suggestions would be
appreciated.