jamdb_oracle
jamdb_oracle copied to clipboard
Query slow using positional bindings against varchar columns
I've been finding that queries to Oracle 11g wind up very slow when using query
or query!
with parameters defined via positional bindings like :1
. Today I found this:
https://stackoverflow.com/questions/37784388/slow-with-bind-parameters-even-slower-with-jdbctemplate#comment121142811_38078671
I think we're hitting this... the database has varchar columns, meaning that our queries are skipping indexes and scanning the table to convert each matched column to nvarchars. When we explicitly to_char(:1)
, our query speeds up dramatically.
Is there a possible connection configuration that would help? Maybe specify the default string parameter type as varchar?
If not hopefully we can contribute something. Just wondering if anyone already has a solution.