Últimos mensajes - Powered by IBM

Palabras claves

Otro debate (mas) sobre los Procedimientos Almacenados

28/04/2008 - 14:00 por Pablo Roca | Informe spam

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

Siga el debate

63 respuestas

Tengo una respuesta

Preguntas similare

Cómo copio más de un archivo sobre otro???

Leer las respuestas

#1 Alejandro Mesa

29/04/2008 - 04:03 | Informe spam

Pablo Roca,

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)

Pudieras ser mas explicito, por favor?

Las consultas adhoc y las consultas parametrizadas (sp_executesql) tampoco
reusan un plan de ejecucion si estas se ejecutan desde diferentes base de
datos.

Una de las propiedades de un plan en el cache, y que es parte de su clave
(clave que identifica al plan), es [dbid]. Su valor es el [id] de la base de
datos donde reside el procedimiento, o la base de datos desde donde se
ejecuto el query adhoc o consulta parametrizada. Si el valor de [dbid]
cambia, para un query adhoc o consulta parametrizada, entonces se genera un
nuevo plan.

Por ejemplo, estos dos queries generan su propio plan:

SSMS - Ventana de queries 1

use northwind
go

declare @i int

set @i = 256

;with
L0 as (select 1 as n union all select 1),
L1 as (select 1 as n from L0 as a, L0 as b),
L2 as (select 1 as n from L1 as a, L1 as b),
L3 as (select 1 as n from L2 as a, L2 as b),
Numeros as (select row_number() over(order by (select 1)) as n from L3)
select *
from Numeros
where n <= @i
go

SSMS - Ventana de queries 2

use AdventureWorks
go

declare @i int

set @i = 256

;with
L0 as (select 1 as n union all select 1),
L1 as (select 1 as n from L0 as a, L0 as b),
L2 as (select 1 as n from L1 as a, L1 as b),
L3 as (select 1 as n from L2 as a, L2 as b),
Numeros as (select row_number() over(order by (select 1)) as n from L3)
select *
from Numeros
where n <= @i
go

A pesar de ser el mismo query, se generan dos planes debido a que este se
ejecuta desde dos distintas bases de datos. Lo puedes ver si usas la vista de
compatibilidad syscacheobjects o las nuevas vistas de administracion:

- sys.dm_exec_cached_plans
- sys.dm_exec_plan_attributes
- sys.dm_exec_sql_text

AMB

"Pablo Roca" wrote:

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

Responder a este mensaje

#2 Eduardo

29/04/2008 - 04:24 | Informe spam

Yo la verdad de tanto leer cosas sobre los planes de ejecución he decidido:
ignorar todo ;) Cuando tenga q usar una consulta ad-hoc, la uso y punto;
una preparada, la uso y punto; un sp, lo uso y punto. Vamos, que SS no
debería ser tan poco 'smart' ;)

"Pablo Roca" escribió en el mensaje
news:

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

Responder a este mensaje

#3 Alfredo Novoa

29/04/2008 - 11:17 | Informe spam

On Mon, 28 Apr 2008 22:24:54 -0400, "Eduardo" wrote:

Yo la verdad de tanto leer cosas sobre los planes de ejecución he decidido:
ignorar todo ;)

Sabia decisión :-)

Cuando tenga q usar una consulta ad-hoc, la uso y punto;
una preparada, la uso y punto;

Bueno, en realidad nunca se "necesita" usar una consulta preparada.
Una consulta preparada es una consulta normal y corriente. Lo de
preparar la consulta es un truquillo para ganar algunas milésimas de
segundo en algunos casos determinados.

un sp, lo uso y punto. Vamos, que SS no
debería ser tan poco 'smart' ;)

Cierto. Los SGBD se crearon precisamente para que los programadores
podemos ignorar esos detalles de bajo nivel. El problema es que no
siempre lo consiguen.

