MS SQL and <cfqueryparam> slow-down

We've been profiling our MS SQL traffic, and we're seeing the occasional spike in execution times related directly to the operations that CF perform in relation to the <cfqueryparam> tag in getting table column details. The problem crops up only on <cfquery> tags where <cfqueryparam> is used for values, and the specific operation to get column details hangs the process up for up to 45 seconds. At this time, I'd like to ask if anyone else has seen this kind of behavior. Thank you!

It could be that you are not indexing the columns you're using variable data to search on. ~ Mike Stemle, jr. On Apr 25, 2012, at 12:18, Christopher Watson <skygeex@gmail.com> wrote: > > We've been profiling our MS SQL traffic, and we're seeing the occasional spike in execution times related directly to the operations that CF perform in relation to the <cfqueryparam> tag in getting table column details. The problem crops up only on <cfquery> tags where <cfqueryparam> is used for values, and the specific operation to get column details hangs the process up for up to 45 seconds. At this time, I'd like to ask if anyone else has seen this kind of behavior. Thank you!

Actually, the prolonged execution times are for simple tables where the proper indexing has been in place. For assessment purposes, we have removed the <cfqueryparam> tags from that set of queries in our processes that manipulate the tables on which we are seeing the degradation in the profiler. We will look at the profiler data again in the morning to see if any of the same prolonged execution times exist. If not, then we'll stick with no <cfqueryparam> tags, since this is all related to tables and column data types and values where we have complete internalized control, and there really is no compelling reason to have the <cfqueryparam> tags in there. We'd LIKE to have them in there anyway, because of the other benefits realized. But if using them causes this degradation, and removing them does not, we'll opt for the latter. > It could be that you are not indexing the columns you're using > variable data to search on.

Cfqueryparam has 2 purposes, to paramatise the params which adds your injection security and to create a prepared statement for better execution plan. It seems the execution plan is where u need to look for problems. But a workaround would be to use cfparam outside the cfquery instead if cfqueryparam, which would do the same job as far as sql injection protection from paramatisation goes. Regards Russ Michaels From my mobile On 25 Apr 2012 20:37, "Christopher Watson" <skygeex@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

What is the ODBC driver in play here? ----- Excess quoted text cut - see Original Post for more -----