This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

URGENT --Help needed --- Delay in executing SQL Query..

 
deepa karkala
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have a SQL query with 300 lines. When I execute it at SQL PLUS prompt, it takes only 4 seconds. The query is not fetching any row as of now.
The same query when I run from java program using Oracle thin driver, it is taking more than half an hour. It is a simple java program just executing this query.
I set the breakpoint in the debugger to prepstmt.executeQuery(); In the debugger, Once the control comes to this statement, it is taking more than half an hour to execute this statement. Offcourse the query is not fetching any row.
Please anyone can suggest me what would be the reason for this delay.
thanks in advance.
 
Rufus BugleWeed
Ranch Hand
Posts: 1551
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's the query, might help...
Sound like you should look into the Oracle performance tuning tools, or light a fire under your DBA.
 
deepa karkala
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry that Query is quite large and I can't paste it over here.
My concern is when I execute this large 300 lines query from sql prompt, it takes only few seconds.
But same query when I run from a simple java program, it is almost taking hanging. More over the sql does not fetch any record due to the where conditions. Somewhere in the internet when I searched for similar problem, there was a suggestion to turn on query logging on the server and then to execute the query once again from the sql prompt. But I don't know what does this "Query logging On" do. How to switch it on in the server ? Is it a parameter which I have to set in ini.ora file ?
What if the query still takes few seconds to run at the sql prompt even after turing on the query logging?
Thanks in advance.
 
deepa karkala
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I got the reason for delay. It is due to the date formatting from java field to the database field. I implemented the timestamp concept and now it is faster.
thank you.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"jyothi",
It appears that you have managed 14 posts without a warning to change your display name.

The JavaRanch naming rultes can be found here.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic