November 1, 2004 at 12:13 am
Just a beginner but facing a big problem.
I have a Database with 28 tables and each table has approximately 7 columns.
Through ODBC each table is updated with new values each 15 second.
The first column of each table contains an integer value representing a time value since
1980-01-01 00:00:00. Each table has a clustered index on this integer value.
We don’t’ want to lose any data. We implemented a trigger on insert for each table, like:
USE DECON
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'scroll_AFVAL'
AND type = 'TR')
DROP TRIGGER scroll_AFVAL
GO
CREATE TRIGGER [scroll_AFVAL] ON [dbo].[AFVAL]
FOR INSERT
AS
WHILE (SELECT COUNT(*) FROM [dbo].[AFVAL]) > 178560
BEGIN
INSERT INTO BDECO.[dbo].[AFVAL]
SELECT * FROM [dbo].[AFVAL] WHERE
(SECTIME=(SELECT MIN(SECTIME) FROM [dbo].[AFVAL]))
DELETE FROM [dbo].[AFVAL] WHERE
(SECTIME=(SELECT MIN(SECTIME) FROM [dbo].[AFVAL]))
END
GO
In the backup database BDECO values are stored from the original database DECON when the total count of records > 178560.
When these triggers are activated, performance is very bad; the log file says every 15 seconds the database BDECO is restarted.
How can we solve this problem??
Many thanks in advance,
Kees de Ruijter
November 1, 2004 at 11:31 am
Can you expand upon the business purpose of what this trigger is doing? Why is the number 178560 important? Why are you re-inserting rows into the table, then deleting them? Can you post some sample data that illustrates what this change accomplishes?
--
Adam Machanic
whoisactive
November 2, 2004 at 12:59 am
Adam,
The database DECON is in use as a real-time database. The number 178560 is related to 31 days (each 15 seconds DECON is inserted with new records in the 28 tables.
The database BDECO has also 28 tables and the columns are the same as DECON. Each table has this clustered index SECTIME.
On each table in DECON we use the same trigger except [AFVAL] has a different name according to the name of the database table.
The purpose of the triggers is to keep the database DECON the same size (500 MB) and to hold the "oldest" records in database BDECO. Without the triggers DECON will grow and grow.
For example [AFVAL] has columns SECTIME, a column flow rate and a column pressure.
I hope this will explain more
November 2, 2004 at 7:29 am
Databases can be configured to automatically shut down when all connections to it have been dropped. Based on your first post it sounds like the BDECO database is configured in that fashion. Change it to stay running even when no connections exist. Basically, I am guessing that every 15 seconds a trigger does an implicate connection to BDECO which starts the database, performs the work, then that connection is broken causing the database to shut down again.
November 2, 2004 at 10:02 am
Regardless of whether or not auto close is set, that loop in your trigger is extremely expensive, and will happen on every INSERT. That's going to slow things down, a lot. Have you considered elmininating the trigger and doing this from a scheduled job? Your job could execute once every 15 minutes, or at whatever interval you want that makes sense; this would allow inserts to happen at a much higher rate while still accomplishing your goals of keeping the size down.
--
Adam Machanic
whoisactive
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply