Skip to main content
P
ProjectedSurplus
Inspiring
October 14, 2008
Question

Stored Proc Performance vs CFusion

  • October 14, 2008
  • 3 replies
  • 653 views
I'm relatively more experienced with sql and for the variety of reasons discussed elsewhere (performance, security, portability HTML/flex and MVC code organization) am building an app more with cfcs and stored procedures.

That said, initial iterations are usually <cfquery> based, however I have just moved a relatively simple procedure (which sets the look and feel for the home page depending on visitor IP) from

<cfquery>
to
<cfinvoke> a cfc which then <cfstoredproc> a mysql 5.1 stored procedure

Long story short, it all works but the latter is taking longer to load (exact same data from exact same devserver machine with both cf8 and mysql on it) by a significant factor (probably 100 times).

Any thoughts on why? (fwiw server is p4 800 mhz 1.5 Gb Ram)
    This topic has been closed for replies.

    3 replies

    P
    ProjectedSurplusAuthor
    Inspiring
    October 15, 2008
    K, traced problem and . . . . it's mysql INNODB table type!

    Converting table type to myisam reduced time down to 1844ms. Still high but this is old equipment (2x800Mhz P3) and the GeoLiteCityBlocks table is 3M rows.

    But still (and I realize it has something to do with cf cache almost undoubtedly) can anyone suggest why (previously) the exact same SELECT statement executed by a <cfquery> (inside a cfc) was so much quicker than the exact same select statement called by a stored proc (from the same cfc)?????

    FYI the difference is still 47ms with <cfquery> SELECT

    vs.

    2098ms with <cfstoredproc>
    P
    ProjectedSurplusAuthor
    Inspiring
    October 14, 2008
    >What about on subsequent hits to the same code, with the same parameters?

    No difference (and I am using CF8).
    N
    Newsgroup_User
    Inspiring
    October 14, 2008
    > <cfquery>
    > to
    > <cfinvoke> a cfc which then <cfstoredproc> a mysql 5.1 stored procedure

    You're doing two operations in the latter compared to one in the former.
    How do just apples-n-apples compare: CFQUERY vs CFSTOREDPROC (eliminating
    the CFINVOKE for the purposes of testing)? What version of CF are you on?
    CF's traditionally been pretty slow to instantiate CFCs (it's getting OK at
    it, in CF8).

    What about on subsequent hits to the same code, with the same parameters?

    I've actually found - much to my dismay - that CF or JDBC is a bit slow
    with Oracle stored procs, compared to the same DB processing being done in
    a CFQUERY. For relatively simple stuff, anyhow. None of my procs are more
    than 50-or-so statements long.

    I can't vouch for MySQL. Haven't used it since v3.x.

    --
    Adam
    P
    ProjectedSurplusAuthor
    Inspiring
    October 14, 2008
    > How do just apples-n-apples compare: CFQUERY vs CFSTOREDPROC (eliminating
    > the CFINVOKE for the purposes of testing)?

    <cfquery> in .cfm page IS SAME SPEED AS <cfinvoke> a cfc which then runs <cfquery>

    AND MUCH MUCH FASTER THAN

    <cfinvoke> a cfc which then <cfstoredproc> a mysql 5.1 stored procedure
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices