Re: improve performance thru oracle odbc driver

screwbai_at_my-deja.com wrote in message <80fh70$ic4$1_at_nnrp1.deja.com>...
>I have a borland client application using an oracle odbc driver against
>oracle 7.3.4 over a WAN. It's performance is not good.

The best thing IMHO to do when hitting network performance is to sniff the network traffic of the problem application. If you do not know what's happening at that level, all performance tuning tips and hints are mere speculation.

But let me speculate anyway.. :-)

First - the protocol and API can make a big difference. We've had some bad network performance experiences with named pipes for example. It seemed like there was a lot of polling happening between the client and the server (frames being transmitted that did not containing any client requests or server responses to client requests). The number of frames dropped substantially when we switched over to TCP sockets. But then this could have been a quirk with SQL-Server.. Or Visual Basic.. Who knows what happens in the dark recesses of your computer systems and networks when using these two? <sigh>

Secondly - what is being pushed down that network pipe? Let's say that the client app needs to insert, update and select tables with lots of columns. A standard SQL query can become substantial in size and span multiple packets. If you put that same SQL into a stored procedure on the database, the client application simply needs to call the stored procedure with parameters to pass the applicable values across. This can cut down the traffic that is generated by the client to up to 80%, if not more.

Another alternative is to make sure that the client only requests data that is needed. And requests data in a meaningful manner. Let's say the application needs to get the current Oracle username from Oracle. This can be done once up front when the application initialises (as it should). Or, it can be done whenever the application needs to display the Oracle username on a window (bad programming/design).

I find that the "standard programmer" (for a lack of a better word) seldom understand the complexities, power and flexibility of SQL. So they abuse it a lot. Worse, as a result they also abuse the network and the Oracle server by placing unnecessary overheads on them. And the substantial penalties that needs to be paid on a WAN for this mediocre programming never occurs to them. Which is why you should rather employ Real Programmers (tm) - if you're lucky enough to find one or two of them... ;-)

Lastly, the ODBC driver. Some Oracle ODBC driver allow you to configure the "default packet size" with a setting like "number of rows" or "max row size" or something like that. Usually a wording that is totally confusing. Anyway, by upping this parameter you are actually increasing the TCP packet size. This can also be a major difference in the number of frames send. The ideal packet size I believe is about 1.4 KB. The best is to experiment with this setting and find the highest value that does not cause the ODBC driver/SQL*Net to crash'n burn.. :-)

regards,
Billy Received on Mon Nov 15 1999 - 04:24:29 CST