Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

set fetch size

 
bahri sirine
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Im trying to set fetchSize on statment, while executing query so that can improve performance of some long running query. But the total execution time increase when I make a higher fetch size. . Is there any different configuration (along with opening connection, like setting up some specific properties etc.) .Any help please

 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not sure if my thinking is correct. Should the fetch size be set for the statement or result set or both?

Since you done it for the statement, do you have 1000 rows in the result set (eg loop 1000 times)?

Also is it necessary to use/create a new connection between each fetch?

Another approach is do it using the pure sql similar to limit x,y in MySQL but you using Oracle so rowid is needed.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
K. Tsang wrote:Not sure if my thinking is correct. Should the fetch size be set for the statement or result set or both?

Using it on the statement is enough. You can call setFetchSize() on the result set object to override the statement object fetch size that was passed to it.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
bahri sirine wrote:Is there any different configuration (along with opening connection, like setting up some specific properties etc.).

As you are using Oracle, you could try setting the defaultRowPrefetch property of OracleConnection (and/or the rowPrefetch property of OracleStatement). Here you can find more info.

Additional note: If you are using a JDBC 4.0 driver, you don't need to load the driver yourself anymore usingThe driver will be registered automatically. You'll find more info in Oracle's JDBC tutorial.

And do you experience the same poor performance if you execute this query in a database client like Squirrel?
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are timing how long it takes to get the first record. So it's maybe not that surprising that using a larger fetch size increases that time, since you're telling the driver to bring over more records before giving you the first one.

But did you really mean to time getting only the first record, or should that "t.end()" statement really be outside the loop, so that you time reading the entire result set? Or do you really only need the one record? If that's the case then you should try to write a query which only returns that one record -- and the fetch size is going to be irrelevant.

EDIT: Sorry... the horrible indentation of that code misled me completely. You are actually timing reading the whole result set. So disregard everything I said here, except for the comment about the confusing indentation. Fixing that wouldn't help the performance of the code but it would help people to understand it.
 
bahri sirine
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I'm used OJDBC6 and also I'm obtained the same result when I change the defaultRowPrefetch.Any idea please
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And you are absolutely sure it's not a network issue? Do you experience the same poor performance if you execute this query in a database client like Squirrel?
 
bahri sirine
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Really I don't know squirrel. there isn't an error in the code for the execution time ?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
bahri sirine wrote:Really I don't know squirrel.

Any other db client will do as well.

bahri sirine wrote:there isn't an error in the code for the execution time ?

How much time does it take to return 1 row from the database? For example:
 
bahri sirine
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
when I execute the code with all idobject I botained 240ms I think there exist an eroor in the time execution because there are millions of data . So I think that maybe the code found just the time execution of 1 id . Sure that there isn't any error in the code
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tweaked your code a little bit:
  • improved indentation -> better readibility
  • added a counter to count the number of processed records
  • print duration in milliseconds

  • Here's the code:Now you can change the query as you like and see how much time is required to process/read the records. Now you can execute a given query and then make a change (e.g. increasing the fetch size) and see its influence on the duration. And then you can share these results here. I would first start with a query returning just 1 record. Then try with a record returning 100 records. And then another go with 1000 or even 10K records.
     
    bahri sirine
    Ranch Hand
    Posts: 33
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    And If I want to know just the execution time of the query so I must eliminate the while (rs.next()) {
    System.out.println(rs.getDouble("objectid"));
    }
    So when I can make the count++ know?
    and what is the difference from 2 codes?
    thanks
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    bahri sirine wrote:And If I want to know just the execution time of the query so I must eliminate the while (rs.next()) {
    System.out.println(rs.getDouble("objectid"));
    }

    No, you can simply add these 2 extra lines of code just before the while loop

    bahri sirine wrote:and what is the difference from 2 codes?

    I explained in my previous post. Another difference I forgot to mention: the code I posted uses the default fetch size.
     
    bahri sirine
    Ranch Hand
    Posts: 33
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    So to know just the execution time without display the results I must to do this ?
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    bahri sirine wrote:So to know just the execution time without display the results I must to do this ?

    Yes! (If you correct your typo in the print-statement, remember Java is case-sensitive: system is not the same as System)

    PS. If you post code snippets, please UseCodeTags (for better readibility). I added them for you this time
     
    Dave Tolls
    Ranch Hand
    Posts: 2095
    15
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    bahri sirine wrote:And If I want to know just the execution time of the query ..
    thanks


    Then you use a tool (like Squirrel, or SLQL Developer, or even SQL Plus) to time the query itself without all the other fluff around your own code.
    If that timing seems too long then that's when you look at the execution plan, via the same tool.
    You are, after all, checking the query.

    I will ask why you are reading a million rows into your Java app...
     
    bahri sirine
    Ranch Hand
    Posts: 33
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    It's the objective of the professeut to read million rows using jdbc and oracle ..
     
    Dave Tolls
    Ranch Hand
    Posts: 2095
    15
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    bahri sirine wrote:It's the objective of the professeut to read million rows using jdbc and oracle ..


    OK, so it's a test rather than a practical requirement.

    Is that the real SQL query you are using?
    If so then there's not much you can do with it to speed things up.
    Why do you think things are slow?

    As for the fetch size (IIRC) Oracle recommends not bothering to change the base size for most situations.

    How long does it take to read the full result set?
     
    Roel De Nijs
    Sheriff
    Posts: 10662
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Roel De Nijs wrote:I would first start with a query returning just 1 record. Then try with a record returning 100 records. And then another go with 1000 or even 10K records.

    Still waiting for these results... The numbers tell the tale.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic