I really need help on this one.
I have a database that has several tables and stored procedures to perform some data storage.
Each day the stored procedure below is called to pack data in some tables. This is done by packing some rows into one row and delete some data that is no longer needed after the time specified as an argument to "PASSIVE_PURGE"
My problem is that the procedure takes far to long time, and since it's a stored procedure, the calling application freezes while it waits for the procedure to complete.
I need to optimize the following stored procedure in Interbase, or, in some way process windows messages on the host.
The database I am using has the following indexies.
CREATE INDEX MEW1 ON MEASURE (WashID, DateTime);
CREATE INDEX MED1 ON DRYMEASURE (DryID, DateTime);
CREATE INDEX WAS1 ON WASHES (WashID, MachineID, DateTime);
CREATE INDEX DRY1 ON DRYINGS (DryID, MachineID, DateTime);
Will the procedure spped up if there is no index that needs to be updated during the call?
Or is there any other way I can spped up this procedure?
/* PASSIVE_PURGE - Removes and packs data older than PDATE */
CREATE PROCEDURE PASSIVE_PURGE (PDATE Date)
DECLARE VARIABLE WashID Integer;
DECLARE VARIABLE MachineID Smallint;
DECLARE VARIABLE DateTime Date;
DECLARE VARIABLE TotalHourCnt Integer;
DECLARE VARIABLE IdleHourCnt Integer;
DECLARE VARIABLE RunHourCnt Integer;
DECLARE VARIABLE ServiceHourCnt Integer;
DECLARE VARIABLE PrmNum Smallint;
FOR SELECT MachineID, :PDATE DateTime, SUM(TotalHourCnt) TotalHourCnt, SUM(IdleHourCnt) IdleHourCnt,
SUM(RunHourCnt) RunHourCnt, SUM(ServiceHourCnt) ServiceHourCnt, PrmNum FROM HOURDATA WHERE DateTime < :PDATE GROUP BY MachineID, PrmNum
INTO :MachineID, :DateTime, :TotalHourCnt, :IdleHourCnt, :RunHourCnt, :ServiceHourCnt, :PrmNum
DELETE FROM HOURDATA WHERE MachineID = :MachineID AND PrmNum = :PrmNum AND DateTime < :DateTime;
INSERT INTO HOURDATA VALUES (:MachineID, :DateTime, :TotalHourCnt, :IdleHourCnt, :RunHourCnt, :ServiceHourCnt, :PrmNum);
FOR SELECT WashID FROM WASHES WHERE DateTime < :PDATE INTO :WashID
DELETE FROM MEASURE WHERE WashID = :WashID;
DELETE FROM PVMEASURE WHERE WashID = :WashID;
DELETE FROM WASHES WHERE DateTime < :PDATE;