Strategy: Cache Stored Procedure Results
Thursday, March 27, 2014 at 8:56AM 
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
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 ;-)
Then you try to implement something like a state machine in stored procedures and you get tons of bugs because it's not natural.