Thanks, trying is always the safest approach. The query you suggested seems to produce the best results.
I should have mentioned that I'm working in embedded mode and the fetch is up to about 200 records (usually in the area of 50). I did some testing with 50 records: the IN approach (my original one), the UNION ALL approach (the one you suggested) and fetching the items one at a time. * IN approach - about 50ms * UNION ALL approach - about 50ms * Separate queries - 85ms Doing real-world tests, as part of my running system, yields very different results each time. Still the UNION and IN approach are about the same (sometimes the UNION is quicker) while the separate queries usually lag behind, but not always. I'm guessing it also have something to do with the command compilation and caching. Thanks again for your answer. Zviki On Feb 17, 4:04 am, Johann Schleier-Smith <[email protected]> wrote: > Zviki, > > One thing you can try is to use the union all clause > > e.g., > > SELECT ... FROM TABLE WHERE ID=? > UNION ALL > SELECT ... FROM TABLE WHERE ID=? > UNION ALL > SELECT ... FROM TABLE WHERE ID=? > . > . > . > > You can also prepare the statement and then bind parameters and > re-execute it repeatedly, which should be pretty fast if you're > running an embedded database, but will cause extra round trips in > server mode. > > Which works fastest in this situation often depends on the details of > the application, so if you really want to maximize speed you'll have > to test the alternatives. > > - Johann > > On Mon, Feb 16, 2009 at 6:58 AM, [email protected] <[email protected]> wrote: > > > Hi, > > > I have a list of IDs which I need to fetch from the database. I > > currently use a query of the form: > > > SELECT * FROM TABLE WHERE ID IN (?,?,?,?,?,....) > > > I construct the number of parameters dynamically from Java and assign > > the IDs when executing this prepared statement. > > > However, this technique does not maintain the order. I can, of course, > > sort the rows after fetching. However, for the sake of performance, I > > would prefer a better solution. > > > One possible approach is to fetch each row individually. I'm not sure > > if this is better. Any help would be appreciated. > > > Thanks, > > Zviki --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
