Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

ADM2 SmartDataObject slow saving record after an update.

Saving a change to a record is very slow.

Record numbers up to 1000 the performance is good. Records above 10000 the performance is bad.

BeginTransactionValidate and EndTransactionValidate procedures performing extra processing for updates. For example:


/* BeginTransactionValidate */
/* Check if there is a history record of Firma, if not create one */
FOR EACH RowObjUpd WHERE RowObjUpd.RowMod = "U":
FIND FIRST Firma NO-LOCK WHERE STRING(ROWID(Firma)) =
RowObjUpd.Rowident NO-ERROR.
IF AVAILABLE Firma AND Firma.Firma <> "" AND
Firma.Firma <> RowObjUpd.Firma THEN DO:
CREATE FirmaHist.
ASSIGN
FirmaHist.Firma = Firma.Firma
FirmaHist.Nr = Firma.Nr
FirmaHist.Opphort = TODAY
FirmaHist.OrgNr = Firma.Orgnr.
END.
END.

/* EndTransactionValidate */
/* Assign a signature and a date as "changing" information */
FOR EACH RowObjUpd WHERE RowObjUpd.RowMod = "U":
FIND FIRST Firma WHERE STRING(ROWID(Firma)) = RowObjUpd.Rowident
NO-ERROR.
IF AVAILABLE Firma THEN
ASSIGN
RowObjUpd.Endret = TODAY
Firma.Endret = TODAY
RowObjUpd.Sign = vBrukernavn
Firma.Sign = vBrukernavn.
END.

CAUSE:

There is a sequential read through the Firma table in each of the validation procedures because of the STRING function performed on the ROWID. For example:

STRING(ROWID(Firma))

Since there is a STRING function converting the ROWID to a character string in order to do the equality check, the function must be evaluated before the equality check is made. This means every record must be read to convert the ROWID to a string, so the record cannot be found directly by navigating to it by the ROWID.

FIX:

Using function calls in a WHERE clause is almost always a bad idea to avoid this kind of sequential read. To avoid the problem use other fields in the WHERE clause that do not need functions performed on them to access the record directly. For example:


FIND FIRST Order WHERE Order.ordernum = RowObjUpd.Ordernum NO-ERROR.

Or use the TO-ROWID function to convert the RowObjectUpd.RowIdent character field to a ROWID. For example:

FIND FIRST Firma WHERE ROWID(Firma) = TO-ROWID(RowObjUpd.Rowident) NO-ERROR.