CODEWITHSUNDEEP
stored-procedurescoldfusion
Pyae Phyoe Shein
Pyae Phyoe Shein

Reputation: 13787

different between cfstoredproc and cfquery

I've found previous programmers using cfstoredproc in our existing project about insert records into database.

Just example he/she used like that:

<cfstoredproc procedure="myProc" datasource="myDsn">
    <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="ppshein" dbvarname="username">
    <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="28 yrs" dbvarname="age">  
</cfstoredproc>

I can't convince why he/she used above code instead of:

<cfquery datasource="myDsn">
    insert usertb
    (name, age)
    values
    (<cfqueryparam cfsqltype="CF_SQL_CHAR" value="ppshein">, <cfqueryparam cfsqltype="CF_SQL_CHAR" value="28 yrs">)
</cfquery>

I feel there will be the hidden performance using cfstoredproc and cfquery for complex data manipulation. Please let me know the performance using cfstoredproc in coldfusion instead of cfquery for complex data manipulation. What I know is reusable.

Upvotes: 1

Views: 3480

Answers (3)

ale
ale

Reputation: 6430

Some shops prefer to have all data logic controlled by the database, leaving CF to act almost exclusively as the front-end generator. Some places are so controlling that they won't let you write any SQL in your CF code.

Update: There might be more to that Stored Proc than a simple INSERT INTO. There may be some data lookup in another table. There could be validation. There may be conditional logic. There may be multiple transactions going on, like a log. A failure to perform the insert may return a specific status code rather than throwing an error.

Honestly, it's simply a matter of style. There are reasons for and against either way, and I've found that it usually comes down to who has more/more competent coders: The CF guys or the database guys.

Upvotes: 3

Ciaran Archer
Ciaran Archer

Reputation: 12446

Basically if you use a stored procedure, the stored procedure will be pre-complied by the database and the execution plan stored. This means that subsequent calls to the stored procedure do not incurr the that overhead. For large complex queries this can be substantial.

So as a rule of thumb: queries that are...

  1. large and complex or
  2. called very frequently or
  3. both of the above

...are very good candidates for conversion to a stored procedure.

Hope that helps!

Upvotes: 1

Lars
Lars

Reputation: 101

CFSTOREDPROC should have better performance for the same reason a stored procedure will have better performance at the database level -- when created, the stored procedure is optimized internally by the database.

Whether this is noticeable depends on your database and your query. Use of CFQUERYPARAM inside your CFQUERY (as in your example) also speeds execution (at the db driver level).

Unless the application is VERY performance-sensitive, I tend to run my SQL code in a profiler first to optimize it, then put it into my CFQUERY parametrized with CFQUERYPARAM tags, rather than use a storedproc. That way, all the logic is in the CF code. This is a matter of taste, of course, and it's easy to move the SQL into a storedproc later when the application matures.

Upvotes: 4

Related Questions