Slow Query, CF 9 & MSSQL 2008; corruption implementation plan?

Question

I have been working on Coldfusion / MS SQL for many years, and this is one of the strangest problems I have seen. Since then, the problem itself has been resolved, but I do not quite understand what happened; this question is trying to get a clear idea of ​​the probable cause.

Problem

In a stable production environment, for no apparent reason, one request starts to return in about 1000-1500 ms (about 10 times slower than usual). I managed to highlight this:

<cfquery datasource="#ds#" name="query"> select 1 from eLearning.v_courseCompletion cc where cc.memberIncId = <cfqueryparam value="3" cfsqltype="cf_sql_integer"> and cc.courseId = <cfqueryparam value="25" cfsqltype="cf_sql_integer"> and cc.currentCourseCompleted = 1 </cfquery> 

What is strange, this behavior is compounded when inside the loop, even when there is one iteration, as in this example:

 <cfloop from="1" to="1" index="i"> <cfquery datasource="#ds#" name="query"> select 1 from eLearning.v_courseCompletion cc where cc.memberIncId = <cfqueryparam value="3" cfsqltype="cf_sql_integer"> and cc.courseId = <cfqueryparam value="25" cfsqltype="cf_sql_integer"> and cc.currentCourseCompleted = 1 </cfquery> </cfloop> 

It should be exactly the same as above, right? The loop should have no effect, but instead this test runs about 10 times slower, returning between 7000-16000 ms. This is how the problem was discovered; the request (buried inside the method of the object) is called from the body of the cycle, if the cycle is repeated more than 5 or 6 times, the request expires.

For me, this indicated a problem on the part of Coldfusion, but restarting the service or even the machine did nothing.

Meanwhile, when I was isolated, I noticed that making any changes to the request itself made the performance return to the expected level of about 150-190 ms. For example:

• Change selected fields (i.e. select * )
• Removing a table alias ( cc )
• Replacing either <cfqueryparam> with an inline value
• Removing any of the conditions

Any of these changes fixed the problem, but when you run the original query, the performance problem will return.

Decision

At this point, I realized that the query execution plan was corrupted or something like some Googling and started DBCC FREEPROCCACHE against the database server. This fixed the problem immediately. Great, the problem is resolved ....

Question

Since then, though, I have done a little more research, and the consensus seems to be that the implementation plans are "not damaged." There are a few conversations of similar problems related to stored procedures, and the sniffing option , but I do not use sp. We choose from a rather complex view, though ( eLearning.v_courseCompletion ) with nested joins. This is problem?

Basically, what really happened here? How can I stop him again?

.. and what the hell is connected with cycles in CF?!?

Versions

• Coldfusion 9.0.2.282541 (64 bit)
• SQL Server Express 10.50.4297 (64 bit)
• Both servers are the Datacenter (64-bit) Win Server 2008 R2

Answer 1

You use the stored procedure under the hood when using cfqueryparam. When you are not using cfqueryparam, your request is simply sent as a “free text” batch request. When you use cfqueryparam, you send your query for execution with sp_executeSQL (), which itself will send the body of the query as a parameter. This allows you to cache the request. If he sees the same query, he will use the statistics stored for this particular plan. This means that if he worked with some really alien data and got a bad idea for fulfilling the request, the following iterations would use the same plan, which is a “bad plan” for 99% of cases of using this query, but perhaps a good plan for of this one random instance.

Each query that is performed using sp_execute SQL also returns a numeric descriptor that the JDBC driver can use to simply specify the SQL that it can use, basically, the shortcut. This is called the "maximum combined statement" in your DSN settings in CFadmin. If set to 0 or 1000, it will not affect the fact that you will use the plan cache with sp_executeSQL.

http://blogs.msdn.com/b/turgays/archive/2013/09/18/exec-vs-sp-executesql.aspx

StackOverflow had a good demonstration of this, if one user of authority loaded his page of his account with his millions of badges and points before query statistics were built, it would ruin the statistics for every other user who has only a few hundred or so dots and a few icons, making the page slow for him or her.