ASE 15.5 Poor performance from ODBC

[former_member650057]

While was running statistics on database one query coming from .NET ODBC connection on IIS couldn't be executed. Was getting time out after 30seconds. When we was running the same query from isql was executed very fast. The problem wasn't solved even when we killed the gather stats process. The case solved after 3 hours, when we restart the IIS.

It seemed that when the query was running from isql was getting the correct plan but when was coming from IIS was performing full table scan

What could cause the problem?

And why the database wasn't using the same execution plan in both cases?

[jtdawso1]

For anyone visiting this page in the future, this thread is related: https://community.sap.com/t5/technology-q-a/pyodbc-runs-quot-select-quot-queries-very-slowly-when-pr...

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

That is, the first solution forces pyodbc to use the encoding "iso-8859-1", aka "latin-1", aka "iso-1", which appears to work better than the default of pyodbc, and therefore passes the param as a VARCHAR. The second solution is a little more indirect--I believe that this works because "DYNAMICPREPARE" tells the DB to create a temporary stored procedure. Because stored procedures force casting to the datatype appropriate for the column, the query again behaves correctly

(edited April 21 2025 with a second solution and direct textual examples)

[dawn_kim]

Hi,

So isql is usually straight sql prepare and execute statement, depending on how the call is made in the code can change if the data is parameterized or dynamic. In reality dynamic is suppose to be faster.

You have already identified the plan is different and bad that causes the issue. There have been CR's for this in ASE.

Might be just setting an ASE option or maybe an odbc connection modification. Maybe do DynamicPrepare=1 in the odbc connection string to see if this changes anything.

You need to list the version of the odbc driver (point to sybdrvodb.dll and click properties or look in the odbc manager for Adaptive Server Enterprise) and the ASE (select @@version) you are connecting to. This will help to see if it's an existing issue.

Thanks,
Dawn