Hi, > I need to implement a new query due to a new application requirement. > If i have a table with dozens of columns and thousands of rows, what > is the most efficient way to select only a few columns (say 10-20) for > a limited number of rows (say 5%)?
Maybe I don't understand the question, but you should use a statement of the form SELECT col1, col2, ... FROM ... WHERE ... If the same query needs to run multiple times, use a prepared statement. > a limited number of rows (say 5%)? Unless you want to select many thousand rows in one query, one statement is fine. If the result set is very large (many thousand rows) then it's faster (in H2 at least) to run multiple statements. > - retrieve each row with an individual select statement > - use "where recordid in (...)" to specify the rows > - create a view with those columns included, and: > - retrieve each row with an individual select statement > - use "where recordid in (...)" to specify the rows I don't see a big advantage or disadvantage. Everything is good. The IN(...) is optimized by default in version 1.1.x (but not in 1.0.x). > Which is likely to be the most efficient in H2? Is there any > performance penalty for creating views? There is a small penalty, but it's probably not measurable if you use prepared statements. > Since the field list is > likely to vary, is there any problem with creating and dropping these > views every time the application is opened? Is there a way to create > temporary views? Dropping views is not a problem. Temporary views are supported as well. Regards, Thomas --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
