[egenix-users] cursor.executedirect configurable?

[Brad Allen]

Thanks. I haven't yet done any serious performance comparisons between 
execute and executedirect with the ODBC driver we're using ( the 
built-in Windows ODBC driver for SQL Server).

Instead, I was motivated by the finding that some of the large join 
queries I generated using Storm worked with executedirect, but failed 
cryptically using plain cursor.execute. I would prefer to find out why 
the same query failed with cursor.execute rather than switch to 
executedirect.

Any ideas on what would cause this a select query to fail with the plain 
execute, but works fine with executedirect? Here is the error:

mx.ODBC.Error.InterfaceError: ('07009', 0, '[Microsoft][ODBC SQL Server 
Driver]Invalid Descriptor Index', 6793)

Thanks.

M.-A. Lemburg wrote:
> Hello Brad,
>
> On 2008-09-10 04:18, Brad Allen wrote:
>   
>> I am using mxODBC 3 with the Storm ORM, which makes use of
>> cursor.execute. I would like to find a way to configure mxODBC to use
>> executedirect without having to change or override Storm's call to
>> cursor.execute.
>>
>> Is there some way to configure mxODBC to use the executedirect behavior
>> when cursor.execute is called?
>>     
>
> No, that's not possible without wrapping the cursor object and
> then redirecting the method in the wrapper.
>
> Note that cursor.execute() will work like cursor.executedirect()
> when called without parameters.
>
> You should check the performance of using one over the other.
> cursor.executedirect() will bypass the prepare step, so the
> statement will have to be parsed and processed over and over
> again.
>
> Some database backends do clever caching on the server, so this
> is not an issue, in fact, it's faster for simple queries since
> you avoid a few network round-trips.
>
> For other backends, it's slower, since they don't implement
> such caching.
>
> Another issue is related to parameter binding: using
> cursor.executedirect() the ODBC driver does not have any
> parameter type information available (this only becomes
> available via the prepare step), so mxODBC cannot do
> any efficient conversion to database data types on the
> client side. This may result in conversion problems on
> the server side and introduce extra overhead.
>
>   

-- 
ZeOmega
Open Minds' Open Solutions
3010 Gaylord Parkway, Ste. 210
Frisco TX, 75034
http://www.zeomega.com

Brad Allen
214-618-9880 (ext. 8006)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: /mailman-archives/egenix-users/attachments/20080910/1a67c691/attachment.htm