pota0311
Član broj: 63065 Poruke: 4 212.200.27.*
|
Evo mene ponovo posle duzeg vremena.
Moram da priznam da se nisam javljao sve cekajuci da nadjem odgovarajuce resenje za problem koji sam opisao u prvom post-u.
Na moju zalost stanje je isto kao i pre, ak ne i gore.
Nista od predlozenih resenja ne pomaze. Jednostavno samo kada uradim alter procedure sve se vrati na normalnu brzinu i to tak radi par sati kada opet naglo uspori. Ako neko ima jos nekih predloga, neka se molim javi.
Drugi problem koji mi se u medjuvremenu pojavio je sledeci:
Imam triger instead of insert na tabeli ALARM_LOG.
ALTER TRIGGER dbo.ALARM_LOG_TRIGG ON dbo.ALARM_LOG
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @timestamp datetime,
@sequence_number integer,
@alarm_id varchar(32),
@alarm_class varchar(5),
@resource varchar(16),
@logged_by varchar(32),
@reference varchar(32),
@prev_state varchar(1),
@log_action varchar(1),
@final_state varchar(1),
@alarm_message varchar(80),
@generation_time datetime,
@project varchar(21),
@openRecords integer;
DECLARE alarm_log_cursor CURSOR FAST_FORWARD FOR
SELECT [timestamp], alarm_id, alarm_class, resource,
logged_by, reference, prev_state, log_action, final_state,
alarm_message, generation_time, project
FROM inserted
OPEN alarm_log_cursor
FETCH NEXT FROM alarm_log_cursor INTO
@timestamp, @alarm_id, @alarm_class, @resource,
@logged_by, @reference, @prev_state, @log_action, @final_state,
@alarm_message, @generation_time, @project
declare @date datetime
declare @workday datetime
declare @h int
declare @min int
declare @shift int
declare @seq_nr int
declare @exact_time datetime --extra logging when record is inserted into alarm_log
SET @exact_time = getdate()
--select @date = max(com_time)from actual_shift_schedule
--select @shift = max(shift_nr) from actual_shift_schedule
--check also area id
select @date = max(com_time)from actual_shift_schedule where area_id IN (select area_id from AREA_ASSIGNMENT where resource_id = @resource)
select @shift = max(shift_nr) from actual_shift_schedule where area_id IN (select area_id from AREA_ASSIGNMENT where resource_id = @resource)
select @h = datepart(hh,@date)
select @min = datepart(mi,@date)
select @workday = DATEADD (hh, -@h,@date)
select @workday = DATEADD (mi, -@min,@workday)
WHILE @@FETCH_STATUS = 0
BEGIN
-- Check for special treatment of system alarms
IF (@alarm_class = '$SYS')
BEGIN
IF (@log_action = 'G')
BEGIN
IF (@alarm_id = '$ALARM.ACTIVE')
BEGIN
-- Close all open alarms of the same project
UPDATE ALARM_LOG
SET log_action = 'R', final_state = 'R'
WHERE (project = @project) and (final_state in ('A', 'G'))
-- Insert the alarm itself for monitoring purpose
INSERT INTO ALARM_LOG
([timestamp], alarm_id, alarm_class, resource,
logged_by, reference, prev_state, log_action, final_state,
alarm_message, generation_time, project)
VALUES
(@timestamp, @alarm_id, @alarm_class, '',
@logged_by, @reference, 'R', 'G', 'R',
@alarm_message, @timestamp, @project)
-- Sven de Bleyser: insert workday and shiftnumber into ALARM_LOG_TABLE
set @seq_nr = @@identity
INSERT INTO ALARM_LOG_DATA
(sequence_number, gen_workday, gen_shift, exact_gen_time)
VALUES
(@seq_nr, @workday, @shift, @exact_time)
-- select * from ALARM_LOG_DATA
-- END
END
IF (@alarm_id = 'ALE_ALIVE')
BEGIN
-- Elongate all open alarms of the same project
-- to the current timestamp
UPDATE ALARM_LOG
SET [timestamp] = @generation_time
WHERE ([timestamp] < @generation_time)
and (project = @project) and (final_state in ('A', 'G'))
END
END
END
-- Ignore comment records, they do not provide any valuable information
ELSE IF (@log_action <> 'C')
BEGIN
-- Simplify the action / state information of the new record
IF (@prev_state in ('A', 'G')) SET @prev_state = 'G'
ELSE SET @prev_state = 'R';
IF (@final_state in ('A', 'G')) SET @final_state = 'G'
ELSE SET @final_state = 'R';
-- Check, whether there is a previous "open" record with the same alarm_id
SELECT @openRecords = COUNT (*)
FROM ALARM_LOG
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))
IF (@openRecords = 0)
BEGIN
-- There is no previous "open" record with the same alarm_id
IF (@prev_state = 'R')
BEGIN
IF (@final_state = 'R')
BEGIN
-- Ignore R -> R transitions
SET @log_action = '';
END
ELSE -- @final_state = 'G'
BEGIN
-- Start of an alarm occurrence
SET @log_action = 'G';
SET @timestamp = @generation_time;
END
END
ELSE -- @prev_state = 'G'
BEGIN
IF (@final_state = 'R')
BEGIN
-- End of an alarm occurrence
SET @log_action = 'R';
END
ELSE -- @final_state = 'G'
BEGIN
-- Continuation, but without a previous start record
SET @log_action = 'G';
END
END
END
ELSE
BEGIN
-- There is already a previous "open" record with the same alarm_id
IF (@prev_state = 'R')
BEGIN
-- We must have missed, that the "open" alarm has been closed in the meanwhile
-- Update and close all "open" records now with the generation_time of the new record
-- SDB get sequence number before update
-- select @seq_nr = sequence_number from alarm_log WITH (NOLOCK)
-- where alarm_id = @alarm_id and final_state in ('A','G')
-- end
UPDATE ALARM_LOG
SET [timestamp] = @generation_time, log_action = 'R', final_state = 'R', @seq_nr = sequence_number
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))
-- Sven de Bleyser: insert closed workday and closed shiftnumber into ALARM_LOG_TABLE
UPDATE ALARM_LOG_DATA
SET closed_workday = @workday, closed_shift = @shift
WHERE (sequence_number = @seq_nr)
-- END
IF (@final_state = 'R')
BEGIN
-- Ignore R -> R transitions
SET @log_action = '';
END
ELSE -- @final_state = 'G'
BEGIN
-- Start of an alarm occurrence
SET @log_action = 'G';
SET @timestamp = @generation_time;
END
END
ELSE -- @prev_state = 'G'
BEGIN
IF (@final_state = 'R')
BEGIN
-- End of an alarm occurrence
-- Update and close all "open" records with the timestamp of the new record
UPDATE ALARM_LOG
SET [timestamp] = @timestamp, log_action = 'R', final_state = 'R', @seq_nr = sequence_number
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))
UPDATE ALARM_LOG_DATA
SET closed_workday = @workday, closed_shift = @shift
WHERE (sequence_number = @seq_nr)
-- END
-- Do not insert a new record
SET @log_action = '';
END
ELSE -- @final_state = 'G'
BEGIN
-- Continuation of a previous start record
-- Update all "open" records with the timestamp of the new record
UPDATE ALARM_LOG
SET [timestamp] = @timestamp, log_action = 'G', final_state = 'G'
WHERE (alarm_id = @alarm_id) and (final_state in ('A', 'G'))
-- Do not insert a new record
SET @log_action = '';
END
END
END
IF (@log_action <> '')
BEGIN
-- Insert the new record
INSERT INTO ALARM_LOG
([timestamp], alarm_id, alarm_class, resource,
logged_by, reference, prev_state, log_action, final_state,
alarm_message, generation_time, project)
VALUES
(@timestamp, @alarm_id, @alarm_class, @resource,
@logged_by, @reference, @prev_state, @log_action, @final_state,
@alarm_message, @generation_time, @project)
set @seq_nr = @@identity
INSERT INTO ALARM_LOG_DATA
(sequence_number, gen_workday, gen_shift, exact_gen_time)
VALUES
(@seq_nr, @workday, @shift, @exact_time)
-- END
END
exec ALM_Conveyors_Update @resource, @alarm_class
END
FETCH NEXT FROM alarm_log_cursor INTO
@timestamp, @alarm_id, @alarm_class, @resource,
@logged_by, @reference, @prev_state, @log_action, @final_state,
@alarm_message, @generation_time, @project
END
CLOSE alarm_log_cursor
DEALLOCATE alarm_log_cursor
END
4 razlicita procesa non stop loguju u tu tabelu. Vrlo cesto imam deadlock na UPDATE ALARM_LOG_DATA delu (update druge tabele iz trigera).
Da li to znaci da se trigeri izvrsavju paralelno na istoj tabeli ?!
Pozdrav,
Predrag
|