Hi, Co-incidentally, I bumped upon the fact that MySQL does actually support having Prepared Statements where the Table Name can be dynamically set.
Details are available at - http://dev.mysql.com/doc/refman/5.4/en/sql-syntax-prepared-statements.html This is really a useful feature for multiple use cases. The placeholder for dynamic Table Names will also help a lot in partitioning data into different tables for large volumes of data. The performance gains and ease of use will be pretty significant. This feature will definitely be really useful to solve a lot of real life complex problems as stated in my examples and by others in this forum as well. I am ready to help in developing/testing this feature. Regards, Vipul. On Jan 28, 1:50 pm, vips <[email protected]> wrote: > Hi Thomas, > > In the test runs that I ran, the performance difference was > significant between using a PreparedStatement and a Statement. > > With Prepared Statements, after doing about 100+ runs, the average > processing was - 7000 inserts /sec > With Statements, after doing about 100+ runs, the average processing > was - 4500 inserts /sec > There is a gain of almost 60% by using prepared statements. > > The SQL in question is - > Create table <TableName (?)> (<ColumnList>) as (select <ColumnList> > from CSVREAD('<FilePath (?)>', '<ColumnNameList>')) ; > > In the real scenario, the <TableName (?) > and <FilePath (?)> would > actually keep changing dynamically in each run. > But for this test I used the same TableName and FilePath for each run > and did not change them dynamically. > > Hence, having a placeholder in the PreparedStatement for dynamic > TableName and dynamic FilePath will help a lot. > > The placeholder for dynamic Table Names will also help a lot in > partitioning data into different tables for large volumes of data. > The performance gains and ease of use will be pretty significant. > > Regards, > Vipul. > > On Jan 20, 11:12 pm, Thomas Mueller <[email protected]> > wrote: > > > Hi, > > > > But adding this will surely be a very helpful and unique practical > > > feature for H2. > > > I don't think it's worth it. The performance gain you get from _not_ > > parsing the statement is minimal (or non-existent). I suggest to > > create the statement dynamically in this case. > > > 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.
