• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

preparedStatement.executeQuery() not returning , not even showing any error

 
ksoft joy
Greenhorn
Posts: 6
MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

We are facing a very weird issue, we are trying to execute a query using preparedStatement.executeQuery() method. This query when run on TOAD takes about 5 minutes to return initial results and the query returns a total of 30,000 rows in whole.

But when we run the same query from Java , executeQuery() method nothing is happening, we waited for almost a full day but still not getting any response or any error, if we change the same query by adding some more filters so that result contains less rows says 10 then executeQuery Method returns successfully.

We tried almost all ways but not able to figure out what exactly is causing the issue, it would be really helpful if some one can give a solution or even what may be causing the issue.

Thanks
 
Nikhil Pasupukuntla
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you could post your code that would be helpful.
 
ksoft joy
Greenhorn
Posts: 6
MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nikhil code is just a preparedStatement.executeQuery() and the statement contains say select * from emp , but if emp contains say 30k rows and 10 columns. is there any limit to the no of rows that can be returned with this method
 
Campbell Ritchie
Sheriff
Posts: 51348
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you really have a select * from table which takes 5 minutes to run? I can imagine it taking 5 minutes to display its results, but not to run the query.
 
Sai Hegde
security forum advocate
Ranch Hand
Posts: 212
1
Android Flex Google App Engine
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No user would want to see all the 30000 results in one go. Have a business parameter 'Max number of rows to show' defined that would help ramp up your response times.

we waited for almost a full day but still not getting any response


Your request must have already timed out by then...
 
ksoft joy
Greenhorn
Posts: 6
MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * from emp is just an example.This is a standalone java class which i use to get all rows of a query to verify something , its not about a web app. Yah i also thought it would have timed out but then i should get an error right. Is it possible to fetch such huge no of rows using a single executeQuery
 
Sai Hegde
security forum advocate
Ranch Hand
Posts: 212
1
Android Flex Google App Engine
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okie, sure you can have a single query return the number of rows mentioned. Since you said the query took about 5 mins to return the results on TOAD as well, you might want to performance tune your query. Have your tables been indexed???
 
Campbell Ritchie
Sheriff
Posts: 51348
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does a “select *” even require an index?
Doesn't it show how long the query took to execute, when it prints out?
 
Sai Hegde
security forum advocate
Ranch Hand
Posts: 212
1
Android Flex Google App Engine
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just a 'Select *' wont. But it is difficult to believe that returning 30k rows required 5 minutes.
 
ksoft joy
Greenhorn
Posts: 6
MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you all in trying to help me. My query contains many joins with sevaral tables. The point i am trying to understand is in Toad execution time is 5 min the same query through java why is it taking long time actually the method never returns even no error. We can fine tune the query but i want to understand what is causing the diff b/w toad and java
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please provide your code. It is likely returning an error or results. You probably just aren't displaying the error. Either way, there are JDBC connection string parameters that will impact performance in certain circumstances. If the query is behaving differently in JDBC vs Toad, it's probably because how the JDBC connection string is written. You should consult the documentation for the JDBC driver you are using and see if there are additional items you can add that will affect your query.

BTW returning 30,000 results to the user is... bad... taking 5 minutes to do so is even worse. You should consider pagination to split up the results and indexes to improve performance and/or materialized views.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sai Hegde wrote:Just a 'Select *' wont. But it is difficult to believe that returning 30k rows required 5 minutes.

It depends on the nature of the query completely. The number of returned rows has very little to do with the total complexity of the query. ksoft said that this is not a user interface thing. It is not uncommon for some background jobs to execute optimal queries taking tens of minutes or maybe a few hours in some backoffice production systems.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:BTW returning 30,000 results to the user is... bad... taking 5 minutes to do so is even worse. You should consider pagination to split up the results and indexes to improve performance and/or materialized views.

According to the original poster, the data are recovered to perform some operation on them (verification), that does not sound as a user related thing. In this case, pagination certainly won't help. Materialized views could, or not, it completely depends.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your saying someone can manually read 30,000 records and make an assessment based on them on the fly? Not likely unless they have really good eyes. If they are validating it they are probably putting into a CSV or some other offline format to parse. I suggest generating the file/data while the user isn't waiting and offering them 'the most recent version' of the file when they request it.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ksoft, which database are you using? In some databases there are diagnostics tools you can use to see what's going on (eg. SQLTrace in Oracle). If your database has such tools, it might be best to utilize them. Oracle's JDBC driver has some tracing capabilities too. I can give a few pointers for Oracle, if you want. If you're not on Oracle, I can't help, but you should consult your database and driver documentation for these tracing tools.

It is generally certainly possible to fetch thousands of rows. In the system we're developing there is an optimization process that is fed from a database. We read tens of thousands of records in total from several tables (and sometimes write tens of thousands records back) without any difficulty, in reasonable times.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott, are you reacting to my post? ksoft has originally written "to verify something"; I've understand this to be an automatic process, no humans involved. I might be wrong, if it is the case, you'd be right, of course.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, it's the idea of transferring 30,000 records from a database. This feels like the kind of thing a stored procedure could solve and eliminate the need to transfer that much data from the database.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:Actually, it's the idea of transferring 30,000 records from a database. This feels like the kind of thing a stored procedure could solve and eliminate the need to transfer that much data from the database.

Yes, I agree completely. Good point.

