Is there an easy way to page large datasets using the Access database via straight SQL? Let's say my query would normally return 100 rows, but I want the query to page through the results so that it only retrieves (let's say) the first 10 rows. Not until I request the next 10 rows would it query for rows 11-20.
+2
A:
If you run a ranking query, you will get a column containing ascending numbers in your output. You can then run a query against this column using a BETWEEN...AND clause to perform your paging.
So, for example, if your pages each contain 10 records and you want the third page, you would do:
SELECT * FROM MyRankingQuery WHERE MyAscendingField BETWEEN 30 and 39
How to Rank Records Within a Query
http://support.microsoft.com/?kbid=208946
Robert Harvey
2009-10-09 00:15:37
Was a little confused at first but after reading some more about this ranking stuff, I think that has solved my problem - thanks!
digiarnie
2009-10-09 00:43:36
@digiarnie, based on the workload you mentioned in your comments to David, you should consider upsizing your backend database to SQL Server Express. You will get a performance boost, and your application will be generally more reliable.
Robert Harvey
2009-10-09 05:10:55
A:
I'm not certain how ranking answers your question. Also, I'm having trouble imagining why you would need this -- this is usually something you do on a website in order to break down the data retrieved into small chunks. But a Jet/ACE database is not a very good candidate for a website back end, unless it's strictly read-only.
One other SQL solution would use nested TOP N, but usually requires on-the-fly procedural code to write the SQL.
It also has the problem with ties, in that unless you include a unique field in your ORDER BY, you can get 11 records with a TOP 10 should two records have a tie on the values in the ORDER BY clause.
I'm not suggesting this is a better solution, just a different one.
David-W-Fenton
2009-10-09 01:33:45
It's not website related, it's a legacy desktop app my company is using that's now getting slow due to people pumping in more and more data into certain tables. How would the TOP solution work in regards to getting rows 10-20 let's say if the ID column values are staggered and not sequential?
digiarnie
2009-10-09 01:44:44
If it's a desktop app, I question the approach you're contemplating. I've never seen a LAN-based Access app where that was necessary at all. Perhaps your fields are not appropriately indexed? If not, then breaking the data down into small sets is not going to improve things -- indeed, it will likely make things worse, as instead of taking one hit on that, you're taking the same hit every time you retrieve the next set of records.
David-W-Fenton
2009-10-10 00:45:09
The TOP N approach would work like this: Get recordcount, N. Retrieve TOP 20. For the next set, in a subquery with the reverse sort, retrieve TOP N-20. Using that as your FROM source, take TOP 20 on that, and so forth. As I said, not elegant. The ID values don't have anything to do with TOP N SELECTs -- it is entirely based on the SORT ORDER.
David-W-Fenton
2009-10-10 00:47:38
Thanks for that. I don't mind getting voted down if there's an explanation of why, but anonymous down votes are pretty hard to address. I try to make a point of not downvoting without a comment (unless it's obvious from my other participation in a thread and would just be piling on).
David-W-Fenton
2009-10-12 22:05:27
+1
A:
The Access Database Engine doesn’t handle this very well: the proprietary TOP N syntax returns ties and the N cannot be parameterized; the optimizer doesn't handle the equivalent subquery construct very well at all :(
But, to be fair, this is something SQL in general doesn't handle very well. This is one of the few scenarios where I would consider dynamic SQL (shudder). But first I would consider using an ADO classic recordset, which has properties for AbsolutePage, PageCount, and PageSize (which, incidentally, the DAO libraries lack).
You could also consider using the Access Database Engine's little-known LIMIT TO nn ROWS syntax. From the Access 2003 help:
You may want to use ANSI-92 SQL for the following reasons... ...
- Using the
LIMIT TO nn ROWSclause to limit the number of rows returned by a query
Could come in handy?
... my tongue is firmly embedded in my cheek :) This syntax doesn't exist in the Access Database Engine and never has. Instead, it's yet another example of the appalling state of the Access documentation on the engine side of the house.
Is the product fit for purpose if the documentation has massive holes and content cannot be trusted? is Caveat emptor.
onedaywhen
2009-10-12 09:23:57