pyodbc runs "select" queries very slowly when providing parameter

[jtdawso1]

Hello,

For a bit of context, I'm using ASE 16.0 SP03, an up-to-date copy of libsybdrvodb.so, Python 3.8.10, and an up-to-date copy of pyodbc ( https://github.com/mkleehammer/pyodbc )

I found a strange issue when running a simple "select" query through using pyodbc's "?" syntax. When I run this query directly in a sqsh console, it executes *very* quickly:

select * from files where filename='pgb.197901.bz2';

Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.

However, when I run that exact same query in a python script, it takes over a minute! The exact syntax I'm using is this:

cursor.execute("select * from files where filename=?", ['pgb.197901.bz2'])

print(cursor.fetchall())

I could've sworn that this wasn't an issue two months ago, which has me quite confused!

The above query takes over a minute, but this syntax takes just 18 ms:

cursor.execute("select * from files where filename='pgb.197901.bz2'", [])

print(cursor.fetchall())

This does work, but it's not a great solution--we want to use pyodbc's placeholder "?" syntax, because it helps to prevent SQL injection attacks.

I have found one other workaround which doesn't work perfectly here: https://community.sap.com/t5/technology-q-a/ase-15-5-poor-performance-from-odbc/qaq-p/12120748 , which suggests adding "DYNAMICPREPARE=0;" to my connection string. That indeed speeds up performance! When I use this connection string:

"DRIVER={};SERVER={};UID={};PWD={};PORT=5000;DYNAMICPREPARE=1;APP=SelectTest;"
My "cursor.execute("select * from files where filename=?", ['pgb.197901.bz2'])" query takes just 20 milliseconds (including the time taken to create the connection).
The problem with this approach is that it appears to break stored procedures! When I run this:

cursor.execute("exec get_files @filename=?", ['pgb.197901.bz2'])

print(cursor.fetchall())

I receive this error message:
"[42000] [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near '?'.\n (102) (SQLExecDirectW)"

So that solution doesn't work very well for me either

To summarize, it seems like I have three bad options:

1. Keep everything the same, but swap all my queries to only use stored procedures (not great because we want to be able to test queries out on-the-fly)
2. Break all stored procedures, but speed up our other queries (by adding DYNAMICPREPARE). Would require large changes to our code base, so this isn't viable
3. Make ourselves vulnerable to sql injection (insert params directly into the query string)

I'm not sure what to do--am I missing something here? Is there some way to speed up our queries so that they don't run orders-of-magnitude more slowly that *doesn't* break our existing stored procedures? Am I doing something strange in my connection information? Is there some argument that I'm missing out on?

Thank you for your help!

EDIT/TL;DR/SOLUTION:

Thanks to everyone who commented, I think that this is much more clear. It appears that the issue is caused by pyodbc encoding python strings incorrectly (as BINARY data rather than CHAR data), which then forces the database to use an inefficient plan to execute its query. I've got two stable solutions, but I recommend using the first. Here they are:

