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

[M.-A. Lemburg]

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