All geek questions in one place

Oracle insert with many WAN binding variables is very slow

We have a problem with a slow insert operation using 40 bind variables as column values. It works for several seconds when working on a WAN link, and we could not solve the problem until we used a network analyzer. For each individual execution of this prepared request, an exchange of more than 120 packets between the client and server is required to complete. What can we do to make it more efficient?

When I start the same insert with the actual parameters (without binding variables) from the same host, it ends in tens of milliseconds. There is nothing special about the parameters, there are only short varchars and numbers.

We use Delphi 6 with ODAC, we tried different versions of ODAC and the Oracle client to no avail. On the server side, we tried both Oracle 10 and 11.

+3
source share
2 answers

TNSnot designed to work more WAN.

If possible, rewrite the application to use a different network layer, for example HTTP, which is more efficient.

You can do this using Oracle HTTP Server, for example.

+1
source

Have you looked at the external tables ? Replaces the need for SQL Loader Requires Oracle 9i or higher, although

0
source

Source: https://habr.com/ru/post/1709198/

More articles:


All Articles