'Paging' search results

[Joe Black]

DP, I wasn't suggesting storing entire tables in memory as some sort of cache, certainly not if they involve thousands of records. Small[ish] datasets are another story of course, which is the whole idea of object pooling etc.

My argument was simply why involve another layer of code (TSQL) which isn't generic and creates another layer of maintenance, dependency etc, when using things such as 'adaptor.fill(dataset, start, count)' does the job and is a dozen times more flexible, especially if wrapped in a nice way.

As for SPs, avoid them completely if possible, though have been known to use them for the odd bit of dynamic sql (oh dear) where sometimes the database engine still offers better performance for certain things...though here you're generally talking about very large datasets and manipulation, where even with the overheads the query engine it's still much faster than any code you could possibly write.

[DimPrawn]

cswd has some good ideas there, although some requirements are that user "browse" data. Take Google for example. People expect to be able to have a broad search and page through results.

I have worked on large online music repertoire systems and although there were tools to locate individual tracks and albums, users still expect to see list by artist, genre, year etc, which requires paging of results.

Coming back to the original question, there is no one definitive best solution, but for scalability sake it is better to have more round trips to the database and only retrieve enough data to service that request (cache static lookup data by all means) than it is to pull a large dataset into memory in the hope that the user might be interested in paging beyond the current screen.

Note that scalability isn't the same as performance. Pulling tons of data into RAM will give a small number of users great performance, but the system will not scale out to support a large number of users. Only stateless designs scale well.