serian eficiente estos triggers

[Yovanny Rojas]

Hola amigos de foro:
Quiero hacer una consulta sobre triggers con relacion a que tan lento
pondrian estos mi aplicación

Me refiero si la tabla detalles seria de miles de registros y lo acumulara
en en la tabla de acumulaciones que presento a continuación, seria eficiente
esto y se podria usar en muchas tablas mi aplicacion esta hecha en visual
basic 6

Gracias.

Creación de triggers con Sql-Server
15 de Agosto de 2001

Todo lo que viene a continuación es código fuente con un ejemplo de como
usar las tablas inserted y deleted en triggers para que en insercciones
masivas
de datos no solamente tengamos acceso al primer elemento insertado o borrado
sino a todos, además evita el uso de cursores que enlentecen siempre el
proceso.

Observad que no borro las tablas temporales #insertados y #eliminados
ya que por definición su alcance es local al procedimiento (trigger en este
caso).

Bien, al código....
Si quereis probarlo es suficiente con copiar y pegar a partir del create
table.
A disfrutar.

-- Creamos una tabla con los detalles de entrada ...
Create table detalles (id int identity(1,1) not null primary key,
Descripcion varchar(80) not null,
Codigo int ,
Cantidad int,
Precio money)
go -- y otra para guardar los acumulados .....
create table acumulados (Codigo int not null, Cantidad int, Totalprecio
money)
go
-- Ahora el disparador para que al insertar en detalles

-- En esta primera versión del trigger haremos solo la inserción....

create trigger trg_Detalles on detalles
for insert,update,delete -- Actuaré se haga lo que se haga desde este
trigger
as
-- Primero actualizamos los valores de los que ya existen si se está
insertando
-- Si se esta borrando inserted estará vacia y no se verá afectado ningun
registro.

-- Necesitaré una tabla temporal, ya que no puedo poner group by en la
select
-- de un update y necesito acumular los totales de inserted..

Select Codigo,Sum(Cantidad) Cantidad,Sum(cantidad*Precio) precio
into #insertados
From inserted
group by codigo

update acumulados
set Acumulados.cantidad= Acumulados.cantidad +insertados.cantidad ,
Acumulados.TotalPrecio = Acumulados.TotalPrecio + insertados.precio
from #insertados insertados inner join acumulados on acumulados.codigo
=insertados.codigo
-- Ahora para los que no existan insertaremos sus valores en la tabla de
acumulados
-- Lo haremos haciendo left join ya que generalmente suele ser más rápido
-- que un not in o not exists
insert into acumulados
select
inserted.Codigo,Sum(inserted.Cantidad),sum(inserted.precio*inserted.cantidad
)
from inserted left outer join Acumulados on inserted.codigo =
acumulados.codigo
where acumulados.codigo is null
group by inserted.codigo

go

insert into detalles values ('Primer elemento',1,1,1500)
go
select * from acumulados
-- y el resultado es ...
--Codigo Cantidad Totalprecio
----------- ----------- ---------------------
--1 1 1500.0000

-- (1 row(s) affected)

go
insert into detalles values ('10 Unidades del primer elemento',1,10,1500)
go
select * from acumulados
-- y el resultado es
--Codigo Cantidad Totalprecio
----------- ----------- ---------------------
-- 1 11 16500.0000

-- De momento hemos comprobado la inserción de un único elemento no
existente,
-- y la inserción de un elemento que ya estaba en la tabla de acumulados y
es correcta

-- Ahora hagamos una insercción masiva.
-- aunque antes metamos algún artículo mas..

insert into detalles values ('10 Unidades del segundo elemento',2,10,1500)
go

insert into detalles select descripcion,codigo,cantidad,precio from detalles
go
select * from acumulados
go
-- Y el resultado es ...
--Codigo Cantidad Totalprecio
----------- ----------- ---------------------
--1 22 33000.0000
--2 20 30000.0000

-- Ahora terminaremos el trigger con los detalles
-- de borrado y lo probaremos en borrado y actualización ...

