2

We had a slow page loading and upon investigating it was the query execution. I noticed the page on the dev server was fine and I noticed when I ran the query via MSSQL Mgt Studio it ran fine, only on the CF page it was slow. I noticed the ids were being checked with a cfsqltype of cf_sql_numeric instead of cf_sql_integer. For kicks I changed it to integer and it now loaded as expected.

So, what gives, why would that make a difference?

As to why it may have suddenly started going slow was our DBA added some indexes recently to speed up the DB in general. But still, why so slow just based on the param type.

Leeish
  • 5,203
  • 2
  • 17
  • 45
  • 4
    If the type of the param does not match the column type you're querying, an underlying conversion will have to be done by the server. In some cases this can lead to a query that *should* be using an index to not use one. What I suspect was that once you changed it to an `INT`, it was able to utilize an existing index on the table. You can verify this in SSMS by looking at the generated query plans. – Siyual Feb 13 '17 at 20:56
  • Thank you. I sort of suspected that might be happening but I wasn't sure if the queryparam was only forcing the data on the CF side or if it was altering the underlying query in some way. We had a dev use "numeric" a lot in queries but against the ids for tables, obviously not needed most of the time for these auto increment columns. – Leeish Feb 13 '17 at 20:58
  • 1
    If this was forcing a conversion on the database side, then this is a thorough explanation: [Implicit Conversion Costs - Jonathan Kehayias](https://sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs) – SqlZim Feb 13 '17 at 21:10
  • Yep to what Siyual said. Implicit conversion can sometimes have unintended, and ugly, side effects. If you have time, check out the execution plans in SSMS to see what they say. – Leigh Feb 13 '17 at 21:38

0 Answers0