jamdb_oracle icon indicating copy to clipboard operation
jamdb_oracle copied to clipboard

Query slow using positional bindings against varchar columns

Open sax opened this issue 5 months ago • 1 comments

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.

sax avatar Sep 19 '24 02:09 sax