2

I am trying to run a few sql scripts in Oracle 11g R2

When I try to run these scripts from a standalone java code the query runs into 40 odd minutes but when I run the same from the sqldeveloper client it runs in 4 minutes

What could be the reason ?

EDIT

Well I am doing a few update scripts, calling a pl/sql procedure

Yes I am doing a string concatenation

Improve this question
5
  • 1
    What kind and how many statements are you executing? (SELECTs, INSERTs, UPDATEs) Commented May 4, 2011 at 7:18
  • What amount of data are you sending (for write statements) and what amount of rows are you retrieving. Commented May 4, 2011 at 7:19
  • 1
    Everything what you're not showing us: Your code. I suspect you're doing something fishy like string concatenations in a tight loop. Commented May 4, 2011 at 7:20
  • Just a shot in the dark: SQLDeveloper is ODBC and from java you work with JDBC. Commented May 4, 2011 at 8:12
  • 1
    @avid: SQLDeveloper (the one from Oracle) is a Java/JDBC based tool Commented May 4, 2011 at 12:08

1 Answer 1

Reset to default
1

To execute the sql from java, you perform the following steps:

  • open a connection (700ms)
  • prepare a statement (80ms)
  • execute the statement (10ms)

Timing examples are from my environment for a simple statement. Your environment will be different, but the proportional amount of time will be similar. The point is that opening the connection can be far more expensive than actually executing the statement.

Check that your code is not opening a connection and preparing a statement inside of a loop. That could account for a big proportion of your time and explain why the script is slower then when launched with sql developer. You should open the connection once and then re-use the connection. If you are not uploading a large amount of data, then the amount of time the code takes to run on the database server is the same whether it was initiated from sql developer or some other client.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.