Skip to main content
D
Dax_Trajero
Inspiring
April 14, 2011
Answered

stored procedures, newbie question

  • April 14, 2011
  • 2 replies
  • 1316 views

I'm at the stage with my website where I need to think about query optimisation, and have started looking at stored procedures for the first time.

1. Am I right in thinking the performance benefit of stored procedures are that its a pre-compiled query?

2. I try to use query cache as far as my 'normal queries' go, but is it possible to cache the result of a stored procedure ?

In practice, using 1 & 2 would offer a significant performance increase.

    This topic has been closed for replies.
    Correct answer Owainnorth

    Personally, I only use them for big hefty updates or deletes. For example, I have an Oracle package with a whole load of procedures that run when someone completes an order - this process involves numerous tables, deletes, updates and some relatively simple logic. I consider that an ideal candidate for a sproc.

    I use CF as an alternative if, for example, I'd have to repeat a load of complex business logic in a sproc that I've already written in a CFC.

    Bear in mind the advantages of a sproc - basically precompiled code. Remember that if you're using well-formed cfqueries with cfqueryparams, the execution plan gets compiled up on the first run *anyway*, so whether you call a sproc or run your query there really will be no difference.

    And yes - don't underestimate the extra time it takes for a developer to do everything in sprocs rather than ColdFusion, they don't call it rapid application development for nothing

    Remember as well that someone can write a poorly-coded sproc as well as they can write a poorly-coded query. At least if the developer has his query in front of him he can instantly see what indexes he needs and spot any mistakes, you could go years before realising someone made a mistake inside a sproc as no-one can see the code.

    As for security yes - if you really want to limit security then sprocs will do that for you. However in most applications I've seen the developer just has the ability to write a query as they see fit.

    One other time I use sprocs while I think of it - if you have more than one application that needs to do the same thing - this should *definitely* be a sproc.

    Basically, the same rules apply to them as any other programming aspect; security, duplication of code, efficiency, maintainability - it's all a case of weighing them up and deciding what's best for you.

    2 replies

    WolfShade
    WolfShade
    Legend
    April 14, 2011

    SP's also have the benefit of A) making the database server do more work, lessening the load on the web server CPU; B) using less bandwidth by returning pre-filtered and pre-sorted content; and C) modular design (being able to use the same SP on more than one page, even more than one site).

    Owainnorth
    Owainnorth
    Inspiring
    April 14, 2011

    1 - Correct. There is also less data to be passed to and from the server to the database box as all you need to send is the proc name, and its parameters.

    2 - Interesting one, and not one I've ever tested to be honest. However CFSTOREDPROC does indeed have CachedAfter and CachedWIthin attributes, so I would assume so.

    As for a performance increase it really depends what you're doing with your procs. I've seen people wrap up every single query into a procedure, which I can't really see helping. However if you're doing big data warehousing or updating tasks then the advantages can be massive.

    D
    Dax_TrajeroAuthor
    Inspiring
    April 14, 2011

    thanks Owain

    "As for a performance increase it really depends what you're doing with your procs. I've seen people wrap up every single query into a procedure, which I can't really see helping."

    So whats the ideal criteria for using stored procedures - only on big meaty stuff ? Surely it doesn't do any harm on the smaller stuff ? More work for the developer, but at least he knows its efficient, offers increased portability and a degree of security, compared to a conventional query ?

    Owainnorth
    OwainnorthCorrect answer
    Inspiring
    April 14, 2011

    Personally, I only use them for big hefty updates or deletes. For example, I have an Oracle package with a whole load of procedures that run when someone completes an order - this process involves numerous tables, deletes, updates and some relatively simple logic. I consider that an ideal candidate for a sproc.

    I use CF as an alternative if, for example, I'd have to repeat a load of complex business logic in a sproc that I've already written in a CFC.

    Bear in mind the advantages of a sproc - basically precompiled code. Remember that if you're using well-formed cfqueries with cfqueryparams, the execution plan gets compiled up on the first run *anyway*, so whether you call a sproc or run your query there really will be no difference.

    And yes - don't underestimate the extra time it takes for a developer to do everything in sprocs rather than ColdFusion, they don't call it rapid application development for nothing

    Remember as well that someone can write a poorly-coded sproc as well as they can write a poorly-coded query. At least if the developer has his query in front of him he can instantly see what indexes he needs and spot any mistakes, you could go years before realising someone made a mistake inside a sproc as no-one can see the code.

    As for security yes - if you really want to limit security then sprocs will do that for you. However in most applications I've seen the developer just has the ability to write a query as they see fit.

    One other time I use sprocs while I think of it - if you have more than one application that needs to do the same thing - this should *definitely* be a sproc.

    Basically, the same rules apply to them as any other programming aspect; security, duplication of code, efficiency, maintainability - it's all a case of weighing them up and deciding what's best for you.

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