My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 17: Regain performance lost since 2.0.36
1 person starred this issue and may be notified of changes. Back to list
Status:  Started
Owner:  mkleehammer


 
Project Member Reported by mkleehammer, Dec 29, 2008
As reported by Joe Salmeri (via email), the 2.1.x is slower than the 2.0.x
branch.

After examining some ODBC trace logs, we determined that it was extra
SQLDescribeParam calls that were added to work around binding None/NULL.

When passing parameters, pyodbc has to tell the driver the data type of the
parameter.  When None is passed, pyodbc obviously doesn't know -- it is
NULL and there is no way to tell if it is going into a character column, an
integer, etc.

In the 2.0 branch, pyodbc always set the data type to char, but SQL Server
will not let you insert a character value into a binary column even if you
are passing NULL.  To fix this, I use SQLDescribeParam, if supported by the
driver, to determine the correct data type.  Unfortunately, this adds 1
driver call per parameter for every query, which is way too much overhead.

Dec 29, 2008
Project Member #1 mkleehammer
The fix will be in 2 parts.  The first, which has been tested by Joe, is to:

* Only call SQLDescribeParam for a parameter when None is passed.  All other types
are determined from the pyodbc data type.

* Cache this information with the prepared statement.  If the same SQL statement is
used repeatedly, we will never call more than once per parameter.

The second part of the fix is to cache this information for more than just the last
statement.  Instead, I'll keep an internal dictionary of the last 10, 20, etc.
statements and anything we know about them.  I'll have to make a method to change the
dictionary size and to flush it.
Nov 28, 2010
#2 rbrt8...@gmail.com
Are you actively working on this? I'm looking for a project to learn python on c-level and I've been playing with pyodbc a couple of days. I was thinking:

- create a hashtable with hashes for each statement, both for fast lookup and to prevent sql-code hanging around in-memory

- instead of caching N statements, to cache all statements, add a 'lastused' ticker and flush statements that were not re-used for x seconds, optionally settable by user (where x=0 means no cache)

I'd not be fast, thinking in term of months go get this right.

Feb 6, 2011
#3 sad.n...@gmail.com
How about adding a way to specify the types manually, for example, by exposing them as classes? Something like this: cursor.execute('select Helloworld = ?', pyodbc.types.Binary(None))
Feb 6, 2011
#4 lukedell...@gmail.com
Another benefit of specifying types manually is that pyodbc could then work better with ODBC drivers which do not implement SQLDescribeParam, eg FreeTDS.
Feb 13, 2011
#5 sad.n...@gmail.com
Yes, and in addition to that, there are databases which do not implement SQLDescribeParam as well, for example, Sybase.

Powered by Google Project Hosting