CFScript query performance is very bad

Question

I am running a very simple query in ColdFusion. If I run it in a cfquery tag, it completes in around 20ms. If I use a query object in cfscript, it is taking around 500ms to complete. It doesn't seem to be the query itself--the debug output says that the query finishes in about 10ms in both cases. Any idea what is going on?

CFQuery version:

<cfquery name="selUsers" datasource="m112dev_2">
  SELECT * FROM Users
  WHERE User_ID = 3
</cfquery>

CFScript version:

<cfscript>
  q = new Query();
  q.setDataSource('m112dev_2');
  q.setName('selUsers');
  selUsers = q.execute(sql="SELECT * FROM Users
                            WHERE User_ID = 3
                     ").getResult();
</cfscript>

If it says that the query is finishing in 10ms, then there is definitely something else going on. You should turn on debugging, and add trace statements before each line to see which line is slowing it down. You can check out the trace function here: help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/… — Dan Short
@DanShort: figured it out. problem was that i had component cache disabled in CFAdmin — Kip

Kip Kip · Accepted Answer · 2012-08-17T16:07:33

I found the solution:

Turn on component cache

Once I turned it on, I get basically the same performance. I think the problem is that ColdFusion actually runs the query in a different component (C:\ColdFusion9\CustomTags\com\adobe\coldfusion\base.cfc), so if path resolution is not cached it has to find that CFC every time.

CFScript query performance is very bad

1 Answers

Turn on component cache