| Issue 269: | To reuse query plans, bind parameters for string should just use SQL_SS_LENGTH_UNLIMITED as ColumnSize | |
| 3 people starred this issue and may be notified of changes. | Back to list |
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.
Jun 4, 2012
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
My understanding was wrong, VARCHAR(MAX) is a different beast than VARCHAR(8000).
Jun 4, 2012
Improved diff to use varchar_maxlength and wvarchar_maxlength as ColumnSize when appropriate
Sep 29, 2012
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
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
You are correct. I was dealing with another Issue and made the same mistake. I'll look at this again. Thanks
Status:
Investigating
|
711 bytes View Download