max # of triggers?

I'm looking for information on if there is a limit to the number of triggers one can define in IDS 7.3. If there is no limit ... are there any guidelines on what is reasonable. I looking at situ where could have 1000-2000 triggers defined in just one database... I personally think this could cause problems with performance, but can not find any documentation on this topic. Does anyone have any advice, Thanks, Julie

Triggers will certainly affect performance. Unfortunately the nature and extent of the slowdown cannot be determined in any general way. It depends on what the triggers are doing (filtering, copying rows as for an audit trail, running a stored procedure that will query other tables for validation etc. It also depends on what kind of triggers, is the trigger a oneshot or does it fire for every affected row? Certainly 2,000 triggers will be slowing things down noticably. Art S. Kagel Juliann Meyer wrote: > > I'm looking for information on if there is a limit to the number of > triggers one can define in IDS 7.3. If there is no limit ... are there > any guidelines on what is reasonable. I looking at situ where could > have 1000-2000 triggers defined in just one database... I personally > think this could cause problems with performance, but can not find any > documentation on this topic. > > Does anyone have any advice, > > Thanks, > Julie

Juliann Meyer wrote: > > I'm looking for information on if there is a limit to the number of > triggers one can define in IDS 7.3. If there is no limit ... are there > any guidelines on what is reasonable. I looking at situ where could > have 1000-2000 triggers defined in just one database... I personally > think this could cause problems with performance, but can not find any > documentation on this topic. > > Does anyone have any advice, > > Thanks, > Julie Hi Juliann, there's no reasonable limit of triggers. Triggers are stored in a similiar way like tables and the server must always check out, whether it has to fire a trigger or not. As long as you will not create dummy triggers they will not slow down the performance. Poor performance would be the result of the action defined inside the trigger. Generally I would use triggers instead of explicit programs to reduce the client/server communication. To find out the amount of time it takes to communicate between a client and its server, run the following test, and I'm sure you will prefer triggers whereever possible: Use a local shared memory communication for INFORMIXSERVER: time dbaccess sysmaster - <<eof > /dev/null select colno,created from systables a, syscolumns b where a.tabid < 10;eof Run this test several times and get the response time. Try it again and change only the select list. time dbaccess sysmaster - <<eof > /dev/null select * from systables a, syscolumns b where a.tabid < 10;eof The second query takes longer. The reason is not an increase of the data to read from disk ( the dictionary is cached ). Its the amount of data that must be transferred from the server to the client. ( 6KB - 32KB per process switch ). You will get a more detailed result if you would write a small ESQL/C program. If you want to see the time the server needs just to get and send the data, run "onstat -p" immediatley when the query terminates. For both queries it's almost the same processing time. Best regards, -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----