Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: JAVA performance boost?

    Recently I read this Retrieve large ResultSet - Real's Java How-to and I decided to try this on DB2 to see the effects.
    Strangely the program works flawless but I cannot measure any effects when I play around with the value of the "setFetchSize" parameter (DB2 ignores this?).

    Can anyone confirm my findings, and even better, explain them? As far as I know DB2 supports "multi row fetching" and this should give a performance boost.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The question is what you have measured and where the performance bottle-neck lies. If you have a CPU-intensive query (and the transfer of the query results is rather marginal), using multi-row fetch won't have much impact. Also, if your application is doing lots of processing for each row and a lot of time is spent there, reducing fetch overhead may not be noticeable either.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    I just adjusted the example program in the link (my java skills hardly exceeds "hello world"). I've tested on local and remote databases. My impression is that DB2 (or the JDBC driver) just ignores this parameter.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I probably only works on ambiguous cursors.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How much data did you fetch? Measuring differences in milli-seconds is pretty much useless, especially with Java applications. You'd have to make sure that caching effects and swapping don't screw up the measurements. Or you re-run the timings several times to average-out such effects.

    In any case, I browsed a bit through the DB2 manual, and setFetchSize() is supported. But it is a hint to the JDBC driver only. The method itself is supported since DB2 V9.5, FP3. (Driver support for JDBC APIs) Do you run this or a newer version?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Ah, there's the catch. I tested on my laptop which has the right version, but not al lot of data (employee on sample). I also tested at the office: there I have loads of data, but a JDBC driver version 8.
    Is it worth while to download and install the newest JDBC drivers on my XP workstation and re-test against a aix V8.2 database? Or has the database have to be V9.5 as well?

    Knut, I am re-reading your reply: a piece of software taking hints? Is that because you're sick&tired of the "it depends" phrase?

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No, it is not an "it depends" thing. A hint is a valid vehicle to transfer information from a client application to the server in case the application knows more details about the nature of the data than the server. Another reason could be - like in the case of Oracle - that the DBMS makes not always the right decisions for an access plan. So a hint can point the optimizer into the right direction. For example, if you add a predicate like "1=1 OR ..." to your WHERE clause sent to DB2 z/OS, you influence the decision to use or not to use an index scan. That is a hint as well. In fact, I'd say that most programs take hints in some form or another.

    As for the version question: you don't really want to compare the behavior of DB2 V8.2 with DB2 V9.5? If you want to test something like that, I would use V9.5 FP3 (or later) and a sufficiently sized data volume. Also, the server has to support this kind of block fetching as well as the client. Otherwise, the client would request it and if the server doesn't support it, you won't get the benefits.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •