3

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Juraj
  • 860
  • 4
  • 16

2 Answers2

1

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
  • 413,100
  • 91
  • 616
  • 614
  • 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 May 28 '09 at 04:41
  • 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 May 28 '09 at 04:43
  • 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 Jun 16 '09 at 10:55
  • We actually ended up rewriting the insert routine using SQL*Loader (it badly needed improvements anyway) and now it works like charm. – Juraj Apr 23 '10 at 12:32
0

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

Chris Gill
  • 2,848
  • 5
  • 26
  • 31
  • But it is not possible to access external tables over network (only when using further complications like network filesystems/shares, remote desktop, etc.). – Juraj Sep 01 '09 at 11:39
  • Can you move the data to the DB server over the WAN, then do the upload? – Chris Gill Sep 01 '09 at 13:17
  • 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 Sep 02 '09 at 11: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 Sep 02 '09 at 14:02