Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes URGENT --Help needed  --- Delay in executing SQL Query.. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "URGENT --Help needed  --- Delay in executing SQL Query.." Watch "URGENT --Help needed  --- Delay in executing SQL Query.." New topic
Author

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

deepa karkala
Ranch Hand

Joined: Jan 29, 2003
Posts: 62
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

Joined: Feb 22, 2002
Posts: 1551
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

Joined: Jan 29, 2003
Posts: 62
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

Joined: Jan 29, 2003
Posts: 62
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

Joined: Mar 06, 2001
Posts: 13459

"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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: URGENT --Help needed --- Delay in executing SQL Query..