SQLAlchemy 1.4 caching support warning

[Jskarie]

I see several issues discussing this issue, but no way to resolve it.

SAWarning: Dialect ibm_db_sa:pyodbc will not make use of SQL compilation caching as it does not set the 'supports_statement_cache' attribute to True. This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions. Dialect maintainers should seek to set this attribute to True after appropriate development and testing for SQLAlchemy 1.4 caching support. Alternatively, this attribute may be set to False which will disable this warning. (Background on this warning at: https://sqlalche.me/e/20/cprf)

current versions
ibm_db==3.2.7
ibm_db_sa==0.4.3

[bchoudhary6415]

Hi @Jskarie,

Thanks for reporting this issue.

We do plan to enable supports_statement_cache = True, but the current dialect is not yet compatible with SQLAlchemy’s statement-caching requirements. Our tests show that LIMIT/OFFSET are still rendered as literals and bindparam-based limits fail to compile, which means the dialect does not yet meet SQLAlchemy’s caching criteria.
According to SQLAlchemy documentation, dialects must generate cache-safe SQL (no embedded literal LIMIT/OFFSET values, proper use of BindParameter.render_literal_execute(), etc.) before enabling this flag:

• Dialect caching support: https://docs.sqlalchemy.org/en/20/core/internals.html#sqlalchemy.engine.Dialect.supports_statement_cache

• Caching requirements for third-party dialects: https://docs.sqlalchemy.org/en/20/core/connections.html#engine-thirdparty-caching

We’ll update the compiler logic accordingly and make the dialect fully compliant before enabling statement caching.

Thanks again for raising this.

[Jskarie]

Would it make sense to set the supports_statement_cache to False to disable the warning until it ready?

I appreciate your work. This project has improved my tooling significantly.

Thank you!
James

[bchoudhary6415]

Hi @Jskarie,

At the moment, the dialect does not set supports_statement_cache = True, it is still False across all dialect variants:
• DB2Dialect_ibm_db in ibm_db_sa/ibm_db.py
• DB2Dialect_pyodbc in ibm_db_sa/pyodbc.py
• DB2Dialect in ibm_db_sa/base.py

So under normal conditions, SQLAlchemy should not emit the caching warning.

However, SQLAlchemy will still show the warning if:
• the dialect is missing part of the internal cache-key machinery,
• or SQL compilation produces “postcompile” parameters,
• or the dialect didn’t fully opt-in to the statement-caching protocol introduced in SQLAlchemy 1.4.

We are currently working through those requirements (compiler changes, limit/offset rendering, cache-key compatibility).
Once the caching support logic is fully implemented and tested for both SQLAlchemy 1.4 and 2.x, we will flip supports_statement_cache = True.

If you have a minimal script that reproduces this warning with the current release, please share it so we can verify the exact trigger.
We are actively adding full statement-cache support, so any reproducible test will help us validate the final implementation.

Thank you!

[bchoudhary6415]

Hi @Jskarie,

This issue has been addressed in the PR #188.
Please review the changes and share any feedback.

I’m closing this issue for now.
If you encounter this problem again or notice any related regressions, feel free to reopen the issue.

Thank you!