'Paging' search results

[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

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

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]

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]

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.

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

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

[scotspine]

are you suggesting passing in tablenames?

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

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.