Skip to main content
M
myscreenname0345
Participating Frequently
November 10, 2010
Question

CFScript query alot slower than CFML!

  • November 10, 2010
  • 4 replies
  • 1696 views

Has anyone experienced CFScript addParams being much slower

than CFML <cfqueryparam>?

In my test, CFML took 2 min. to insert 20,000 rows.

But, CFScript took 27 min!

Here is basically what I'm trying to do:

----------------------- CFScript ----------------------------------------------

var myquery = new query(dataSource="...",

                                     sql="insert into mytable (col1, col2, ..., col64)

                                            values (:col1, :col2, ..., :col64)");

var i = 1;

while (i <= #recs.q.recordCount#) {

      myquery.clearParams();

      myquery.addParam(name="col1", null="#recs.n['col1']#", value="#recs.q['col1']#", cfsqltype="cf_sql_varchar");

      myquery.addParam(name="col2", null="#recs.n['col2']#", value="#recs.q['col2']#", cfsqltype="cf_sql_varchar");

      ...

      myquery.addParam(name="col64", null="#recs.n['col64']#", value="#recs.q['col64']#", cfsqltype="cf_sql_varchar");

      myquery.execute();

      i = i + 1;

}

----------------------- CFML --------------------------------------------------

<cfset var i = 1>

<cfoutput query="recs.q">

      <cfquery name="myquery" dataSource="...">

            insert into mytable (col1, col2, ..., col64)

            values (<cfqueryparam null="#recs.n['col1']#" value="#recs.q['col1']#" cfsqltype="cf_sql_varchar">,

                       <cfqueryparam null="#recs.n['col2']#" value="#recs.q['col2']#" cfsqltype="cf_sql_varchar">,

                       ...

                       <cfqueryparam null="#recs.n['col64']#" value="#recs.q['col64']#" cfsqltype="cf_sql_varchar">)

      </cfquery>

      <cfset i = i + 1>

</cfoutput>

-------------------------------------------------------------------------------

In the CFScript version, I thought clearing and re-adding the params on each

iteration might be the problem. So, as a test, I removed the clearParams(),

and moved the addParams before the loop, calling them just once

(adding the same param values over and over, which of course is not what

I really want to do). But, it's just as slow.

I might also point out that both versions are huge memory hogs

(the CFScript version is worse), and the memory never seems to get

garbage collected after my script is done.

Anyone have any ideas?

Thanks.

myscreenname0345

    This topic has been closed for replies.

    4 replies

    M
    myscreenname0345Author
    Participating Frequently
    November 11, 2010

    This morning, I inserted 20,000 rows using the following methods:

    CFML        <queryparam>                                5m 30s
    CFScript    addParam() Named params           35m
    CFScript    addParam() Positional params         9m 35s
    CFScript    No params (hardcoded values)         5m

    So, using named params appears to be a major problem.
    However, positional params are still twice as slow as CFML.

    There's nothing else going on the machine during these tests.
    I'm the lone user.

    Also, after 90 min., jrun still hasn't released the memory used.
    Is ColdFusion somehow still pointing to the queries, structs, and arrays
    I built in the processes which have since completed? They should have
    been garbage collected by now.

    Thanks.

    myscreenname0345

    A
    Adam Cameron.
    Inspiring
    November 11, 2010

    Good investigation.  I dunno what to suggest other than to raise a bug with Adobe: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#.

    And go back to using <cfquery>.

    Hopefully Adobe will revisit their approach in CF10.

    --

    Adam

    A
    Adam Cameron.
    Inspiring
    November 11, 2010
    var i = 1;

          i = i + 1;

    while (i <= #recs.q.recordCount#) {

    }

    Unrelated to your question, but why are you using a while() loop here instead of a for() loop?

    And you dount needs those hashes around the recordCount variable. One only needs to use hashes when there's ambiguity as to whether the expression is a string or a variable name.  There is no ambiguity here.

    --

    Adam

    A
    Adam Cameron.
    Inspiring
    November 11, 2010

    Have you put any timing metrics in to identify what exactly is causing the problem? Do the addParam() calls run progressively slower?  Is it definitely the addParam() calls, and not the execute() call?

    I don't have CF9 in front of my to confirm, but I seem to recall that one doesn't need to use named params, one can still use positional ones.  Does using positional ones make any difference here?  Because as it stands you're not really testing like-for-like here.

    --

    Adam

    12Robots
    12Robots
    Participating Frequently
    November 11, 2010

    One thing I would be curious about, what would happen if you created a new Query Obect for each iteration in the cfscript version.

    In your example, you are using the same query object for every query. Perhaps that is causing an issue?  I don't know, I am just throwing out an idea. I would say, even if just for fun and knowledge, to try adding the query object creation to the loop, and see what happens.

    Also, you might want to try it with the same variable name, and a dynamic variable name.

    for example:

    while (i <= #recs.q.recordCount#) {

         var myquery = new query(dataSource="...",

         ....

    }

    and

    while (i <= #recs.q.recordCount#) {

         var "myquery#i#" = new query(dataSource="...",

         ....

    }

    I have NO IDEA if this will help or make things worse, I am only guessing at something that might help figure out what's different. Also, I am unsure of the dynamic variable syntax in my second example.

    Good luck.

    Jason

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices