Loading Data into the RAM

[Zadock]

Hi All,

When loading data from a table whose database is sitting on a server onto a client machine using BUFFER COPY, it takes unnecessarily long. The table contains about 15000 records and it is taking approximately 3 minutes to load before my users can start using the system. Is there a faster way around this? The server in terms of RAM and other hardware requirements is perfect.

Regards

 

[Cringer]

Let me get this straight - you're caching a table into a temp-table on the client side?
Why are you doing this? Performance reasons? Other?
What Progress version?

 

[Zadock]

Hi Cringer,

Yes, Im caching a table into a temp-table on the client's side. What is the best approach? Im on Openedge V11.1.

Regards

 

[Cringer]

There's not much else you can do other than cache it with buffer copy, but my question of why you're doing this is quite important. It may be there is something better you can do which has less overhead, but solves the problem.
If you do go down the cache route then you'll want to ensure that your client start up parameters allocate enough memory for the temp table you are creating so it's not constantly flushing to disk.

 

[Zadock]

We are web enabling our application and the advice I got was that we should at all costs avoid direct contact with the database except when saving a record or deleting. This is why we are loading the contents into the RAM, then run our application. May be you need to shed light on this just incase there is something I got wrong.

 

[Cringer]

It completely depends what the part of the application is doing, but I really don't understand the necessity to cache 15000 records. If you're web-enabling then you'll be setting up some sort of AppServer I'd assume? The client screen should have no knowledge of the database, you then use the AppServer layer to get the info from the server layer and pass it back. I can't imagine a scenario where you would need 15k records though. If you really need that much then devise a way of batching it. The user won't be able to deal with 15k at a time anyway.
Another idea is to request JSON from the server. A Web Application should be able to handle the JSON...

 

[Cringer]

Without knowing what exactly you are trying to achieve it's tricky for anyone to assist though.

 

[Zadock]

Our application is an HR System. Our end users are spread all over the country but the server sits in one central place. Ideally, i want an end user to be able to access all records while sitting at another branch.

 

[Cringer]

Caching isn't the solution IMO. You've already hit one problem - the overheads. What happens if data changes? Users will be reading out of date information. You should be able to quite simply create a web front end that communicates with your database using AppServers etc that will be easy to use. There are plenty of folk on here with experience of designing this sort of system. Unfortunately not me. I'll therefore leave this thread for others to comment.

 

[RealHeavyDude]

Just out of curiosity: Who did advice you to avoid reading from the database at any cost? This generic advice does not make any sense to me ...

But then again, it is not really clear to me, what it is that you are trying to achieve.

You are mentioning that you are web-enabling your application - what exactly does that mean?
How do you access the Progress database - via an AppServer ( or WebSpeed Broker )?
Do your users really need the full set of 15'000 records to do their job?
Why do you think that serving a web page that gives your users access to those 15'000 records with a Progress Backend won't do so that you need to cache them on the client?
How are you accessing the cache from the WebBrowser?

Please don't get me wrong, but I think that whole approach needs an oversight.

Heavy Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

I agree with RealHeavyDude. Web-enabling an application may involve changing its architecture or using different clients than in the past (e.g. AppServer or WebSpeed agent, versus GUI or character client) but I don't know of anyone who advises "at all costs avoid direct contact with the database" for this scenario. (Or for any other scenario, for that matter.) There is no shortage of problems with this approach.

I'd say it's time to stop coding and testing and go back to the design stage. Or maybe even further back, to the requirements-gathering stage.

 

[RealHeavyDude]

Just to add to:

Running a fat client ( direct database connection ) across a WAN ( the internet ) will alway result in poor performance. The problem with such an architecture is loads of small network traffic ( roundtrips ) that a "classic" fat client produces. It is much better to have less roundtrips that transport more data than the other way round. That is where the AppServer comes into play. You would populate the cache on the AppServer and send it across the wire once instead of causing constant network traffic during the executing of your for each.

Heavy Regards, RealHeavyDude.

 

[Zadock]

Many thanks for your reply.

I must admit I am not well conversant with the aspect of web enabling my application. It is the first time I am trying it out and any advice/ideas to help me out in regards to accessing data is most welcomed.

I have an application server installed but I havent really tried accessing records through it. I will give this a try and see how it all goes.

 

[RealHeavyDude]

Some 10 years ago I was involved in a test with a Dynamics application - which you could run with the AppServer or with a direct database connection out-of-the-box. We tried both scenaries constantly reducing the network bandwith. I don't have access to the data anymore as I am working at a different company today - but here we go:

The client with the direct database connection was immediately affected when we reduced the network bandwith to 50MBit. At 25MBit it was almost unusable from a performance point of view. The same reduction in network bandwith did not yield any noticeable effect on the client running with the AppServer.

Heavy Regards, RealHeavyDude.

 

[Joe Meyer]

FWIW

We connect to our databases from web applications via ODBC. We ALWAYS have our application servers in the same city as our database server. We are also a multi site company but trying to have the application be driven from a different city is severely taxing on database performance.

Example:

If I have a client in City A and they need to get data from a database in City B. If I use an application (in this case a web server) which also resides in City A (essentially simulating the "fat client") that directly makes 7 database queries calls to the database in City B over a 50 MBit pipe (with 1-2 ms ping times, which is extremely low for interconnected cities and as these grow so do all your other times) the database query timing is approximately 90 ms and the processing time in total runs around 452 ms and the client web browser had fully received and rendered the page at the 460 ms mark.

Now lets take the flip side of that, we're going to have a client in City A and they still need to get data from a database in City B, but this time our application server is going to reside in City B and then only send the rendered page back to the client in City A after it's been generated. Running the same 7 queries and building the exact same page we end up with a page that has only 20 ms of database query time and 133 ms of processing time in total and the client web browser had fully received and rendered the page at the 140 ms mark.​

What you should be able to conclude from this is that making network calls to another server, especially one in a different city is one of the most expensive operations that you can do. It's by far cheaper to keep your database and application closer together and then serve up your final results over the network.

Another side note I'll rant about here is that since very frequently data is being sent over unencrypted lines, having fat clients is just asking for security trouble. Frequently you might be requesting data from the database which is later suppressed by the application, but with a Fat Client, all that data is being sent to the client so it's completely possible to glean that hidden information from a simple network packet sniffer.

 

[RealHeavyDude]

Joe, you can use SSL on the client/networking to connect to AppServers and Databases for a long time now. Since OE 11.4 ( supposedly - I haven't had a look at it since we are on OE 11.3 ) you can use SSL client certificates too to authenticate against your servers. That should reduce the risk of exposing data on the internet significantly. Of course you have an additional overhead with SSL - but I've yet to see a case where that made a big impact on performance.

Heavy Regards, RealHeavyDude.

 

[Joe Meyer]

Joe, you can use SSL on the client/networking to connect to AppServers and Databases for a long time now. Since OE 11.4 ( supposedly - I haven't had a look at it since we are on OE 11.3 ) you can use SSL client certificates too to authenticate against your servers. That should reduce the risk of exposing data on the internet significantly. Of course you have an additional overhead with SSL - but I've yet to see a case where that made a big impact on performance.

I'd agree that the internet risk is minimal and that the performance impact these days is so negligible there's not really a reason not to do it (other than maybe the minor pain of setting up a new certificate every X years). However, that still doesn't prevent the deviant computer science intern from going and grabbing a memory dump software (such as cheat engine or other) and logging things after they've been decrypted on their local machine and wading through the data to find the information they are after. My thought process on this sort of stuff has always been that if it hits the client machine, there's a good chance that someone can see it and any programming logic that occurs to suppress it is merely security by obscurity.

Take for instance a scenario where I pull back a list of account numbers, and their account balance. Then using the programming logic of my client I suppress the balance amounts for certain account numbers (be it based on some logical security or what have you). I would think that theoretically those actual balances were indeed in memory at one point in time on the client machine and therefore they probably could be gone after using the right tools.

Again the risk here is probably next to none and if you can't trust your employees then you probably shouldn't have hired them to begin with, on the other hand it might be a more valid security concern if all of a sudden you're in the banking industry or otherwise high risk data runs through your application.

 

[RealHeavyDude]

I am in the banking industry. Actually I do work for a big Swiss bank - which IMHO is the most paranoid of paranoid environments you might end up in ...

We have all sorts of security layers and controls in place to ensure that anybody is getting only data on a need-to-know/need-to-do basis - mostly cliend identifying data. This involves strong 2 factor authentication, location aware access control and late enrichment so that only data to which the user has been granted access ends up in the memory of any client device. Any client device - and that also includes corporate provisioned Windows PCs and notebooks located within corporate premises connected solely to the internal network - is considered to be insecure.

Nevertheless you will have employees like client advisors that must have access to such sensitive data. And here is where all technical steps you have taken to prevent data leakage will become obsolote as soon as your - maybe disgruntled - employees are not trustworthy. There will always be a human factor which can't be ruled out regardless how many highly sophisticated technial solution you employ.