alter trigger trg_Detalles on detalles
for insert,update,delete -- Actuaré se haga lo que se haga desde este
trigger
as

-- Esta parte sigue igual ...
-- Primero actualizamos los valores de los que ya existen si se está
insertando
-- Si se esta borrando inserted estará vacia y no se verá afectado ningun
registro.

-- Necesitaré una tabla temporal, ya que no puedo poner group by en la
select
-- de un update y necesito acumular los totales de inserted..

Select Codigo,Sum(Cantidad) Cantidad,Sum(cantidad*Precio) precio
into #insertados
From inserted
group by codigo

update acumulados
set Acumulados.cantidad= Acumulados.cantidad +insertados.cantidad ,
Acumulados.TotalPrecio = Acumulados.TotalPrecio + insertados.precio
from #insertados insertados inner join acumulados on acumulados.codigo
=insertados.codigo

-- Ahora para los que no existan insertaremos sus valores en la tabla de
acumulados
-- Lo haremos haciendo left join ya que generalmente suele ser más rápido
-- que un not in o not exists

insert into acumulados
select
inserted.Codigo,Sum(inserted.Cantidad),sum(inserted.precio*inserted.cantidad
)
from inserted left outer join Acumulados on inserted.codigo =
acumulados.codigo
where acumulados.codigo is null
group by inserted.codigo

-- Ahora la parte de eliminación. No es un copy-paste, pero casi....

-- También necesito tener los acumulados ya que si borro más de uno
-- Tengo que hacer un update masivo y necesito lo totales, al no poder
-- hacer un group by en la update pues tengo que hacer esto ...

Select Codigo,Sum(Cantidad) Cantidad,Sum(cantidad*Precio) precio
into #Eliminados
From Deleted
group by codigo

update acumulados
set Acumulados.cantidad= Acumulados.cantidad - eliminados.cantidad ,
Acumulados.TotalPrecio = Acumulados.TotalPrecio - eliminados.precio
from #Eliminados eliminados inner join acumulados on acumulados.codigo =
eliminados.codigo

-- Por precaución debería hacer la insert en negativo de los que no
existiesen
-- pero la lógica dice que no se pueden borrar filas que no se han grabado
¿Nó?
go

-- primero borremos un elemento del código 1 que tiene 1 unidad
delete from detalles where id=1
go
select * from acumulados
go
-- y el resultado es ..
--Codigo Cantidad Totalprecio
------------- ----------- ---------------------
--1 21 31500.0000 Correcto 1 menos..
--2 20 30000.0000

-- ahora un borrado masivo..
delete from detalles
go
select * from acumulados
-- y el resultado es
--Codigo Cantidad Totalprecio
------------- ----------- ---------------------
--1 0 .0000
--2 0 .0000

-- Bueno ¿las actualizaciones funcionarán bien?...

insert into detalles values ('10 Unidades del primer elemento',1,10,1500)
insert into detalles values ('Otras 10 Unidades del primer
elemento',1,10,1500)
insert into detalles values ('10 Unidades del segundo elemento',2,10,1500)
go
select * from acumulados
go
-- resultado
--Codigo Cantidad Totalprecio
----------- ----------- ---------------------
--1 20 30000.0000
--2 10 15000.0000
update detalles set cantidad=cantidad*2 where codigo=1
go
select * from acumulados
go
--Codigo Cantidad Totalprecio
------------- ----------- ---------------------
--1 40 60000.0000
--2 10 15000.0000

update detalles set cantidad=0
go
select * from acumulados
go
--Codigo Cantidad Totalprecio
------------- ----------- ---------------------
--1 0 .0000
--2 0 .0000
delete from detalles
go
select * from acumulados
go
--Codigo Cantidad Totalprecio
------------- ----------- ---------------------
--1 0 .0000
--2 0 .0000

[Adrian Garcia]

En mi opinion personal lo que lograrias es lo siguiente:

a) Buena performance en las lecturas que necesiten el valor acumulado
b) Pesima perfomance en tus actualizaciones. Por cada fila que insertes
(seguramente la operacion mas abitual) en la tabla de detalle debera ademas
actualizar la fila correspondiente a la tabla de cabecera.

