Optimising an Oracle application for bandwidth

Hi,

I have an application, written in VB, that uses the Microsoft OLEDB for Oracle drivers (and by extension, the Oracle supplied Net8 client) to communicate with an Oracle database. The application is used by 10-15 users who use it as their only interface to update the information held in the Oracle database.

Most users are on the same LAN as the database server, but I have one (soon to be two) remote users. These users dial up a local ISP access point, and connect into my network via PPTP, and from there, they run the VB which talks to the database. For this user, connected at 30,600bps, application response speed is a real problem.

Investigating this has shown that the main problem seems to be bandwidth, rather than latency - as some screens in the app refresh, a lot of SQL is generated, and the link is getting saturated for up to 10 secs or so, leading to pretty dismal performance. Now, some time ago this database used to live on MS SQL server. It was migrated to Oracle, and most things now run better, except this. On SQL server, the VB developer had many of the queries built as stored procedures in the database. In the process of migrating, as recommended by the Oracle documentation, he changed from this stored procedure model to generating select statements within the client application, and sending them across the wire. This has lowered performance over a modem link compared to the SQL server version.

Oracle recommends against the use of stored procedures for returning query results to a client application. With this in mind, is there any sensible course we can take to reduce the bandwidth requirements of the application in rder to make it more responsive on such a poor connection?

Please don't advise a connection upgrade - the user in question lives in a remote part of Ireland, where ISDN and ADSL are unavailable, and gets only 30kbps out of a v90 modem :-(. Smoke signals would be faster.....

TIA, Mike. Received on Tue Oct 30 2001 - 18:25:28 CST