High Performance: the importance of Stored Procedures and WHY you SHOULD NOT write them

Posted: September 14, 2012 in Architecture, Database

Why Stored Procedures? It’s true that SP do not allow full flexibility on the coding aspect, but it’s also true that enterprise systems are sophisticated machines that requires fine tuning and extreme security… and this is why the SP are so important.

From a developer point of view, I always see a sad faces when I “force” the use of stored procedures and sometimes, believe it o not, also from the DBAs…
Now, I want to set the target on Oracle and SqlServer and leave outside of this discussion MySQL and DB2: MySQL because when you’re talking enterprise it’s better to use commercial products (especially on the DB)… and DB2 because the implementation of IBM SP sounded always strange to me (in DB2 mainframes, apparently, the bigger and complex the SP, the better… and external access from outside the IBM sphere environment is always really slow).

In general, PROs of the SP:

  • high performance;
  • direct DBA tuning (if every access to the DB is manageable from the DBAs, the tuning is in the DBA hands as well);
  • total execution plan caching;
  • you add a layer of authentication and data validation;

and the CONs:

  • “someone” has to write them;
  • there is DBA time spent on something “developer driven”.

I had been a DBA and also a developer and I can tell you that the best place to put the “development” of the SP is in the DBAs hands… but with a custom SP code-builder.

Normally 90% of the code has the same functionality: GET to show a grid, SET to insert/update/delete values. Every time I work on this topic I build a TABLE-based SP builder which, based on the structure of the table (or view) creates the following SP:

  1. Insert, getting all fields i input;
  2. Update, getting all fields i input and checking with ISNULL if the update value is required or not and using as where always the PK;
  3. Delete, using as where always the PK;
  4. Select, using as where always the PK.

In addition to these normally are used a couple of other SELECT with different where condition. We’ll talk about the dynamic TOP and ORDER BY clause in the future, since normally we use a good amount of them.

Clearly also the calling of these stored procedures must be created from code-builders, so we can build a nice structured code, without double-coding and without add any time-consuming error.

I’ll be soon more detailed about these codebuilders, if you want you can write me and i can send you some example to speed your “perfect system” building.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s