Subscribe to the RSS feed
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 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).
as
begin
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;
end;
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.
2 comments:
There is a problem with using stored procedures in your design. It ties you to a specific database.
Using a library like Hibernate or LINQ (when M$FT supports db's other than $QL $erver) would be a better choice when designing your persistence into your application.
I see your point (had to look into LINQ, which has some merits). However, we could make the same argument about programming languages. Portability is a nice selling point, but people still go for features and performance over easy migrations for mission critical applications. To which you may site the gaining prevalence of web applications and compilers/interpreters being less platform dependent than before the Dot Bomb. Ok, it was a better point in the early nineties.
Blame the vendors—they are the ones that choose to use their own implementations of a partially implemented standard that does not even fully support set operations. Blame the SQL standard for not being robust enough to accommodate all the business needs and failing to adapt… Maybe the vendors should participate in that process a bit more.
From a design and implementation perspectives, stored procedures are the way to go. Just because DBMS companies constantly one-up and play keep-up with each other doesn’t change good design. And yes, stored procedures are very implementation specific, as are the performance advantages. But it is grounded in good theory and supported by good practice.
Since most DBMS vendors use SQL, it is the vendor’s choice on how closely they adhere to the standard and that is what has the largest impact on portability. Even without stored procedures, you are going to have database code changes for the schema and DML (Data Modification Language) statements. Yes, you are looking at larger changes if you use stored procedures (There are conversion utilities out there, but that is sort of scary. MySQL tries to stay in the middle, aliasing a lot of their functions to accommodate personal preferences and porting).
But generally, we do not design around portability; databases need to scale more than they need to be portable. As well, the larger a system, the more customized it is, the less portable it is.
So is portability more for smaller applications? Why do we need to port? In my experience, most ports are a result of an application outgrowing its initial scope or moving into the world of ‘legacy.’ In which case, you are revisiting functionality anyway and it is often simpler to do a whole re-write.
Just because something requires more effort does not mean that it is wrong. Simplification is a great thing; but I do not think that making something easier to do ensures that you are doing it right. I think when we do things right, they seem simple. Libraries can make things easier and more portable. Just because products support inline SQL methods, that does not make them the smartest answer.
So while you have a point, I do not think it invalidates what I am trying to convey. Thanks for making me think.
Post a Comment