Otro debate (mas) sobre los Procedimientos Almacenados

Siento si el tema es recurrente, pero ahi va:

El siguiente hilo es para proponer un debate (amistoso) sobre los
procedimientos almacenados.

Llegué hace poco a SQL Server 2005 y de lo primero que hice fué empezar a
programar todo o casi todo con procedimientos almacenados. Hasta aquí bien,
en momentos noté que (a ratos) las consultas no iban todo lo bien que
debieran ...

Soy un cliente final, y mi casuistica particular me impone que tenga que
utilizar unas 60 bases de datos. Por lo que uno piensa en buscarse la manera
de sincronizar los procedimientos almacenados entre BBDD, alternativamente
mira uno de poner los SPs en la BBDD master, lo que simplifica la
administración y mantenimiento (hasta cierto punto, porque trabajar en
pruebas contra master es un tanto latazo), pero se me presenta el problema
de que tengo que dar acceso a master a todos los usuarios que utilicen la
aplicación, cosa que no me gusta nada.

Lo que es el modelo de poner todo el acceso a datos dentro de la BBDD, la
verdad que me gusta. Pero despues de leer y comprobar en el post de Eduardo
Quintás

http://geeks.ms/blogs/quintas/archi...nados.aspx

Que los procedimientos almacenados no reutilizan sus planes de ejecución si
se ejecutan en BBDD distintas, eso ya me tiró por el suelo a los SPs. Y me
dió una pista de porque los SP's me iban bien a veces (si consultaba siempre
contra la misma BBDD)

Pegas que le veo a los SPs

- la fundamental es la no reutilización de planes de ejecución entre BBDD
distintas.
- si uno se pone a escribir SPs como si fuera un lenguaje normal de
programación puede caer en el error de hacer esto:

IF condicion1
SELECT uno
ELSE
IF condicion2
SELECT dos
ELSE


hacer grandes bloques de if y consultas SELECT es de las peores cosas que se
pueden hacer con un SP, ya que: los planes de ejecución variaran en funcion
de las condiciones de la consulta, la compilación del plan de ejecución se
hace muy lenta.

En lugar de hacer eso es mucho mas optimo hacer:

IF condicion1
EXEC sp1
ELSE
IF condicion2
EXEC sp2
ELSE


¿Porque? Por la reutilización de planes de ejecución. En este segundo
ejemplo el plan de ejecución del SP principal es siempre el mismo, y el de
cada SP que es llamado tambien. Se evitan tiempos de compilación, cacheo,
...

Ventajas de los SPs

- tener todo el código de acceso centralizado en un único sitio
- facilidad de mantenimiento (incluso en producción)

Bien ante todo esto, estoy empezando a cambiar el chip y pasar mis consultas
a la capa de negocio del lado del servidor (lo que estoy haciendo es una
aplicación SOA) utilizando consultas parametrizadas (ahora decirme que las
consultas parametrizadas no se reutilizan entre BBDD y ya se me lió el
invento :)).

Al hilo de esto.

¿Como trabajar de una manera segura con los SPs en master? ¿Metiendo los SPs
en esquemas personalizados y jugando con los permisos?

Bueno .. cualquier comentario es bienvenido.


Saludos,

Pablo Roca
La Coruna - Spain
http://www.portalfox.com