Yo creo que lo más inteligente es ignorar el rendimiento en una
primera fase, y una vez que el sistema funcione correctamente evaluar
si vale la pena intentar mejorar el rendimiento de alguna parte del
sistema. La gran mayoría de las cosas no habrá que tocarlas.

Saludos
Alfredo

Responder a este mensaje

#4 Carlos M. Calvelo

29/04/2008 - 19:51 | Informe spam

Hola Alejandro, Hola Pablo,

On 29 apr, 04:03, Alejandro Mesa
wrote:

Pablo Roca,

> 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...lanes-d...

> 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)

Pudieras ser mas explicito, por favor?

Las consultas adhoc y las consultas parametrizadas (sp_executesql) tampoco
reusan un plan de ejecucion si estas se ejecutan desde diferentes base de
datos.

Una de las propiedades de un plan en el cache, y que es parte de su clave
(clave que identifica al plan), es [dbid]. Su valor es el [id] de la base de
datos donde reside el procedimiento, o la base de datos desde donde se
ejecuto el query adhoc o consulta parametrizada. Si el valor de [dbid]
cambia, para un query adhoc o consulta parametrizada, entonces se genera un
nuevo plan.

Lo caul es totalmente lógico.
Imaginémonos dos bases de datos con la estructura lógica totalmente
igual, pero a nivel físico la una optimizada para actividades tipo
OLTP y la otra para actividades tipo OLAP.
Pues una tendrá pocos índices por ejemplo y la otra muchos; el mismo
procedimiento puede estár implementado con algoritmos distintos, etc,
etc.
Entonces... claro que el plan de ejecución depende de la base de
datos,
porque depende de los datos, estadísticas, y mecanismos a nivel
físico
como los índices y distintas implementaciones del mismo procedimiento.

Si Pablo me permite, y muy amistosamente, tengo la impresión de que
está confundiendo SGBD con base de datos.

Saludos,
Carlos

Responder a este mensaje

#5 Carlos M. Calvelo

29/04/2008 - 20:34 | Informe spam

Hola Eduardo,

On 29 apr, 04:24, "Eduardo" wrote:

Yo la verdad de tanto leer cosas sobre los planes de ejecución he decidido:
ignorar todo ;) Cuando tenga q usar una consulta ad-hoc, la uso y punto;
una preparada, la uso y punto; un sp, lo uso y punto. Vamos, que SS no
debería ser tan poco 'smart' ;)

Efectivamente. Pero por expresarlo de otra forma:

Se diseña un modelo lógico que es el que se ve desde las
aplicaciones; tablas, vistas, funciones, procedimientos (interfaz),
etc. Esto es lo que siempre se verá desde el exterior. Subesquemas
distintos dependiendo de quien se conecta.
No todas las tablas, funciones, etc. tienen que pertenecer a este
nivel.

Cuando esto esté listo siempre se puede optimizar haciendo cambios
en el modelo físico. Por ejemplo: lo que era una tabla pasa a ser
una vista materializada y viceversa; se monta un índice aquí y otro
allá; lo que en el diseño lógico es una tabla en realidad es una
vista que une dos tablas (nivel físico) o viceversa; se reprograma
un procedimiento por que se ha encontrado un algoritmo mejor;
particonamos tablas, etc, etc. Los planes de ejecución pertenecen
a este nivel.

Todos estos cambios, sin afectar para nada el modelo lógico y por
tanto sin romper las comunicaciones con las las aplicaciones
existentes. Solo se podrán notar cambios con la eficiencia, que
por ejemplo se puede degradar para unas aplicaciones o tipo de
consultas para favorecer otras, porque alguien que tiene mucho
que decir en la organización ha decidido que son son mas importantes.

Espero valga de algo.

Saludos,
Carlos

Responder a este mensaje

Ads by Google

Hacer una preguntaSiguiente

Tengo una respuesta

12 3 4 5 6 7 8 9

Busqueda sugerida