Creo que podrias tener una decente perfomance en las lecturas y una buena
perfomance en las inserciones si defines correctamente las claves primarias,
si por ejemplo, defines tu clave primaria de la tabla detalle incluyendo en
su primera columna al id de la columna de la cabecera, SQL Server definira
un indice agrupado (cluster) sobre la clave primaria, con lo cual, todas las
filas pertenecientes a un fila de cabecera estaran fisicamente contiguas
reduciendo al maximo la cantidad de lecturas de paginas necesarias para
obtener el total.

Saludos
Adrian D. Garcia
NDSoft

"Yovanny Rojas" <yovann...@hotmail.com> wrote in message
news:eDarzut0...@TK2MSFTNGP09.phx.gbl...

[SqlRanger]

Yo creo que sería mucho mejor dejar a un lado los triggers e implementar los acumulados mediante una vista indexada. Al final el efecto es el mismo que el conseguido mediante una tabla de acumulados y triggers, pero es mucho más sencillo y eficiente:

 

CREATE TABLE Detalles (

 id int identity(1,1) not null primary key,
 Descripcion varchar(80) not null,

 Codigo int not null ,
 Cantidad int not null,
 Precio money not null
)

 

GO

 

CREATE VIEW Acumulados WITH SCHEMABINDING
AS
 SELECT COUNT_BIG(*) As Detalles, Codigo, SUM(Cantidad) AS Cantidad, SUM(Cantidad * Precio) As TotalPrecio
 FROM dbo.Detalles
 GROUP BY Codigo

 

GO

 

CREATE UNIQUE CLUSTERED INDEX IdxAcumulados ON Acumulados(Codigo)

 

GO

--

Saludos:

 

Jesús López
MVP Microsoft .NET

 

"No darás tropezón ni desatino que no te haga adelantar camino"

 

 

"Yovanny Rojas" <yovann...@hotmail.com> escribió en el mensaje news:eDarzut0...@TK2MSFTNGP09.phx.gbl...

[Adrian Garcia]

Recordemos que esta solucion solamente funciona en la version SQL Server
2000 Enterprise y SQL Server Developer.

En mi opinion, el costo de mantener un indice cluster adicional generaria
una sobrecarga superior a la de la ejecucion de los triggers en algunos
casos, analicemos las opciones
:
a) Cuando se insertaria una fila cabecera se deberia agregar una entrada al
indice cluster que mantiene la vista, en ese punto en la solucion con
triggers no haria falta.

b) Cuando se insertaria, eliminaria una fila en el detalle tambien se
debería actualizar la fila (y consecuentemente la pagina) en donde se
encuentra la fila en la vista indexada, similar a la solucion con triggers a
nivel de costo

c) Es necesario espacio en la base de datos para el nuevo indice

d) En las lecturas de las cabecera con los totales tendria un rendimiento
similar a la solucion con triggers a menos que:
1) La cantidad de columnas que definimos en la vista sea menor que la
cantidad de columnas definidas en la vista. Si son iguales o mayor la
cantidad de filas que entrarian por pagina seria igual o inferior a la de la
tabla base, con lo cual una index scan tardaría los mismo
2) Definimos como clave del indice cluster de la vista columnas que no
son claves primarias y que son usadas frecuentemente como parametros de
busqueda, especialmente si se definen rangos (por ejemplo rango de fechas).

e) Cuando se modifica algun monto en la tabla detalle el rendimiento de esta
operacion es similar a la de la opcion con triggers.

Saludos

Adrian D. Garcia
NDSoft

"SqlRanger" <sqlran...@mvps.org> wrote in message
news:uVz5lnw0...@TK2MSFTNGP09.phx.gbl...

[SqlRanger]

Adrián,

 

A partir del Service Pack 3, las vistas indexadas están disponibles en todas las ediciones de SQL Server, incluída la MSDE.

 

