On Sun, Apr 1, 2012 at 10:19 AM, Christian MICHON <[email protected]> wrote: > On Sun, Apr 1, 2012 at 10:12 AM, Christian MICHON > <[email protected]> wrote: >> On Sun, Apr 1, 2012 at 12:59 AM, Jeremy Evans <[email protected]> wrote: >>> On Saturday, March 31, 2012 2:51:37 PM UTC-7, Christian MICHON wrote: >>>> >>>> On Sat, Mar 31, 2012 at 11:27 PM, Christian MICHON >>>> <[email protected]> wrote: >>>> > Hi, >>>> > >>>> > I've been trying to optimize repetitive insertions into one of my H2 >>>> > database, using jruby + sequel. >>>> > >>>> > One recently found option was to use prepared statements in H2. As I >>>> > did not know how to do so with sequel, I went down to the lowest jdbc >>>> > API possible and managed to make it work yesterday. >>>> > >>>> > I gained a lost in terms of speed of execution in production, as >>>> > expected, but at the same time somehow I lost all the nice DSL from >>>> > sequel. I am willing to compromise some of this speed if I can get it >>>> > coded using sequel. >>>> > >>>> > I posted a small jruby snippet at http://pastie.org/3705530. This is >>>> > not my production code, but a simpler testcase. It just creates a H2 >>>> > db, add few records using std statement, and then a prepared statement >>>> > to delete based on id parameter. How would this be coded using pure >>>> > sequel ? >>>> > >>>> > I've been trying to read >>>> > >>>> > http://sequel.rubyforge.org/rdoc/files/doc/prepared_statements_rdoc.html, >>>> > but could not understand how this would work, especially in my >>>> > production case where one of my prepared statement has ~30+ values to >>>> > be inserted into a table. Please note that the small pastie does not >>>> > reflect that (just 1 input parameter). >>>> > >>>> > I will try in parallel more experiments (like naming all my 30+ >>>> > values, which is not my preference...). >>>> > >>>> >>>> Here is what I did using sequel: http://pastie.org/3705667 >>>> >>>> I see few drawbacks to this approach: >>>> - I need to name my parameters (I cannot just give a sequence like an >>>> array of parameters like I have in my production code) >>>> - I must give a hash when calling the prepared statement. In my >>>> previous jdbc pure api code, I would set all parameters one by one >>>> (and I can use an array iterator) and then execute the prepared >>>> statement. >>>> >>>> Is this the only way to do prepared statements in sequel? >>> >>> Well, that's the supported database-independent way. If you want more speed >>> than that and don't mind being dependent on JDBC, you can just use >>> Database#synchronize to get the JDBC connection object, and then use your >>> original code (conn in your pastie is the object yielded by >>> Database#synchronize). >>> >>> Jeremy >>> >> >> Sounds good: I do not mind being dependent on JDBC. It would allow me >> to keep 95% of my production code as is and only tweak 5% of my >> repetitive statements. >> >> Yet, if my database is configured with DB=Sequel.connect(...), then >> DB#synchronize does not have the prepareStatement java method. >> >> Am I doing something wrong here? Will perform more experiments later in the >> day. >> > > yes, I was doing something wrong. I need to do this instead: > > conn = DB.synchronize {|co| co} >
Cool. I changed only 5 lines over 80 in 1 source file of my production code, and one of my simplest testcases went from 82sec runtime down to 33sec. I have now similar performance as pure jdbc, keeping sequel most of the time in my code. Nice! Thanks again Jeremy! -- Christian -- You received this message because you are subscribed to the Google Groups "sequel-talk" 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/sequel-talk?hl=en.
