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.

[sladebe]

Whoops, entered a bad answer here by mistake.  No way to delete it.

[sladebe]

Yo Mark, thanks for the "kudo" for this answer. It made me snicker. Especially since I got the notification email saying, "Mark_A_Parsons thinks your post is awesome and just gave you kudos!"

[Mark_A_Parsons]

Hilarious email. I have email notifications disabled so didn't realize that's a thing. Thanks for the EOW chuckle!