[Sqlrelay-discussion] [sqlrelay] Perl API versus Perl DBI API
Brought to you by:
mused
|
From: <b-r...@ro...> - 2009-06-15 19:26:39
|
Some initial tests with the Perl API indicated that we got a performance hit of 50% or more by using the DBI directly. I'm not particularly worried by this, as the connection pooling is a good trade-off, and we will realize a considerable performance gain by converting our existing applications to use MySQL rather than CSV. I really like the Perl DBI API as, other than the initial dbi->connect(), everything else just works. At the moment, that's what I'm using. Here is what I have done: I set up a 'low level' library with basic routines to access the database tables - delete, insert, select, update. Each routine does it's own dbi->connect(). There are no worries about looping through millions of inserts to load a table - the rows get inserted on an as-needed basis, not in bulk. The application scripts never call these routines directly. Next, I set up intermediary libraries that set up the various SQL statements used, so they get formatted the way the applications require. The application scripts call these libraries. One of the reasons for using SQLRelay is to avoid setting up and breaking down the database connections, but since I'm using the Perl DBI API, I don't think that is happening, because I still have to do a dbi->connect(), even to get to SQLRelay. When I looked at the documentation for the Perl API (native), it indicated that only session can run at a time. My questions: 1) Does the native API (Perl API) only allow a single connection to SQLRelay at a time? 2) If so, doesn't that negate the connection pooling benefits of SQLRelay? 3) If using the Perl DBI API, how does the connection performance to SQLRelay compare to connecting directly to the database? 4) Is there a significant performance difference between using the native API versus the Perl DBI API? What would that be? 5) I only want to send basic SQL statements to be executed, with only MySQL built-in functions (basic delete, insert, select, and update). Which API is recommended - native or Perl DBI API? Thanks, Roger |