To reuse query plans, bind parameters for string should just use SQL_SS_LENGTH_UNLIMITED as ColumnSize

[GoogleCodeExporter]

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.

Original issue reported on code.google.com by sok...@gmail.com on 4 Jun 2012 at 6:46

Attachments:

• pyodbc-bind-param-varchar-max.diff

[GoogleCodeExporter]

Updated diff. The previous one only changes NVARCHAR(N) to NVARCHAR(MAX); this 
one also does the same to VARCHAR.

Original comment by sok...@gmail.com on 4 Jun 2012 at 9:37

Attachments:

• pyodbc-bind-param-varchar-max.diff

[GoogleCodeExporter]

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).

Original comment by sok...@gmail.com on 4 Jun 2012 at 11:39

[GoogleCodeExporter]

My understanding was wrong, VARCHAR(MAX) is a different beast than 
VARCHAR(8000).

Original comment by sok...@gmail.com on 4 Jun 2012 at 11:50

[GoogleCodeExporter]

Improved diff to use varchar_maxlength and wvarchar_maxlength as ColumnSize 
when appropriate

Original comment by sok...@gmail.com on 5 Jun 2012 at 12:44

Attachments:

• pyodbc-bind-param-varchar-max.diff

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 29 Sep 2012 at 7:54

[GoogleCodeExporter]

I thought `info.StrLen_or_Ind` is to indicate the data size?

`info.ParameterType` and `info.ColumnSize` combine to indicate the bind 
parameter type.

Original comment by sok...@gmail.com on 30 Sep 2012 at 12:13

[GoogleCodeExporter]

You are correct.  I was dealing with another Issue and made the same mistake.  
I'll look at this again.  Thanks

Original comment by mkleehammer on 30 Sep 2012 at 12:49

• Changed state: Investigating