Buffer-create slowing down?

[incuboy]

HI,

I just want to ask if it's normal for a buffer-create method to slow down while records were accumulating?
If its not then probably somethings wrong in my code.

I'm using 101c. And I'm doing a record transfer from excel file(.xls/xlsx) to dynamic temp-table then to the physical table.

If the answer is "NO its not normal" then i'm gonna show to you guys my code if its ok. Thanks alot.

 

[incuboy]

ooops.. sorry for double post. my browser hangs up..

 

[TomBascom]

Is your temp-table overflowing memory to disk? (Look at the size of the DBI* file in your SESSION:TEMP-DIRECTORY directory...)

Or maybe you are not using an index in some lookup portion of your code.

Or, perhaps, your update logic is driving a lot of bi file activity and your disk subsystem cannot keep up.

 

[Cringer]

Are you tidying up handles as necessary? It certainly sounds like something is wrong, but we're just trying to catch flies with chopsticks at the moment. Post your code and we'll have a look at the problem.

 

[RealHeavyDude]

Out of experience I can tell you that the BUFFER-CREATE () method does not slow down when invoked repeatedly. I suppose there is something in your code that is responsible for slowing down. If I should speculate I would say that you are generating a memory leak in not cleaning up properly handle based objects that you create. To give you a better advise you should post the code.

Heavy Regards, RealHeavyDude.

 

[incuboy]

Hi, sorry for super late reply. This is my code. This is the code responsible for putting the excel values to a dynamic temp-table. And i'm pretty sure I am using the index 'coz i had created the dynamic buffer same as the physical table I used create-like and add-like-field methods. Not sure about the DBI* file tom was saying i'm not yet familiar with it but i will try to check it. Thank you the replies sorry again for posting this late.

  ASSIGN
    iRow    = INT('{&gRow}') + 1
    iDynRow = 0
    iColumn = 1.


  DO WITH FRAME {&FRAME-NAME}:


    /* Output the keys in comma separated list */
    RUN getUniquekeys.p(INPUT gchDatabase,
      INPUT gchTableName,
      OUTPUT gchKeysList ).


    /* sample output: --> WHERE Field-Name = ,AND Field-Name = */
    RUN buildPredicateExpressionProc(OUTPUT chRawPredicate ). /* for processing */

    buf-hndl-TempTable:EMPTY-TEMP-TABLE().

    /* Extract excel values to the dynamic temp-table */
    ExcelRow:
    DO WHILE(TRIM(chWorkSheet:CELLS( iRow + iDynRow, iColumn ):VALUE) <> "eof" ) TRANSACTION:

      chPredicate = "".
      /* Assigning values to where clause */
      RUN assignPredicateExpressionProc(INPUT chFieldListFromExcel,
        INPUT chRawPredicate ,
        INPUT (iRow + iDynRow),
        INPUT gchKeysList,
        OUTPUT chPredicate,
        OUTPUT loValidPredicate ).
      /* with keys only */
      IF chPredicate <> "" THEN
        loAvail = buf-hndl-TempTable:FIND-FIRST(chPredicate, EXCLUSIVE-LOCK ) NO-ERROR.


      IF NOT loAvail THEN 
      DO:


        IF loValidPredicate THEN 
        DO:

          buf-hndl-TempTable:BUFFER-CREATE.


          /* Iterate to columns */
          ExcelCol:
          DO idx = 1 TO giTotalCol:

IF buf-hndl-TempTable:BUFFER-FIELD(STRING(chWorkSheet:CELLS( int( '{&gRow}') , idx):VALUE))ATA-TYPE <> "BLOB" THEN
ASSIGN
buf-hndl-TempTable:BUFFER-FIELD(STRING(chWorkSheet:CELLS( int( '{&gRow}') , idx):VALUE)):BUFFER-VALUE = chWorkSheet:CELLS( iRow + iDynRow, idx):VALUE.


ELSE DO: /* Load the blob datatype */


          /* Blob file availability checking */
          FILE-INFO:FILE-NAME = STRING(chWorkSheet:CELLS( iRow + iDynRow, idx):VALUE).
          IF FILE-INFO:FILE-TYPE <> "FRW" THEN 
          DO:


            ASSIGN 
              loProblemFound = YES.
            NEXT ExcelCol.

          END. 
          ELSE 
          DO: /* File path is valid */

            COPY-LOB FROM FILE STRING(TRIM(chWorkSheet:CELLS( iRow + iDynRow, idx):VALUE)) TO memImage.
            buf-hndl-TempTable:BUFFER-FIELD(STRING(chWorkSheet:CELLS( int( '{&gRow}') , idx):VALUE)):BUFFER-VALUE = memImage.

          END.

        END.

      END. /* DO idx = 1 TO giTotalCol: */

    END. ELSE /* IF loValidPredicate THEN DO: */


ASSIGN loProblemFound = YES.


  END. ELSE /* Duplicate Record or with error */


ASSIGN loProblemFound = YES.


  ASSIGN
    iDynRow        = iDynRow + 1 /* Excel Row Iteration */
    loProblemFound = NO. /* reset problem indicator */


END. /* DO WHILE(TRIM(chWorkSheet:CELLS( iRow + iDynRow, iColumn ):VALUE) <> "eof" ): */


chExcelApplicationisplayAlerts = NO.
chExcelApplication:AlertBeforeOverwriting = NO.
chWorkBooks:SaveAs(FILL-IN-Source,,,,,,) NO-ERROR.

DELETE OBJECT hTempTable.

 

[incuboy]

hi guys.. I think i already found the problem.. In the above code forgot to delete the query object. Which is invoking inside the do while . Currently i'm testing it again seems fine as of now. Still don't know about DBI* file, i've noticed it gets bigger. Anyone can explain what DBI* file purpose?

 

[Stefan]

Hi, sorry for super late reply. This is my code.

When posting code please use the CODE tags - it keeps it formatted.

 

[Cringer]

There you go Stefan - looks a bit nicer now...

 

[Marian EDU]

wasn't able to find the query object you were referring to but I guess there might be more than what you've posted... I see however something that might look like a memptr variable and no sign of release on it - set-size(0).

also, having the temp-table as no-undo could help i guess...

 

[TomBascom]

The DBI* file is "overflow" for temp-tables. If temp-tables will no longer fit in the memory allocated for them (via the -Bt client startup parameter) then the excess will be written to the DBI files associated with that session. DBI files start at a standard size which varies depending on Progress version and -tmpbsize. When they grow beyond that initial value you know that you have filled -Bt memory and that temp-tables are now costing you IO operations.

But your other memory like is likely the more direct cause of your initial problem.