Re: [Sqlrelay-discussion] (no subject)

[David M. <dav...@fi...>]

Roger,

See responses below.

David Muse
dav...@fi...

b-r...@ro... wrote:
> 	(This is a resend, as the original garnered no responses.)
>
> 	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?
>   
No, it allows multiple connections to SQL Relay.

There was originally a limit, but it was fixed a long time ago.  What
doc says that only 1 connection will go at a time?  Let me know and I'll
fix it.
> 2) If so, doesn't that negate the connection pooling benefits of SQLRelay?
>
> 3) If using the Perl DBI API (DBD::SQLRelay), how does the connection
> performance to SQLRelay compare to connecting directly to the database?
>   
It will be slower because of the extra layer.  I don't have stats on it,
but I'd imagine that it would be small, but measurable.
> 4) Is there a significant performance difference between using the native
> API versus the Perl DBI API? What would that be?
>   
The difference between DBI and native perl should be small, but measurable.
> 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?
>   
For the best performance, use the native API.  DBI is easier to use
though.  The performance difference should be small.
> 	Thanks,
>
> R
>
> ------------------------------------------------------------------------------
> Enter the BlackBerry Developer Challenge  
> This is your chance to win up to $100,000 in prizes! For a limited time, 
> vendors submitting new applications to BlackBerry App World(TM) will have
> the opportunity to enter the BlackBerry Developer Challenge. See full prize  
> details at: http://p.sf.net/sfu/Challenge
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com
>