Trigger problem

28 Jul 2005 11:14 AM

Fred

Hi everybody!

I'm trying to upgrade my app's on sql2005. Everything work's fine except
triggers on a huge tables with more then 30 millions of rows. It takes more
then 10 min but on sql2000 on the same computer it takeâ€™s 1 second.

I have trigger on each table and this triggerâ€™s are for insert and update
and they are updating two fields who add or change the row and when

My version of SQL2005 9.00.1116.00

Here is the example of trigger:

alter TRIGGER [dbo].[tr_SLOSLPIU] ON [dbo].[SLOSLP]
FOR INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE SLOSLP
    SET SLP_UDO = CASE WHEN SLOSLP.SLP_UDO IS NULL
                       THEN SYSTEM_USER
                       ELSE SLOSLP.SLP_UDO
                END,
        SLP_CDO = CASE WHEN SLOSLP.SLP_CDO IS NULL
                       THEN CURRENT_TIMESTAMP
                       ELSE SLOSLP.SLP_CDO
                END,
        SLP_USP = SYSTEM_USER,
        SLP_CSP = CURRENT_TIMESTAMP
    FROM INSERTED
    WHERE INSERTED.SLG_SIF = SLOSLP.SLG_SIF
    AND INSERTED.PON_SIF = SLOSLP.PON_SIF
    AND INSERTED.VRS_IZP = SLOSLP.VRS_IZP;
END;

Can someone give me an explanation ??

Thankâ€™s Fred

28 Jul 2005 12:09 PM

ML

If it came in a box, the box would say: this is Beta - full functionality not
included. Look for answers on the SQL 2005 newsgroup.

ML

28 Jul 2005 12:25 PM

Jens SÃ¼ÃŸmeyer

make sure the same index are defined as on the sql 2000 box.

HTH, Jens Suessmeyer.

Show quote

"Fred" wrote:

> Hi everybody!
>
> I'm trying to upgrade my app's on sql2005. Everything work's fine except
> triggers on a huge tables with more then 30 millions of rows. It takes more
> then 10 min but on sql2000 on the same computer it takeâ€™s 1 second.
>
> I have trigger on each table and this triggerâ€™s are for insert and update
> and they are updating two fields who add or change the row and when
>
> My version of SQL2005 9.00.1116.00
>
> Here is the example of trigger:
>
>
> alter TRIGGER [dbo].[tr_SLOSLPIU] ON [dbo].[SLOSLP]
> FOR INSERT,UPDATE
> AS
> BEGIN
>     SET NOCOUNT ON;
>
>     UPDATE SLOSLP
>     SET SLP_UDO = CASE WHEN SLOSLP.SLP_UDO IS NULL
>                        THEN SYSTEM_USER
>                        ELSE SLOSLP.SLP_UDO
>                 END,
>         SLP_CDO = CASE WHEN SLOSLP.SLP_CDO IS NULL
>                        THEN CURRENT_TIMESTAMP
>                        ELSE SLOSLP.SLP_CDO
>                 END,
>         SLP_USP = SYSTEM_USER,
>         SLP_CSP = CURRENT_TIMESTAMP
>     FROM INSERTED
>     WHERE INSERTED.SLG_SIF = SLOSLP.SLG_SIF
>     AND INSERTED.PON_SIF = SLOSLP.PON_SIF
>     AND INSERTED.VRS_IZP = SLOSLP.VRS_IZP;
> END;
>
>
> Can someone give me an explanation ??
>
>
> Thankâ€™s Fred
>
>

28 Jul 2005 12:37 PM

Fred

Hi!

Off course, the only think Iâ€™ve done is restore databases and I made some
changes on same procedures because backward compatibility

This problem occurs on every table that have many rows....

On the other side everything works fine, except performance problems

Fred

Show quote

"Jens SÃ¼ÃŸmeyer" wrote:

> make sure the same index are defined as on the sql 2000 box.
>
> HTH, Jens Suessmeyer.
>
> "Fred" wrote:
>
> > Hi everybody!
> >
> > I'm trying to upgrade my app's on sql2005. Everything work's fine except
> > triggers on a huge tables with more then 30 millions of rows. It takes more
> > then 10 min but on sql2000 on the same computer it takeâ€™s 1 second.
> >
> > I have trigger on each table and this triggerâ€™s are for insert and update
> > and they are updating two fields who add or change the row and when
> >
> > My version of SQL2005 9.00.1116.00
> >
> > Here is the example of trigger:
> >
> >
> > alter TRIGGER [dbo].[tr_SLOSLPIU] ON [dbo].[SLOSLP]
> > FOR INSERT,UPDATE
> > AS
> > BEGIN
> >     SET NOCOUNT ON;
> >
> >     UPDATE SLOSLP
> >     SET SLP_UDO = CASE WHEN SLOSLP.SLP_UDO IS NULL
> >                        THEN SYSTEM_USER
> >                        ELSE SLOSLP.SLP_UDO
> >                 END,
> >         SLP_CDO = CASE WHEN SLOSLP.SLP_CDO IS NULL
> >                        THEN CURRENT_TIMESTAMP
> >                        ELSE SLOSLP.SLP_CDO
> >                 END,
> >         SLP_USP = SYSTEM_USER,
> >         SLP_CSP = CURRENT_TIMESTAMP
> >     FROM INSERTED
> >     WHERE INSERTED.SLG_SIF = SLOSLP.SLG_SIF
> >     AND INSERTED.PON_SIF = SLOSLP.PON_SIF
> >     AND INSERTED.VRS_IZP = SLOSLP.VRS_IZP;
> > END;
> >
> >
> > Can someone give me an explanation ??
> >
> >
> > Thankâ€™s Fred
> >
> >

28 Jul 2005 10:55 PM

Hugo Kornelis

On Thu, 28 Jul 2005 05:37:06 -0700, Fred wrote:

>Hi!
>
>Off course, the only think I’ve done is restore databases and I made some
>changes on same procedures because backward compatibility
>
>
>This problem occurs on every table that have many rows....
>
>On the other side everything works fine, except performance problems

Hi Fred,

For performance (and other) problems with SQL Server 2005, please post
to the SQL Server 2005 groups. These are actively monitored by the SQL
Server development team. I'm sure they'd love to know about your
performance problem BEFORE the product hits the shelves...

http://www.aspfaq.com/sql2005/show.asp?id=1

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)