Possibly a "what sort of cigarette" is that question. Just testing out my reverse engineered db creation script with log messages I noticed that the longest operation was the creation of the triggers and knowing there's a wadge of surplus white space and CRLSs I wondered is it anything like HTML where getting rid of the excess white space etc would make them faster?

Roy Lambert

Roy,

<< Possibly a "what sort of cigarette" is that question. Just testing out my
reverse engineered db creation script with log messages I noticed that the
longest operation was the creation of the triggers and knowing there's a
wadge of surplus white space and CRLSs I wondered is it anything like HTML
where getting rid of the excess white space etc would make them faster? >>

You can try it, but I doubt that it will bring you much extra performance.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>You can try it, but I doubt that it will bring you much extra performance.

What do you reckon the scales will do weighing legibility Vs extra performance?

Roy Lambert

Roy,

<< What do you reckon the scales will do weighing legibility Vs extra
performance? >>

I'd personally go for legibility.  Not being able to read your own triggers
isn't really worth a few extra milliseconds.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'd personally go for legibility. Not being able to read your own triggers
>isn't really worth a few extra milliseconds.

Me too most of the time, but when that few extra milliseconds builds up by multiplying by a few thousand or more I start to wonder.

I'm over the moon about the DISABLE TRIGGER functionality (it would have been over the sun if there was a global one). With my audit log triggers enabled a block operation (eg import) got bombed because it was taking to long. Disable the triggers and it went down to a few seconds. Brilliant, and with a utility program (just pass in the name of the table) not a lot or work.

Roy Lambert

Roy,

<< I'm over the moon about the DISABLE TRIGGER functionality (it would have
been over the sun if there was a global one). >>

A global what ?  DISABLE TRIGGER statement ?

<< With my audit log triggers enabled a block operation (eg import) got
bombed because it was taking to long. Disable the triggers and it went down
to a few seconds. Brilliant, and with a utility program (just pass in the
name of the table) not a lot or work. >>

Cool.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>A global what ? DISABLE TRIGGER statement ?

You guessed My suggested syntax

DISABLE ALL TRIGGERS FOR TABLE
DISABLE ALL TRIGGERS FOR DATABASE
JUST DISABLE EVERYTHING

ENABLE ALL TRIGGERS FOR TABLE
ENABLEALL TRIGGERS FOR DATABASE
WOOPS SHOULDN'T HAVE DONE THAT

Roy Lambert

Roy,

<< You guessed My suggested syntax >>

I'll see what I can do, but this stored procedure will do what you want.
Just pass NULL or '' in as the TableName to disable all triggers in the
database.

CREATE PROCEDURE DisableTriggers(TableName VARCHAR)
BEGIN
  DECLARE TempCursor CURSOR FOR TempStatement;
  DECLARE TempTableName VARCHAR;
  DECLARE TempTriggerName VARCHAR;

  IF (COALESCE(TableName,'') <> '') THEN
     PREPARE TempStatement FROM 'SELECT * FROM Information.Triggers WHERE
TableName=?';
     OPEN TempCursor USING TableName;
     FETCH FIRST FROM TempCursor (Name) INTO TempTriggerName;
     WHILE (NOT EOF(TempCursor)) DO
        EXECUTE IMMEDIATE 'DISABLE TRIGGER '+TempTriggerName+' ON
'+TableName;
        FETCH NEXT FROM TempCursor (Name) INTO TempTriggerName;
     END WHILE;
  ELSE
     PREPARE TempStatement FROM 'SELECT * FROM Information.Triggers';
     OPEN TempCursor USING TableName;
     FETCH FIRST FROM TempCursor (TableName, Name) INTO TempTableName,
TempTriggerName;
     WHILE (NOT EOF(TempCursor)) DO
        EXECUTE IMMEDIATE 'DISABLE TRIGGER '+TempTriggerName+' ON
'+TempTableName;
        FETCH NEXT FROM TempCursor (TableName, Name) INTO TempTableName,
TempTriggerName;
     END WHILE;
  END IF;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I see, skim reading posts again eh. You missed "and with a utility program (just pass in the name of the table)" <vbg>

Roy Lambert

Roy,

<< I see, skim reading posts again eh. You missed "and with a utility
program (just pass in the name of the table)" <vbg> >>

Yep, I missed that.

But, it's still useful to have out there for others.

--
Tim Young
Elevate Software
www.elevatesoft.com