Issue 269: To reuse query plans, bind parameters for string should just use SQL_SS_LENGTH_UNLIMITED as ColumnSize
Status:  Investigating
Owner: ----
Reported by sok...@gmail.com, Jun 3, 2012
The current usage of bind parameters doesn't really maximize query plans reuse. In SQL Server, the following code:

import pyodbc

conn = pyodbc.connect(...)
cur = conn.cursor()
cur.execute('SELECT * FROM users WHERE username = ?', ('admin'))
cur.execute('SELECT * FROM users WHERE username = ?', ('aminah'))

will generate 2 different queries, i.e.:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 varchar(5)',N'SELECT * FROM users WHERE username = @P1','admin'
select @p1

and:

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 varchar(6)',N'SELECT * FROM users WHERE username = @P1','aminah'
select @p1

Each requires its own plan, which can be verified with:

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

The attached diff changes the behavior by sticking with VARCHAR(MAX) and NVARCHAR(MAX).

I am not sure if there is any side effect, but so far things look fine with my SQLAlchemy based application using dialects mssql+pyodbc and ibm_db_sa+pyodbc.
pyodbc-bind-param-varchar-max.diff
437 bytes   View   Download
Jun 4, 2012
#1 sok...@gmail.com
Updated diff. The previous one only changes NVARCHAR(N) to NVARCHAR(MAX); this one also does the same to VARCHAR.
pyodbc-bind-param-varchar-max.diff
711 bytes   View   Download
Jun 4, 2012
#2 sok...@gmail.com
Hmm.. forget about it. I have a composite index on a VARCHAR(50) column and an INT column, e.g.

    CREATE INDEX ix_test_lookup ON test (varchar_col, int_col)

With bind parameter being VARCHAR(MAX), e.g.

    exec sp_executesql N'SELECT * FROM test WHERE varchar_col = @P1 AND int_col = @P2',
    N'@P1 varchar(MAX),@P2 int',
    'abc',123

SQL Server is not able to use the index as intended, as it will resort to very inefficient seek predicates:

    Start: test.varchar_col > Scalar Operator(ExprXXX), End: test.varchar_col < Scalar Operator(ExprYYY)

With bind parameter being VARCHAR(N) where N is anything from 1 to 8000, e.g.

    exec sp_executesql N'SELECT * FROM test WHERE varchar_col = @P1 AND int_col = @P2',
    N'@P1 varchar(8000),@P2 int',
    'abc',123

SQL Server will use the index as intended, with seek predicates:

    Prefix: test.varchar_col, test.int_col = Scalar Operator(@P1), Scalar Operator(@P2)

This is truly bizarre since VARCHAR(MAX) should be the same as VARCHAR(8000).
Jun 4, 2012
#3 sok...@gmail.com
My understanding was wrong, VARCHAR(MAX) is a different beast than VARCHAR(8000).
Jun 4, 2012
#4 sok...@gmail.com
Improved diff to use varchar_maxlength and wvarchar_maxlength as ColumnSize when appropriate
pyodbc-bind-param-varchar-max.diff
1.9 KB   View   Download
Sep 29, 2012
Project Member #5 mkleehammer
I don't think I can use the patch as-is - the column lengths are typically used to indicate the data size and I don't want to lose that.

Please reopen if I'm misunderstanding something.

Sep 29, 2012
#6 sok...@gmail.com
I thought `info.StrLen_or_Ind` is to indicate the data size?

`info.ParameterType` and `info.ColumnSize` combine to indicate the bind parameter type.
Sep 29, 2012
Project Member #7 mkleehammer
You are correct.  I was dealing with another Issue and made the same mistake.  I'll look at this again.  Thanks

Status: Investigating