September 28, 2016 at 8:21 am
Hello community!
In the database I have i table where I record alarms from a connected PLC. I have the following columns defined:
ID (int)
eventTime (datetime, getdate() as default)
alarmNumber (int)
alarmState (bool)
When the program starts all PLC variables are transmitted once so that I have the current variable values. During this procedure, all alarms are received and the state is recorded in the database. This makes a lot of duplicates in the table.
To avoid the duplicates, I think I have the following options:
1) If a new alarm is received, check the state, get the last inserted value and compare the alarm states. If they are different, insert the new data into the database.
Easy to handle, but performance degradiation because of database roundtrips. Blocking of the application.
2) Select all alarms from their last insert date in a single statement or procedure. Load the result into a local dictionary. When a new alarm event is raised, check if the alarm is in the dictionary and compare the states. If the states are different, insert the new alarm into the database.
Delete the entry form the local dictionary.
ad 2) I could make a sp where I first execute a distinct select for the alarm numbers. Then I iterate over the result set and insert the last inserted data into a temp tabel. When the loop is finished, I select the data from the temp table and return the data.
But I do not like the idea, I think there are better ways that I have not found yet.
Thank you for your ideas.
Best regards
Alex
September 28, 2016 at 8:31 am
Alex
I've no idea what a PLC is, but I think I can answer your question without knowing! If I were you, I'd put all the PLC variables into a staging table in the first instance. Once they're in there, you can write a SELECT statement that returns only one row per variable (or whatever you need), and insert those results into your main table. Once you've done that, you can discard the data in the staging table.
John
September 28, 2016 at 8:47 am
PLC - Programmable Logic Controller.
September 28, 2016 at 12:56 pm
Option (1) should be fine as long as the trigger is efficiently written and there is an index to support the trigger's query. For example, if the table is clustered on ( alarmNumber, ID ) rather than on just ID.
CREATE TRIGGER table_name__trg_insert
ON table_name
INSTEAD OF INSERT
AS
INSERT INTO table_name
SELECT i.eventTime, i.alarmNumber, i.alarmState
FROM inserted i
OUTER APPLY (
SELECT TOP (1) *
FROM table_name tn
WHERE tn.alarmNumber = i.alarmNumber
ORDER BY ID DESC
) AS prev_alarm
WHERE prev_alarm.alarmState <> i.alarmState OR prev_alarm.alarmState IS NULL
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 29, 2016 at 12:35 am
@Lynn Pettis: Thank you for the explanation of the PLC, I should have added the explanation in my first post.
@john-2 Mitchell-245523
Once they're in there, you can write a SELECT statement that returns only one row per variable (or whatever you need), and insert those results into your main table.
Thats the problem, I cannot exactly say when all alarm variables have been read. There is no fixed reading order during the initialisation of the connection to the PLC.
I had hoped that there would be a neater solution. Thank you for the hints.
PLC Explanation
PLC (Programable Logic Controler) are controllers interacting with sensors (light barriere, temperature, ...) and actuators (e.g. motor). They are used on production machines or in facility automation environments. The variables reflect work steps and/or machine/environment conditions e.g production charge finished, humidity, pressure ...
The program I make is the connection to higher management systems (e.g. ERP). I read the data from the work preparation department, transform and transmit the data to the PLC and the next charge could be produced.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply