I think that you need to trace the entire "conversation" between Oracle and
the VB application. It may be that the MS OLEDB driver is doing a lot of
"talking" in addition to actually retrieving the data. One quick and dirty
way to measure if this might be the case would be to run sqlplus on the
client. spool the output to a file and not view it on the screen. Compare
this time to execute with the same queries in a specific area of the VB
application. (If possible I would do this on the end of the modem
connection to exacerbate the performance issue.) Also I have seen a tendency
for people to connect and disconnect in VB and that could lead to a large
time sink.
Just a couple of ideas.
Jim
"Mike Brodbelt" <mike@_nospam_coruscant.demon.co.uk> wrote in message
news:9yHD7.87595$uM2.10613013_at_monolith.news.easynet.net...
> 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:52:34 CST
