Generic query result bulk loading operations

Issue #30 new
Igor Sereda created an issue

Originally reported on Google Code with ID 30

Right now there's SQLiteStatement.loadLongs() which is a fast way to transfer one column
of integer values from SQLite into Java. A generic method to transfer any type of data
and in multiple columns is needed.

Reported by sereda on 2011-06-29 12:36:01

Comments (9)

  1. Former user Account Deleted
    It turns out that modern JDK have quite efficient implementation of JNI calls. Our benchmarks
    showed only 20% gain from loadLongs() on Windows, Linux, OS X with JDK 1.6 - 1.8. Only
    Android platform has shown 7x gain.
    Considering large effort to design a generalization of loadLongs for arbitrary columns,
    we have decided to postpone the implementation of this feature.
    Since there are people who have starred this request, I'd like to ask for your feedback
    - is this really needed and what are your circumstances?
    Kind regards,

    Reported by on 2014-09-22 10:15:09 - Labels added: Priority-Low - Labels removed: Priority-Medium

  2. Former user Account Deleted
    I think it is perfectly reasonable to postpone this optimization.
    Le 22 sept. 2014 12:15, <> a écrit :

    Reported by dbouyssie on 2014-09-24 20:31:36

  3. Former user Account Deleted
    I am also interested in this feature. The question might sound stupid after the responses,
    but would be also many effort to make a version for other data types? For example,
    SQLiteStatement.loadDouble() or SQLiteStatement.loadString(). I think that covering
    most of the data types would be interesting. I am specifically interested in the double
    one, since I am creating a database with data collected from wearable devices.

    Reported by alejandrosaez3 on 2014-11-11 17:08:30

  4. Igor Sereda reporter
    Alejandro, do you need to just load multiple double values or do you need a speed-optimized
    method? In the former case, you can just iterate through statement results and retrieve
    the values with columnDouble().
    If you need an optimized method, let me ask you, what platform does your code run on?
    What JVM version do you have there?

    Reported by sereda on 2014-11-11 19:29:56

  5. Former user Account Deleted
    Currently I am interating through statement results and retrieving the values with columnDouble().
    I did a quick benchmark selecting 5 millions of rows and got a great improvement with
    the SQLiteStatement.loadInt() (in some cases even 50% faster!). Probably wasn´t the
    most reliable benchmark ever, but it was enough for me.The database will have eventually
    50 millions of rows or even more so that I am interesting in a speed-optimized method
    (any idea is welcome!). I am running the application on Windows but in the future will
    run on OSX and maybe on Linux. I am using the JVM 7.

    Reported by alejandrosaez3 on 2014-11-11 23:29:15

  6. Former user Account Deleted
    I forgor to say that you did a great job with the wrapper. As a developer, I know how
    good is when people recognise your work.

    Reported by alejandrosaez3 on 2014-11-11 23:35:14

  7. Igor Sereda reporter
    Alejandro, thanks for your kind words :) 
    At the time when loadLongs() method was introduced, the performance improvement was
    around 10x faster, so it was a big deal. We did benchmarks with the modern JVMs - the
    results are mentioned above, only 20% improvement, maybe even 50% improvement as you
    mentioned. In our opinion, this is not that bad. 
    We may still work on providing a generic method for bulk loading results, but given
    the complexity involved (and we're not going to just add loadDoubles() method, but
    rather load multiple columns of different types into a buffer), we won't be able to
    work on it in the near future.
    So the issue remains open, but low priority. Sorry about that! If you very much need
    loadDoubles() method, you can try adding it yourself, basically making a copy of the
    call chain under loadLongs() method. Please let us know if you succeed.
    Kind regards,

    Reported by sereda on 2014-11-12 07:29:15

  8. Former user Account Deleted
    I totally understand it. No worries! I don't think I will try it now, but if I will
    let you know if I try it in the future. Thank you for your response.

    Reported by alejandrosaez3 on 2014-11-13 08:52:00

  9. Log in to comment