[DB-SIG] RE: mxODBC performance question

[David Bolen]

Probably bad form immediately following up on my own question, but I think I
may have a handle on at least a significant contributor to my problem.  In
my last note I mentioned:

"So from what I can see the only real difference is the Perl ODBC module
versus mxODBC on the Python side, along with whatever translation needs to
be performed between the host script environment and the ODBC interface."

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.

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.

-- David

/-----------------------------------------------------------------------\
 \               David Bolen            \   E-mail: db3l@fitlinxx.com  /
  |             FitLinxx, Inc.            \  Phone: (203) 708-5192    |
 /  860 Canal Street, Stamford, CT  06902   \  Fax: (203) 316-5150     \
\-----------------------------------------------------------------------/