oracle insert with many bind variables over WAN is very slow

views:

672

answers:

+3 Q:

oracle insert with many bind variables over WAN is very slow

Hello,

we have problem with slow insert statement using 40 bind variables as columns values. It runs several seconds when running over WAN link and we were not able to nail down the problem, until we used network analyzer. Every single execution of this prepared query required exchanging over 120 packets between client and server to complete. What we can do to to execute it more efficiently?

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

We are using Delphi 6 with ODAC, we tried various versions of ODAC and Oracle client with no avail. On server side we tried both Oracle 10 and 11.

+1 A:

TNS is not designed to work well over WAN.

If it's possible, rewrite your application to use other network layer, like HTTP, which is more efficient.

You can do it using Oracle HTTP Server, for instance.

Quassnoi 2009-05-27 14:14:56

Even on a LAN, it would be nice to have some option to not go to the server for every individual bind, but do this in bulk when the query is executed. Of course, error handling semantics would be a bit different, but still ...

Thilo 2009-05-28 04:41:21

In fact, if every bind goes to the server, would not that completely negated the idea of a batched query (where the main performance improvement comes from eliminating server round-trips)?

Thilo 2009-05-28 04:43:00

Completely changing network stack is not acceptable. Because the inserted data originate from text files, we are considering to use SQL Loader instead. But we'd be much happier if we could improve performance without disruptive changes in code.

Juraj 2009-06-16 10:55:09

We actually ended up rewriting the insert routine using SQL*Loader (it badly needed improvements anyway) and now it works like charm.

Juraj 2010-04-23 12:32:45

Have you looked at External Tables? Replaces the need for SQL Loader Requires Oracle 9i or above though

Chris Gill 2009-08-24 14:01:51

But it is not possible to access external tables over network (only when using further complications like network filesystems/shares, remote desktop, etc.).

Juraj 2009-09-01 11:39:23

Can you move the data to the DB server over the WAN, then do the upload?

Chris Gill 2009-09-01 13:17:44

Yes, but how do you move the data? You need to configure remote desktop or file sharing first, both on server and every client. Did you ever work as sys/network administrator, do you know what such thing means? Just deploying sqlldr.exe + updated app to everyone is much easier for us, because there are almost no changes necessary on server and no additional configuration on clients.Actually we are lucky that only few users need this functionality and they already use remote desktop, so final solution can be postponed.

Juraj 2009-09-02 11:38:38

Dude - I have no idea what you architecture is, you haven't described it. I only asked a question - there's no need to go crazy.

Chris Gill 2009-09-02 14:02:22

I've a similar kind of problem but we're using http application and database is running on RAC environment.

when query is hard coded(with bind variables it's taking 19 minutes, but if the query is being executed with Bind variables it taking more than 45 mins.

please help me in fixing this issue

Steve

2009-09-04 13:46:39

ansaurus

tags:

views:

answers:

oracle insert with many bind variables over WAN is very slow

related questions