SQLite Forum

Performance issues with query parameters vs. static strings
Login
Hi folks,

we are currently changing a lot of code in a big solution to use parameters instead of using concatenated strings for the parameters in large IN queries, like so:

Old:
`SELECT surrogate, assoc, partner FROM tcAggrValueC WHERE surrogate IN ('CBC96E58-0CF1-4EA8-81C6-6A17A9F20913') ORDER BY sequence ASC`

New:
`SELECT surrogate, assoc, partner FROM tcAggrValueC WHERE surrogate IN (:I0) ORDER BY sequence ASC`

Now the number of IN parameters can go up to 1000 at a time, quite a lot, I know. But we were happy with the performance up to now
After the first round of changes however, our automated performance tests broke, with up to 30% degradation. We wonder now, how can using parameters be that much slower than using static strings.

Basically we believe that in the transition code from managed to unmanaged something seems to not efficiently use the existing command, can this be? Another idea is that SqLite in general is slower in this mode due to the need of parsing and mapping of the parameters.

Another thing is that we need those queries to run in transactions, meaning that the command needs to be explicitly moved on the new connection and transaction like this for every execution:

`//Old connection and transaction are disposed, refresh them
command.Connection = Connection;
command.Transaction = Transaction;`

But somehow I feel thats a bad way to do it, any ideas on that as well? Any good example how to use a prepared command together with transactions?

Thanks BR Florian