[DB-SIG] RE: mxODBC performance question

[M.-A. Lemburg]

David Bolen wrote:
> 
> I was browsing the Windows module from mxODBC, and noticed that the
> execute() command always does a SQLPrepare()+SQLExecute() approach, and
> never an SQLExecDirect().  Of course, the prepare+execute makes sense in
> order to cover the case of parameters, but I think that in my case (one shot
> commands without parameters) I'm probably seeing my performance hit by the
> multiple network access.  ODBC performance over a WAN seems to be quite poor
> to start with (high overhead per request) so doubling the requests
> (unnecessarily in my case) is a bad thing :-)  This would also explain why I
> didn't see as much of an issue when operating over a LAN during testing.
> 
> While I wasn't able to track down sources to the exact Perl module we're
> using (our now-gone Perl guy must have chosen some intermediate build) I
> found releases just before and after and verified that the source in both
> cases is using SQLExecDirect().  So if the per-request overhead of the WAN
> ODBC setup is a large fraction of my runtime, it would explain a good part
> of the 2:1 ratio I was getting compared to the Perl script.

This could be one of the reasons. mxODBC is designed and enhanced
to make good use of bound parameters.

SQLExecDirect() is currently not used, but would be a good candidate
for the case where you don't have any parameters and keeping the
prepared command around is not important. I'll add that to my TODO
list.

> I've seen some comments in the mailing list archives about permitting direct
> access to the prepare/execute sequence, but haven't seen anything about
> access to a non-prepared approach.  It would seem to me that if I know that
> I won't be using any parameters, then preparing the statement is wasteful.
> But I'm not sure the module itself could determine this (is scanning for the
> parameter character code in the statement enough?), so permitting some level
> of application control over this might be helpful.
> 
> Of course, it may well be that the lack of a prepare+parameters approach in
> the Perl module is what led to the current full-string individual command
> implementation, in which case I should be able to restructure things
> post-transition to be more efficient.  But the current approach of preparing
> everything makes that transition difficult.

You should definitely consider using bound parameters, since
these fit your problem quite nicely (fixed SQL statement + 
variable query values).

-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/