En mi opinión las soluciones basadas en vistas indexadas tienen un coste similar a las soluciones basadas en tabla adicional + triggers tanto en procesamiento como en espacio. Sin embargo el código que tienen esos  triggers al crear y usar tablas temporales da la impresión de ser menos eficiente que el procesamiento interno que hace SQL Server de la vista indexada.

 

Además no sólo hay que considerar el espacio y el rendimiento, una solución basada en vistas indexadas es mucho más sencilla y mantenible que la basada en tabla adicional + triggers.

[Miguel Egea]

Bueno, como el código objeto de crítica es mio, me permito opinar :-d

 

Este trigger tendrá la mitad de rendimiento (según mis pruebas con un motón de datos) que cualquier vista indexada y admitirá menos paralelismo. Por lo que en resumen estoy con Jesús, si se puede no hay que usar triggers.

 

En cuanto al espacio, datos son datos, si hay que almacenarlos ocuparan el mismo espacio acumulados en vistas indexadas que en una tabla de acumulados.

 

Por otra parte mi trigger (que esta hecho a efectos educativos), controla los bloqueos y la concurrencia solo en parte, una actualizacio´n masiva podría provocar resultados 'inesperados' sin el correcto tratamiento de bloqueos y transacciones. Si tiramos por la calle de enmedio y bloqueamos mucho seguramente acabemos serializando las inserciones y eso si que sería tremendo. Las vista indexadas no tienen problemas (para nosotros al menos) en esa gestión.

 

En resumen, un trigger mejor si lo evitamos

 

En cuanto a las vistas indexadas...

 

Los comandos SET que hay que establecer antes, (podéis mirar los books on line), tienen que estar establecidos igual en todos los sps que hagan cualquier actualización sobre la base de datos. He visto bastante frecuentemente que si los sp's se modifican con el administrador corporativo, estos comandos set, no están establecidos como necesitan las vistas (debe ser solo cuestión de configuracio´n, pero por defecto no están bien.).  Por lo que si se hace así nuestra aplicación puede dar unos errores bastante curiosos sin que 'aparentemente' nada se haya modificado. Sobre todo si alguien curioseando tus sps pulsa aceptar en lugar de cancelar.....

 

Por todo lo demás me gustan mucho y dan muy buen rendimiento....

 

En cuanto a mi trigger y el rendimiento.

 

El hecho de usar tablas temporales en lugar de subselects no deja de ser por claridad en el código, en cualquier caso no creo que perjudiquen sensiblemente el rendimiento. Podrían usarse variables de tipo tabla, pero al final hacen lo mismo, es decir se guardan en tempdb ....

 

--
=================
Miguel Egea
http://www.portalsql.com
Microsoft SQL-SERVER MVP.
Brigada Anti-Cursores
Aviso de Seguridad
http://www.microsoft.com/spain/technet/seguridad/boletines/MS03-039-USER.asp
==================

 

 

 

"SqlRanger" <sqlran...@mvps.org> escribió en el mensaje news:uqKRAL20...@TK2MSFTNGP09.phx.gbl...

[Adrian Garcia]

Coincido contigo de que hay que evitar utilizar triggers. Ademas del costo
de rendimiento que generan estan los costos de mantenimiento a lo largo de
la vida del sistema. He visto en muchas oportunidades como el conocimiento
de que existan triggers sobre una tabla determinada se pierda (los
desarrolladores cambian, no existe documentacion sobre los mismo, el
desarrollador se olvido por completo, etc, etc) y despues se generen efectos
colaterales imprevistos ante modificaciones que se realizan.

Ahora respecto al espacio que ocuparia una vista indexada es muy superior al
de agregar una columna en la tabla cabecera. Por ejemplo, tenemos una tabla
que ocupa, digamos, unas 100 paginas de 8 KB del motor. Si sobre esa misma
tabla materializo una vista indexada que incluya a las mismos campos mas un
campo de totales entonces en mi base de datos necesitare 100 paginas
adicionales mas el espacio necesario para la columna nueva por el indice
cluster que la vista indexada genera.

Saludos
Adrian
"Miguel Egea" <migue...@online.telefonica.net> wrote in message
news:%23gFtAJK...@tk2msftngp13.phx.gbl...