Greetings,
I've the following table:
CREATE TABLE STOCK_RECORDS
(
ID INTEGER NOT NULL,
LOCATION SMALLINT,
INSERTUSER SMALLINT DEFAULT 0,
<-- FOREIGN KEY CONSTRAINT
INSERTDATE TIMESTAMP,
EDITUSER SMALLINT DEFAULT 0,
<-- FOREIGN KEY CONSTRAINT
EDITDATE TIMESTAMP,
PRODUCTID INTEGER,
<-- FOREIGN KEY CONSTRAINT
RECORD_DATE TIMESTAMP,
RECORD_TYPE SMALLINT DEFAULT 0,
STORAGE_AREA_ID_SOURCE INTEGER, <--
FOREIGN KEY CONSTRAINT
STORAGE_AREA_ID_TARGET INTEGER, <--
FOREIGN KEY CONSTRAINT
INVENTORYPOSID INTEGER,
<-- FOREIGN KEY CONSTRAINT
PURCHASEORDERPOSID INTEGER,
<-- FOREIGN KEY CONSTRAINT
DELIVERYPOSID INTEGER,
<-- FOREIGN KEY CONSTRAINT
INVOICEPOSID INTEGER,
<-- FOREIGN KEY CONSTRAINT
INSTANCE_TYPE SMALLINT,
INSTANCE_ID INTEGER,
AMOUNT DOUBLE PRECISION,
RESULTING_AMOUNT DOUBLE PRECISION,
NOTES VARCHAR( 255),
STOCK_RELEVANT BOOLEAN
);
ALTER TABLE STOCK_RECORDS ADD CONSTRAINT PK_STOCK_RECORDS PRIMARY KEY(ID);
CREATE GENERATOR STOCK_RECORDS_PRIMARYKEY;
CREATE ASC INDEX STOCK_RECORDS_LOCATION ON STOCK_RECORDS (LOCATION);
CREATE ASC INDEX STOCK_RECORDS_RECORD_DATE ON STOCK_RECORDS (RECORD_DATE);
CREATE ASC INDEX STOCK_RECORDS_RECORD_TYPE ON STOCK_RECORDS (RECORD_TYPE);
CREATE ASC INDEX STOCK_RECORDS_INSTANCE_TYPE ON STOCK_RECORDS (INSTANCE_TYPE);
The domain "BOOLEAN" is defined like this:
CREATE DOMAIN BOOLEAN AS SMALLINT DEFAULT 0 CHECK (VALUE IN (-1, 0)) NOT NULL;
In an update procedure I'm doing the following by using IBOjects:
....
IBOQueryStockRecords.ReadOnly := False;
IBOQueryStockRecords.RequestLive := True;
IBOQueryStockRecords.Close;
IBOQueryStockRecords.SQL.Clear;
IBOQueryStockRecords.SQL.Add('select ID, EDITDATE, PRODUCTID, DELIVERYPOSID as
POSID, abs(AMOUNT) as AMOUNT, RESULTING_AMOUNT from MOV_STOCK_RECORDS where
(INSTANCE_TYPE = 2)');
IBOQueryStockRecords.Open;
if not (IBOQueryStockRecords.Bof and IBOQueryStockRecords.Eof) then begin
with IBCursorResultingStock do begin
Close;
SQL.Clear;
SQL.Add('select sum(AMOUNT) as GESAMTBESTAND from MOV_STOCK_RECORDS
where (ID > 0) and (PRODUCTID = :PRODUCTID) and (STORAGE_AREA_ID_SOURCE =
:STORAGE_AREA_ID_SOURCE) and (STOCK_RELEVANT = -1)');
SQL.Add('and ((DELIVERYPOSID <> :DELIVERYPOSID) or DELIVERYPOSID is
null)');
SQL.Add('and (EDITDATE <= :EDITDATE)');
Prepare;
end;
IBOQueryStockRecords.First;
while not IBOQueryStockRecords.Eof do begin
with IBCursorResultingStock do begin
Close;
ParamByName('PRODUCTID').AsInteger :=
IBOQueryStockRecords.FieldByName('PRODUCTID').AsInteger;
ParamByName('STORAGE_AREA_ID_SOURCE').AsInteger := 1;
ParamByName('DELIVERYPOSID').AsInteger :=
IBOQueryStockRecords.FieldByName('POSID').AsInteger;
ParamByName('EDITDATE').AsDateTime :=
IBOQueryStockRecords.FieldByName('EDITDATE').AsDateTime;
Open;
First;
if not (Bof and Eof) then begin
ResStockAmount := Fields[0].AsFloat;
end;
end;
if IBOQueryStockRecords.FieldByName('RESULTING_AMOUNT').AsFloat <>
ResStockAmount then begin
IBOQueryStockRecords.Edit;
IBOQueryStockRecords.FieldByName('RESULTING_AMOUNT').AsFloat :=
ResStockAmount;
IBOQueryStockRecords.Post;
end;
IBOQueryStockRecords.Next;
end;
end;
....
For ca. 100k records as the result of the select statement of
IBOQueryStockRecords updating the values takes very long. I also tried to use
an IB_Script rather than editing the records directly, but this didn't change
much. Apparently selecting the sum of AMOUNT with IBCursorResultingStock is
what adds in execution time of the procedure the most.
Do you guys see any potential for improvement by adding some indexes or
something?
There was an other procedure that took long, and I was able to drasticaly
improve it by adding one multi-segment index, but all my attempts to do the
same in the case above didn't have much impact so far.
Best regards,Patrick