On the other hand, JDBC should be able to deliver a few thousand records from the database without any problem, that's beyond any discussion. That it doesn't means that something is wrong, and I'd say it is a good idea to find out what's wrong; even if the logic would be moved to the database eventually. Otherwise the problem might reappear in different circumstances anytime.
 
ksoft joy
Greenhorn
Posts: 6
MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We are using Oracle database and in my code i am verifying database records with a CSV file through code. There may be diff ways of doing this , i want to understand why same query behaving diff in java and toad? Is there a way to trace what is happeing inside executeQuery likd what is the query generated after setting parameters etc .Thanks so much all for your replies
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It might be a good idea if you post your SQL query along with the code you use to run it. Somehing might be revealed even this way. Especially make sure you're not doing any implicit conversions (text to date, text to number or vice versa, etc). If TOAD's locale settings (it's called NLS in Oracle - national language settings) differ from Java's, these conversions could turn up differently and you'd be actually unknowingly executing a different query.

Secondly, see this document about JDBC driver tracing capability. I don't have actual experiences with it, but if you understand Java more than Oracle, it might be easier for you to set this up and something might pop out too. You might try out different driver versions too.

If this fails, then it's time to turn to database SQL tracing. Can you secure ALTER SESSION privilege for your database user? If so, run the following statement immediately before your problematic query (if not, we'll discuss other possibilities):This will turn on tracing in the database. A .trc file will be created on the database server. Location of this file is specified by the USER_DUMP_DEST system parameter, your DBA should be able to find it. If you're your own DBA , your best bet is to search for *.trc file with recent modification date (I've changed the USER_DUMP_DEST parameter on my system and don't remember the default location, so I cannot offer reliable hint).

Interpreting the TRC file is not straightforward. But you should at least see the text of your SQL statement as it was processed by the database and if any errors executing it were encountered, they should appear there. Post back if you need more help interpreting it. A TKPROF utility can be used to convert this file into a more terse and meaningful format, we could use it to see how many records were fetched as a result of your query, for example.

Another way would be monitoring what your session is doing in the database (basically whether it is still alive and whether it processes any query). Cannot offer any help here, though, I do that seldom and always look up the procedure again in documentation/on the web whenever I need it. Try googling for monitoring long running operations in Oracle.
 
ksoft joy
Greenhorn
Posts: 6
MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry all, i was actually investigating the issue even deeper before posting any updates. Seems it is not the no of rows which is causing the problem , but when i set some values it is not behaving as exepcted it seems, not able to understand the exact issue but looking into it.

Select beginyear, name, wcode from table1 where beginyear = ? name = ? wcode = ? , ( this is a modified version of the query i am using, actually it is a very big query )

in Program i am using

String query = "Select beginyear, name, wcode from table1 where beginyear = ? name = ? wcode = ? ";

String year = "2009";
int i = 1;
int j =1;
int beginyear = Integer.parseInt(year);
String name = "joy"
String wcode =i+""+j;

and setting the parameters as

preparedStatement.setInt(beginyear);
preparedStatement.setString(name);
preparedStatement.setString(wcode);
preparedStatement.executeQuery();

This query is not returning or not giving any error.

But if i didnot set the parameters through setter Methods , and set parameters in the query String itself
like
String query = "Select beginyear, name, wcode from table1 where beginyear = 2009 name = 'joy' wcode = '11' ";
then call executeQuery , it is retrieving result. It seems when i set though setter methods some thing else is happening and since the table is very huge and it doesnot contain such values it is taking time.
I didnot get a chance to look at the tracing thing, but i will check that also to see what is hapening in the bacjground.
If any one have any thougts or faced similar issues please reply....

Also i dont have the ALTER SESSION pevilage as of now and it may take some time to get it :-(

Will that conversions causing the issue here ?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You say you've posted a modified version of your query -- does this query result in the same behaviour? I pretty much doubt it, as the statement is syntactically incorrect (see the missing ANDs?). You need to reproduce the code that actually exhibits the behaviour you're describing (see also PostRealCode). There is no point for anyone to ponder over code that might - or might not - cause the problems you're facing.

Replacing binds with literals might lead to different execution plans, and it is possible that one of these plans would use index and the other full scan, resulting in wildly different execution times. However, based on the information you've posted so far, it is impossible to tell.
 
NitishK Kumar
Ranch Hand
Posts: 40
Chrome Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, regarding TOAD I cannot say anything but when you are retrieving such a large amount of data from the database you must set the resultset fetchsize, which is by default is set to 10. Please refer java.sql.ResultSet
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
NitishK Kumar wrote:... but when you are retrieving such a large amount of data from the database you must set the resultset fetchsize, which is by default is set to 10. Please refer the following link http://download.oracle.com/javase/6/docs/api/index.html.

That is not true. The fetch size does not in any way limit the total number of records that can be retrieved from a resultset.

Fetch size says how many records will be loaded from a database at once, saving network roundtrips and generally speeding things up. The default value might not offer optimal performance, but otherwise is perfectly ok. On the contrary, setting fetch size too high might cause problems (trying to fetch many thousands of records at once, for example).

Moreover, the time it takes for the first record to be fetched will not be affected very much by fetch size. If anything, it will take more time for the first record to be read by the client code for higher fetch sizes, as the database query has to produce more rows and all those rows have to be transferred via the network before they will be available in the resultset. Fetch sizes higher than the default could generally shorten the total time it takes to load lots of records, though.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic