I was just thinking about stored procs in services , now im definetly on the side of dynamic ( paramaterized) code instead of stored procsas i rarely think the benefits are worth the costs of another deployment tier or involving an additional team.
The suposed benefits are Security, performance and organisation i will address these below.
Security
Stored procs are supposed to have security so that only the stored proc can change the table however has anyone ever used this ? It is also worth noting you can creating a number of sql accounts with specific security and create different connection pools - has anyone ever done this ? No.
The biggest security issues with databases is the fact applications nearly always use a single application wide login. This means the DBA does not knowwhich user is running the query killing the DB nor does he have auditing of the DB of which user made a certain change. Pass through authentication has been available for a while but in practice its rarely used , this is is a far more serious yet resolvable problem if SP security was such a big deal this would be resolved much earlier.
Performance
By now all the performance benefits of stored procs have been debunked except for some reports.
Organizational
In some organizations the DB's are managed by a skilled set of DBAs who know the DB better and often tune the stored procs. This is the only valid reason for stored procs. It is up to the organization to decide whether the impediments to development and roll out of an application are worth these benefits . Its worth noting that in some environments an extensive stored proc library has enabled an architecture with some of the benefits of SOA ( such as reuse , though hard to debug and maintain) .
But what about services ?
As a service's roll is tightly defined it can normally have a very limited sql account with only the tables its responsible for.
As far as performance goes most services provide caching. Service also run similar queries eliminating the miniscule benifit provided by the SP.
Services should ideally have their own database or be responsible for a few tables in a database. Having multiple services responsible for the same table in a database makes that services considerably more difficult to maintain and can result in significant issues if the schema has to change for one service. This changes the organizational focus.
Lastly services can easily become a set of wrapped stored procs this is wastefull and can result in a poorly designed architecture and should be discouraged.
Hence i can see no reason why stored procs should be used in SOA services.
Print | posted on Tuesday, January 13, 2009 8:50 PM