« Stuff The Internet Says On Scalability For March 28th, 2014 | Main | Oculus Causes a Rift, but the Facebook Deal Will Avoid a Scaling Crisis for Virtual Reality »

Strategy: Cache Stored Procedure Results

Caching is not new of course, but I don't think I've heard of caching store procedure results before. It's like memoization in the database. Brent Ozar covers this idea in How to Cache Stored Procedure Results.

The benefits are the usual for doing work in the database, it doesn't take per developer per app work, just code it once in the stored proc and it works for everyone, everywhere, for all of time. The disadvantage is the usual as well, it adds extra load to a probably already busy database, so it should only be applied to heavy computations.

Brent positions this strategy as an emergency bandaid to apply when you need to take pressure off a database now. Developers can then work on moving the cache off the database and into its own tier. Interesting idea. And as the comments show the implementation is never as simple as it seems.

Reader Comments (3)

The Oracle DB has had function & query result caching for quite sometime. For details of function result caching see PL/SQL documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS00817

March 27, 2014 | Unregistered CommenterPLSQL

I suspect there are limited applications for this concept, as most modern databases will cache the data in memory anyway, so re-running the stored procedure on the same data will be much faster (in general).

It depends entirely on the specific nature of the project, the team, etc, but IMHO, YMMV, etc, as there is a danger of adding a layer of complexity, hence additional point of failure, for little real-world gain.

If course, if the stored procedure is doing a lot of very complex computations, then there may be some advantage, but you have to ask whether that is a good use of a database engine anyway. Of course, that is really a question for another topic ;-)

March 28, 2014 | Unregistered CommenterSteve Jones

Then you try to implement something like a state machine in stored procedures and you get tons of bugs because it's not natural.

March 28, 2014 | Unregistered CommenterAmel Musić

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>