Search code examples
sqlodbcsybasedatabase-performanceparameterized-query

performance of parameterised SQL

I have a query like 

SELECT *
FROM myTable
WHERE key LIKE 'XYZ'

The value 'XYZ' is entered by users (and may include % and _)

If I construct the query using string concatenation it runs in 10 seconds. But this is unsafe, and I should use a parameterised query.

So I'm constructing the query using the odbc command object and it's execute method, and passing a parameter.

SELECT *
FROM myTable
WHERE key LIKE ?

Unfortunately the parameterised SQL execute method takes a full minute.

This query is one of many that are part of a drill-down / investigation package, and I've had similar slow downs with all the parameterised queries (compared to string concatenation).

How do I find out where the time is going (and fix it) ?

Solution

  • Mitch had the right suggestion.

    I had to change the connection string to use the OLEDB driver, then I could set the options:

    • Optimize Prepare=None
    • Select Method=Direct