Reply to: 'Paging' search results

[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.

[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.

[scotspine]

i'm never happy with dynamic sql. avoid it like the plague if i can and usually i can. i'd rather have more sp's.
besides a half decent code gen app will churn them out all day. and of course you can get much greater granularity over security/permissions.

[DimPrawn]

No, since then we are in the realms of dynamic SQL.

I would be surprised if in terms of searching and paging, a web app is dealing with no more than 10 or 15 main tables in a schema, so we are talking about 10 or 15 stored procedures to handle the paging requests.

A good DBA/Developer can write those in a day.

[scotspine]

are you suggesting passing in tablenames?

[DimPrawn]

Joe it's simple. The web is stateless. Large stateful object models have no place in scalable web apps. It's great when developers think the answer is to build a big OO snapshot of the data in memory, cos that's what they've been doing on the desktop or client-server, but when you hold data in memory on the web server, that data has to serve maybe 10,000 simultaneous users, each requiring a different view of the data. Even if the OO model of the data is only 1MB, multiply that up by the number of users over the lifetime of a session and you quickly run out of RAM, unless you have 64 bit webservers with TB memory.

The only scalable workable solution is to retrieve from the database, render the view for that user and discard all state from memory, ready to serve the next request. To improve performance you can cache the output based on the users parameters, assuming you don't mind what might be stale results being displayed.

You don't need to write a stored proc for every request, since you can parameterise them.

[Joe Black]

Oh dear...

.Net, that's the answer.

You either have to hit the database once, pull a large dataset into memory and page throught that OR just ask the database for the page (say 50) results the user wants to see at that point.

The 2nd approach is much more scalable. Using SQL Server and a store procedure one call is all that is required per page of results. You pass in the page number (page 1,2,3, whatever) and how many items per page (e.g 50). The stored proc counts the total row count and works out how many pages there are, what the rownumber is for the first row on that page and what the rownumber is for the last row on the requested page and returns these results.

The webserver converts this data to HTML, chucks it to the browser and discards the data from memory.

This scales well, even if the search results might contain 10 million results.

Hmm...so when he wants to add paging to another result-set then it's another stored-procedure..and then another...and then another.

I thought the idea of .Not was reuse, object model, encapsulation etc...

[janey]

Was a big fan of PB used it from Version 1 to version 6. It was the best tool of its time but then came the crash of y2k and moved on to all things Java. Surprised you are still getting work with it thought the whole market died.

there's still a lot out there. big systems which people jsut can't afford to convert to java/.net etc
the work is mainly in the financial industry but it is elsewhere too.

It would seem that it is slowly dying out (unfortunately for me) but I keep hoping it will see a small revival if sybase manage to get it more up to speed with current technologies. I still haven't seen anything which can compete with the PB datawindow.

[privateeye]

not a fan of PB then?!

Was a big fan of PB used it from Version 1 to version 6. It was the best tool of its time but then came the crash of y2k and moved on to all things Java. Surprised you are still getting work with it thought the whole market died.

[janey]

You're right tell them to get well away from VB - and don't come near PB either

not a fan of PB then?!

[privateeye]

oh yes I've worked on PB code written by VB developers... grrrrr, they shouldn't be let anywhere near it!

You're right tell them to get well away from VB - and don't come near PB either

[janey]

PowerBuilder 11 and 12 are going to be fully fledge .NET development tools, which means it's worth learning the .NET framework now to make the most of your PB experience.

I would love to, believe me!
it's getting your hands on the training which is the difficult bit! or the software to have a play with...

I know pb10 was trying to go in the web direction but I haven't been able to get my hands on a copy to have a play with

[DimPrawn]

PowerBuilder 11 and 12 are going to be fully fledge .NET development tools, which means it's worth learning the .NET framework now to make the most of your PB experience.

[janey]

Them were the days, I remember some poor guy spent days writing a bit of code for pagination in PB - he'd come from VB. He was so proud when he showed it to me, but was brought down to earth when I showed him the checkbox to do it all for him in less than one second.

oh yes I've worked on PB code written by VB developers... grrrrr, they shouldn't be let anywhere near it!

[privateeye]

Now the smart contractor would charge for 3 days to check that checkbox.

..and the not so smart would click the checkbox again to make sure and then wonder why it didn't work.