Re: Performance question on triggers

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Quentin Ran (ab_at_who.com)
Date: 04/07/04 

Date: Wed, 7 Apr 2004 11:28:20 -0500

Bernd,

Not sure I fully understand what you want to achieve.

you can create a separate trigger just for updating the x column using the
IF UPDATE ( column ) [{ AND | OR } UPDATE ( column )] condition in the
create trigger code (see create trigger in Books Online)

why do you use "set nocount off"? That could be the reason of the bad
performance.

Quentin

"Bernd Maierhofer (dato)" <bernd.maierhofer@dato.at> wrote in message
news:Oty#30JHEHA.3700@TK2MSFTNGP09.phx.gbl...
> Hi and thanks for reading:
>
> MSSQL 2000 Given a table
>
> Table A
> id integer
> x,a,b,c float
>
> x is calclulated from the values a,b,c of the rows < id id is the primary
> key
>
> Up to now I calculated x at runtime using a function:
>
> select id,a,b,c,f_func(id) as x f_func(id) basically is a select sum(a)
> where id < @id
>
> But as the table grows, this is too slow. As changes to a,b,c usually
occur
> for the newest records only, I wanted to switch to using a field x wich is
> only calculated when a,b,c for one of the preceeding rows is changed. Thus
> by using a trigger:
>
> create trigger xx on a
> after insert, update, delete as
>
> set nocount off
> IF ( (SELECT trigger_nestlevel( object_ID('lsmp') ) ) = 1 ) begin
> update a set x = f_func(id) where id in (select id from deleted)
> update a set x = f_func(id) where id in (select id from inserted)
> end
> set nocount on
> end
>
> The performance is very poor, as the trigger is fired recursively. One
> option is to disable recursive and/or nested triggers. Is it possible to
> issue a
>
> sp_configure 'nested_triggers','0' within the trigger? And would this
> inhibit firering the trigger each time, I update the table?
>
> Any idea? Any different approach?
>
> TIA
> Bernd
>
>

Relevant Pages

  • Re: Whats wrong with this trigger
    ... I actually had SET NOCOUNT OFF at the top, ... >>table that has the trigger is control data, so the results that are moved ... >>would scrap the Access app at this point, but I don't have time for the ... and the app that uses this data is .NET. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Edit conflicts between table and trigger
    ... Failing to issue SET NOCOUNT ON results in ADP's confusing the row count message as the result set. ... make sure the trigger does a 'set nocount on' first thing ... I am in the process of migrating a very complex mdb/mde to ADP. ... stage being migration of the backend and some core functionaility to MSDE/mde and the second migrating the remaining ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SET NOCOUNT ON - am I doing this wrong?
    ... > place it in the trigger if you also had to place it around every call ... by running a set nocount on one time when you connect to the server. ... create trigger trigtest_ins on trigtest ... exec trigtest_sp ...
    (microsoft.public.sqlserver.server)
  • Can i reuse the code in triggers??
    ... each of them has a Delete trigger ... Delete rr from ResourceRating rr ... Set NoCount Off ... W. Jordan ...
    (microsoft.public.sqlserver.programming)
  • Re: Fehlerbehandlung
    ... Den Fehler aus dem Trigger kannst Du auch ohne PRobleme aus dem gewöhnlichen ... SET NOCOUNT OFF ... Fehlermeldung in ACCESS angezeigt wird. ...
    (microsoft.public.de.access.clientserver)