4

[Edit: Edited based on Leigh's suggestions

I consistently have an issue with where using 

 column like <cfqueryparam cfsqltype="cf_sql_varchar" value="abc%" />

is slower than by about 30ms.

column like 'abc%'

Before the plan is cached, both queries run at about the same time ~60ms. Subsequent hits result in 1ms for the query without cfqueryparam and 30ms for the one using cfqueryparam. The DSN is sending unicode and the column type is nvarchar. I don't notice this behaivor with "=", just the LIKE operator. This particular column is not indexed.

Does anyone know why this behavior happens?

J.T.
  • 2,606
  • 15
  • 31
  • 1
    Which version of MS SQL and what are your dsn settings? Have you traced the queries with an ms sql profiler? See also this thread which touches on some [differences between varchar vs nvarchar](http://stackoverflow.com/a/10555204/104223) – Leigh Aug 10 '12 at 18:27
  • @Leigh MSSQL2012, these are all nvarchar, so that setting does not apply. Even if we weren't forcing unicode, we are sending nvarchar as the param and the columntype is nvarchar. The trace looks fairly normal. – J.T. Aug 10 '12 at 19:05
  • 1
    I do not think [ColdFusion 8](http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html) has a `cf_sql_nvarchar` type. IIRC that was not introduced until CF10. – Leigh Aug 10 '12 at 19:15
  • 1
    Also, CF does not throw an error if the `cfsqltype` supplied is invalid. I usually just defaults to `cf_sql_char`. So I would compare the trace and profile of the two queries and see what is happening behind the scenes. [Here is an example](http://stackoverflow.com/a/10848136/104223) of the details you can glean from a dsn trace and sql profiler. Just ignore the CF10 specific stuff.. – Leigh Aug 10 '12 at 19:29
  • Wow, Leigh, that is a huge find! This is going to require a relook at all of our indexes. I can only imagine that you are stuck with a unicode dsn and a nonunicode dsn when you need the ability to work with both nvar and var for performance. You know, this is the same issue I ran into last year on another application that was running sybase. The cfqueryparam was incompatible with the sybase timestamp/datetime. It was causing a scan instead of a seek in the cf app only. – J.T. Aug 12 '12 at 14:02
  • Leigh, I have edited my original question. With all things being equal, the LIKE is still slower. – J.T. Aug 13 '12 at 12:35
  • @JT - Sorry, just saw your response. Fyi, if you use a `@` in front of the username that person gets notified when you respond. Re: *All things being equal* - The dsn spy log and ms sql trace are *exactly* the same? – Leigh Aug 19 '12 at 02:03
  • @Leigh, I did not look at the dsn spy log, but I will report that. What I need to look at is the catch hits and misses with this query. – J.T. Aug 28 '12 at 01:59
  • @J.T. - Okay. The reason for examining the logs and trace was to rule out differences in preparation on the CF side. That was the cause in some of the threads above. But if everything is truly *exactly* the same then the reason probably lies with ms sql, like Jaguar suggested. – Leigh Aug 28 '12 at 23:27

1 Answers1

1

I've seen similar behaviour with sql server when querying with non parameterized parameters. As far as I know the query

select x from y where x.a like 'dog'

will never change plan, statistics or output as far as table y data does not get modified. SQL Server can, and does, detect this and stores the plan/statistics/output for a longer time period vs this query: 

select x from y where x.a like @p1

where there is nothing common between any possible parameter value. You actually say that after the plan get's cached you see the performance difference and that is because it is not only the plan that gets cached.

I've also seen a case where the query optimizer never used a valid index for a parameterized query -where for the non-parameterized it used it- and an index query hint had to be used.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Jaguar
  • 5,929
  • 34
  • 48
  • I'm having an issue understanding what exactly is triggering a cache hit or miss in this instance. I've been caught up tuning some other issues that Leigh brought to my attention in other areas, so I haven't made it back to this query. My biggest struggle right now is observing how the plan cache is managed for complex queries. – J.T. Aug 28 '12 at 01:56