stored procedures, newbie question

[Dax_Trajero]

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.

[Owainnorth]

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.

[Dax_Trajero]

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 · Accepted solution]

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.

[Adam Cameron.]

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

This is a very good point, and a significant consideration.  The last time I had to work with Oracle procs, it took me a bloody age to get the solution written.  That said: it was my first time using PL/SQL (or indeed writing a proc of any description), so it was as much a learning exercise for me as much as writing the solution (don't tell my client...). It took me over a week to write the procs that I could have banged out with CFQUERY tags in an afternoon.  But I'm good with CF, and I had only just started with PL/SQL, so fair enough, I guess.  PL/SQL sure is a lot less forgiving and much fiddlier than writing CFML though.

I did enjoy the whole exercise though.

--

Adam

[JR__Bob__Dobbs]

It might also be appropriate to ask this question in a forum specific to the database you are using.  Not all database systems are created equal.

For example:

1. PostgreSQL allows functions to be written in non-SQL languages such as Java or Perl.  This is in addtion to creating user defined functions with the PostgreSQL SQL dialect.

2. MS SQL Server compiles sprocs the first time they are invoked, in Oracle (if I remember correctly) sprocs are complied when created.

[Owainnorth]

1. PostgreSQL allows functions to be written in non-SQL languages such as Java or Perl.  This is in addtion to creating user defined functions with the PostgreSQL SQL dialect.

2. MS SQL Server compiles sprocs the first time they are invoked, in Oracle (if I remember correctly) sprocs are complied when created.

Also very good points, you can compile up CLR code (in C#, Visual Basic etc) into a SQL Server database if you need something more complex doing.

[Owainnorth]

Adam Cameron. wrote:

PL/SQL sure is a lot less forgiving and much fiddlier than writing CFML though.

It sure is, would still rather write a thousand lines of that than ten lines of T-SQL though

[Dax_Trajero]

Great info as always guys.

Many thanks

[Adam Cameron.]

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 ?

My position is the DB should do the DB stuff, and CF should do the CF stuff.  I'd put all the data interaction in the DB, and simple have proc calls from CF to get specific data when necessary.  The CF plays to its strengths and loops (etc) over that generating mark-up.

You should perhaps experiment with creating solutions as both SQL-written in the CF application (ie: called via CFQUERY), and the same data-fetch requirement implemented as a proc.  Then profile them both under a reasonable load simulation (always simulate under load: running a single CFQUERY and comparing to a single CFSTOREDPROC call is not going to tell you much: it won't be how your app will be used).  See what the profile says to you, and adjust your solution accordingly.

It's surprisingly infrequent that for a given data-fetching requirement than only one record set is needed... have a look at your debug output when you render a page: how many DB hits are there with CFQUERY tags?  Probably quite a few.  I've seen - poorly written - single requests that run HUNDREDS of queries (we got this down to seven, and it could probably legitimately be one proc call).  There's probably a case for rolling a bunch of these together into a single proc, which does much the same logic, but it's just one hit to the DB.  I don't think simply converting all the queries you're currently running into procs one a one-to-one-mapping basis: analyse what record sets are used together, which ones are always being called together, which ones can be sensibly / logically grouped together.

--

Adam

[WolfShade]

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).