[egenix-users] Re: mxODBC Cursor Unicode issuewith method executedirect().

Harri Pesonen harri.pesonen at wicom.com
Fri Aug 17 14:35:09 CEST 2007


Good to know, I was not aware of .execute() doing automatic caching of
the prepared statement. So it may be faster than .executedirect(),
depending on how the application is written. We have a multi-threaded
application and it is not clear, in which order the executes are made,
it may change from day to day, so it is hard to tell which one is
better. Obviously we could have different cursors for the different
cases, but it would be harder to implement. Hmm we already have a
different database connection (and cursor) for some cases... So it could
benefit of using execute instead of executedirect...

-- Harri 

-----Original Message-----
From: M.-A. Lemburg [mailto:mal at egenix.com] 
Sent: 17. elokuuta 2007 13:20
To: Harri Pesonen
Cc: egenix-users at lists.egenix.com
Subject: Re: [egenix-users] Re: mxODBC Cursor Unicode issuewith method
executedirect().

On 2007-08-17 12:07, Harri Pesonen wrote:
> M.-A. Lemburg <mal at ...> writes:
> 
>> On 2007-08-15 16:47, Cliff Xuan wrote:
>>> Hi Marc-Andre,
>>>
>>> Thanks very much for your answer.
>>> Dose that mean I should avoid using executedirect() if I do have
parameters?
>> You should always prefer .execute() over .executedirect().
>>
>> If you do want to try .executedirect(), then please do timings checks
>> and data transfer checks (like the ones you are apparently doing),
>> since using .executedirect() may very well be slower for what
>> you want to do, use more memory and result in unwanted recoding
>> of data.
> 
> Hi, about executedirect() vs execute(). Generally executedirect() is
faster
> (ODBC documentation says "SQLExecDirect is the fastest way to submit
an SQL
> statement for one-time execution"). Execute() is faster only if the
statement is
> prepared first, and then executed several times. Usually it is faster
and easier
> to use executedirect(), because prepare+execute(N times) is harder to
implement.

There's an optimization in mxODBC which does this for you:

If you pass in the same SQL command multiple times (and in
sequence) to the .execute() command, with varying parameters tuples,
it will only prepare the command once and then reuse the already
prepared command for all subsequent invocations of .execute().

mxODBC 3.0 also features a .prepare() method which can be
used to e.g. keep a set of prepared cursors in a pool for
fast execution of common queries. .prepare() allows to
build query plans without actually executing the statement
in question.

> I guess that mxODBC executemany() does exactly the same thing as
> prepare+execute(N times).

Right.

The problem with .executemany() is that error detection is a bit
harder, ie. say the insert of row 10 fails in a set of 100 rows.
mxODBC will include the row information in the error message,
but only textually.

Using a loop with .execute() will allow you to catch and process
such errors on a row-by-row basis.

> Well we have experience only with SQL Server, so different databases
may behave
> differently.

Thanks for the feedback.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 17 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611



More information about the egenix-users mailing list