Use DB at 100% without filling the COMMIT-cache

kriki

NATIVE DB-ONLY!!!

The following code can be used to fully occupy the DB, but without filling the COMMIT-cache and blocking other users.

Use function "PerformanceSleep()" just after a COMMIT in your code. This function can be used in SQL without receiving an error-message, but will not do anything.

PerformanceIndication() : Integer
// PerformanceIndication
// gives an indication of the performance of the server
// IMPORTANT : NOT usable with SQL-server
// PARAMETERS
//   RETURN-VALUE : indication of performance of the server
//                  0 : the server is doing (almost) nothing
//                  1 : no performance problems, but can become bad very fast
//                  2 : no performance problems, but system is very busy
//                  3 : performance problems very likely
//   ex  : writing of thousands of records in multiple transactions
//         every few records : do this test
//         FOR i := 1 TO 100000 DO BEGIN
//           write record;
//           IF i MOD 200 THEN BEGIN
//             COMMIT;
//             CASE PerformanceIndication() OF
//               0 : BEGIN END;
//               1 : SLEEP(1000); // 1 second
//               2 : SLEEP(5000); // 5 seconds
//               ELSE BEGIN
//                 j := 0;
//                 WHILE j < 3 DO BEGIN // wait until performance is ok, or until 1/2 minute has passed
//                   SLEEP(10000);
//                   j := j + 1;
//                   IF PerformanceIndication() < 3 THEN
//                     j := 3;
//                 END;
//               END;
//           END;
//         END;

LintTestDiskAccess := 0;

LrecDatabaseFile.RESET;
LrecDatabaseFile.SETCURRENTKEY("No.");
IF LrecDatabaseFile.FIND('-') THEN
  REPEAT
    IF (LrecDatabaseFile."Writes in Queue" >= 5000) OR
       (LrecDatabaseFile."Reads in Queue" >= 16) THEN BEGIN
      LintTestDiskAccess := 3;
    END
    ELSE IF (LrecDatabaseFile."Writes in Queue" >= 2000) OR
            (LrecDatabaseFile."Reads in Queue" >= 7) THEN BEGIN
      IF LintTestDiskAccess < 2 THEN LintTestDiskAccess := 2;
    END
    ELSE IF (LrecDatabaseFile."Writes in Queue" >= 500) OR
            (LrecDatabaseFile."Reads in Queue" >= 3) THEN BEGIN
      IF LintTestDiskAccess < 1 THEN LintTestDiskAccess := 1;
    END;
  UNTIL LrecDatabaseFile.NEXT = 0;

EXIT(LintTestDiskAccess);

PerformanceSleep()
// PerformanceSleep //*** 019
// IMPORTANT : ONLY USABLE BY NATIVE-SERVER
// Tests the performance and if performance-indication is > 0 stays in this function via REPEAT and SLEEP
// this function tests every 5 seconds if performance-indication is 0,
// if it is or it is waiting for 1 minute, than it returns returns back to the calling program

IF lrecObject.RECORDLEVELLOCKING THEN
  EXIT;

lint := 0;
WHILE PerformanceIndication() > 0 DO BEGIN
  SLEEP(5000);
  lint := lint + 1;
  IF lint >= 12 THEN
    EXIT;
END;