10 December 2007

Good Database Design Part 7: Stored Procedures

Most DBAs are aware of the many benefits of using stored procedures for data access; in short, performance and security. By using stored procedures we can offload some to all of the business rule enforcement that is not handled by constraints.

Database servers tend to bottleneck more on I/O than memory or CPU. The hardware backing a database server tends to be stronger than an application or web server. Using some of those free resources can ease the burden on your application servers while reducing the amount of network traffic and database transactions. Why would you want to worry about in-line SQL and ad hoc queries? Maintenance, permissions and tuning are often complicated by using such primitive methodologies. Stored procedures are beautiful things.

By using one procedure for inserts and updates, you can minimize the types of calls to a database, simplifying the developer’s job. If you are using surrogate keys, it is very simple to implement:

create procedure Client_Add(iClient_UID, iClientName, iDateContract)
    if(iClient_UID = 0 or iClient_UID is null) then
        insert into
            Client(
                ClientName,
                DateContract
            )
            values(
                iName,
                iDateContract
            );
    else
        update
            Client
        set
            ClientName      =   iClientName,
            DateContract    =   iDateContract
        where
            Client_UID      =   iClient_UID;
    end if;

If you are not using surrogate keys, you need to check if the record exists based on the primary key (not a bad thing to do even if you are using surrogate keys).

Tables with foreign keys can be translated and/or validated. In many cases, we can get away from costly triggers by using stored procedures to handle translations. Validate your foreign keys

Similarly for the retrieves, you can use the surrogate key to determine whether to grab a specific row or some to all rows.

While procedures facilitate dynamic SQL beware that they introduce similar risks as inline SQL. Always validate your inputs.