1. conn = pyodbc.connect(
   "DRIVER={};SERVER={};UID={};PWD={};PORT=5000;APP=SelectTest;".format(
   db_config["driver"],
   db_config["server"],
   db_config["uid"],
   db_config["pwd"],
   ),
   autocommit=True,
   )
   conn.setencoding(encoding='iso-8859-1') # this is the most important line!
   cursor = conn.cursor()
   cursor.execute("select * from files where filename = ?", [filename_to_query'])
   print(cursor.fetchall())
2. conn = pyodbc.connect(
   "DRIVER={};SERVER={};UID={};PWD={};PORT=5000;APP=SelectTest;DYNAMICPREPARE=1;".format( # this DYNAMICPREPARE thing is the most important part here
   db_config["driver"],
   db_config["server"],
   db_config["uid"],
   db_config["pwd"],
   ),
   autocommit=True,
   )
   cursor = conn.cursor()
   cursor.execute("select * from files where filename = ?", [filename_to_query]) # note: if the query is a stored proc, make sure that you *do not* use the "exec" keyword, otherwise the query appears to break. Unsure why
   print(cursor.fetchall())

[Mark_A_Parsons]

I can't comment on pyodbc coding so just some general suggestions:

• Have the DBA capture the query and query plan (eg, via MDA tables) to see what's being passed to ASE. (I'm guessing the search parameter is being mangled, possibly wrapped in % (wild card) characters, and ASE is opting for a table scan.)
• With the info provided by the DBA, post a detailed question in the discussion area of that github/pyodbc page.

I'd want to verify no issues with the pyodbc code before (potentially) opening an ASE case.

[ChrisBaker]

'Dynamic prepare' supports queries, not stored procedure calls.

[Mark_A_Parsons]

runs more slowly is because the plan says "Positioning at index start", whereas running the same query in sqsh says "Positioning by key".

Positioning by key means the index was used to quickly zero in on the desired row(s) based on a distinct search value (eg, key='abc').

Positioning at index start implies an index scan (as opposed to a table scan) and will occur when (in this case) the only search clause is on the index key but the search value is not a distinct value (eg, key like '%abc%', or key=substring('abc',1,3)).  In this situation ASE is left with scanning the entire table and applying the search clause against every row; if there's an index on the search column and ASE determines the index resides on fewer pages than the raw data then it will opt for an index scan.  In your case ASE is opting to scan the index (fewer pages than scanning the entire table) while applying the search clause against every key value in the index ... this will (usually) be faster than a full table scan but it will still be quite a bit slower than a query that can benefit from Positioning by key.

Why is ASE opting for an index scan? This would require knowing what exactly ASE is seeing as the search value (submitted by your python script).  This is where the previous comment about the DBA and MDA tables comes into play ... have the DBA capture the actual SQL being submitted by your application.  [Or use the trace method provided by Ryan.] Having the actual (likely mangled) search value in hand should get you a step closer to determining if there's an issue with the way you're constructing the query or an issue with the library you're using.

Does the 'slow' python query eventually return the same results as the sqsh query? or does it return no results?

[sladebe]

Here's a SAP Note about a similar behavior, but with cached statements (automatically precompiled SQL statements) instead of prepared statements:

[sladebe]

Here's a SAP Note about a similar behavior, but with cached statements (automatically precompiled SQL statements) instead of prepared statements: https://me.sap.com/notes/2077161/E

Query takes a long time to complete due to bad table scan when statement cache or literal autoparam is on

ASE query showplan shows that optimizer is choosing a table scan instead of more efficient index scan when statement cache and literal autoparam are on.
Optimizer chooses the more efficient index scan when literal autoparam is off
Query contains multiple copies of the same literal value

Environment
SAP Adaptive Server Enterprise (ASE) 15.7
SAP Adaptive Server Enterprise (ASE) 16.0

Cause
When literal autoparam is used, the optimizer cannot assume that the literal values are all the same, as different values could be substituted for the next execution and the cached plan will need to be reasonable for both circumstances. When literal autoparam is not active, the optimizer can generate a better plan based on knowing the values of the literals.

The issue has been identified and logged under SAP CR 644531. Its description reads "When statement cache or literal autoparam is on, the query chooses a bad table scan that takes a long time to complete. With statement cache off, or literal autoparam off, the query chooses the index."

Resolution
There is currently no target Fix Pack or Service Pack scheduled for SAP CR 644531. It is being reviewed by our Product Group. Subscribe to this KBA to be automatically notified when SAP CR 644531is fixed.
Workarounds are:

set statement_cache off
or
sp_configure "literal autoparam", 0

[Mark_A_Parsons]

Ahhh, you updated your comment while I was typing mine.

-------------

In OP's case I'd opt for set statement_cache off; select ...; set statement_cache on.

Disabling configs (eg, literal autoparam) at the server level should be thoroughly tested as said changes could adversely affect other queries, users and/or applications. ("Duh, Mark!" ?)