3 Replies Latest reply on Sep 13, 2007 4:25 PM by MarkAltenbernd

    CFC vs SQL Stored Proceedure

    BigRedFan
      I'm not real familiar with SQL stored proceedures so I'm wondering if they have any speed or resource benefits over CFCs? Do they help take a load off of CF and are they any faster or just another way to query? I have a number of queries that get used over and over that are currently in CFCs. I'm trying to determine if it makes sense to convert them to stored proceedures.

      Any thought?

      Thanks!
        • 1. Re: CFC vs SQL Stored Proceedure
          Level 7
          A SQL stored procedure is going to put the work on the database server
          where database work should be. The ultimate answer to your question is
          'it depends'. What type of logic is in the cfcs? What kind of
          processing is going on there?

          If you are just doing basic select, update, insert queries making them
          stored procedures is not going to provide much of a performance boost,
          but you may get a separation of logic that is worth the effort to some
          developers.

          If you are dealing with more complex data logic where you are getting
          multiple record sets with complex parameters and combining them into
          sophisticated structures, a stored procedure may provide significant
          boost in performance. Since all the heavy lifting will be done in the
          database with one request, rather then several requests to get all the
          pieces of data and assembling them in the ColdFusion memory.

          If your logic is mostly business logic that is not all about complex
          data manipulation then the CFC is going to be a more logical place to do
          this in my mind. Databases are not any more efficient at basic loops
          and branches then ColdFusion if data manipulation is not involved.

          To completely answer your question is going to require a good
          understanding of your requirements and a benefits versus costs analysis
          of developing separate layers to maximize efficiency or a simpler single
          layer solution that may be a few cpu cycles slower.

          • 2. Re: CFC vs SQL Stored Proceedure
            Mamdoh_Alhabeeb
            I have been using the cfstoreproc within cfccomponent since those tags introduced in CF 6.0. All what I can say, that it make it much easier,faster process, better organizing my application logic, and better security since I am using SQL storeproc. for sure it is faster to use storeproc for all the four functions (insert, select, update and delete). there are many research approve that the spend using SP is faster and much efficient .

            Good luck
            Mamdoh
            • 3. Re: CFC vs SQL Stored Proceedure
              MarkAltenbernd
              CFCs and stored procs are not mutually exclusive. Stored procs can be called from within a CFC.

              A stored procedure will always provide a performance boost over a query that submits SQL to the database server. The reason for this is that the server must parse and analyze the incoming SQL, produce an execution tree, optimize the tree, and do a few other things, as well. With a stored procedure, these operations are done once, when the procedure is compiled; with SQL that is passed in a query , these expensive operations must be repeated each time the query is used.

              In addition, there are potentially some significant security benefits from using stored procedures.