temp-table performance

T

tsp

New Member
Hi

I have a temp table that is being populated with around 3000 records.

The speed is fine when populating at the beginning but as the table gets bigger its performance is degrading. I have found this out by outputting to a file and checking etime

what things can i do to improve this performance?
 
Look into the -Bt client startup parameter. Temp-tables are held in memory until the buffer specified by -Bt is filled. At that point they start overflowing to disk. Disk IO is roughly a million times slower than RAM access. That's usually noticeable ;)

You might also want to consider modifying -tmpbsize.

You can use a combination of -T and -t to place the temp files in a known location and make them visible. The DBI* temp files are temp-table overflow. The only diagnostic which is available is to observe changes in size.
 
hi

Thanks

-BT is currently set to 5120 i have increased it to 40000 but made no difference

it also has indexes on the table

In regards to the .dbi files. I have some in my temp table which are 32kb what does that mean?

cheers
 
DBI files start out with an initial size. In OpenEdge 10 the temp-table database is a type 2 storage area so that size is one cluster (for the data) plus at least one block (for the default index). I forget how the v9 initial size is calculated -- it was so very, very long ago ;)

If the DBI files are not growing as you add records that indicates that you probably have some sort of coding issue rather than an IO issue. You will have to post some code.
 
heres the code nothing different of how this is created as it is similar in other parts of the system. If the records are say 500 then its is very quick! but struggling with such high number of records

FOR FIRST pdm_client FIELDS (pdm_client.client_obj)
WHERE pdm_client.organisation_obj = pdOrganisationObj
NO-LOCK:
FOR EACH pdm_matter
WHERE pdm_matter.client_obj = pdm_client.client_obj
NO-LOCK:

CREATE ttConflictMatter.
ASSIGN ttConflictMatter.cCode = pdm_matter.matter_code
ttConflictMatter.cName = pdm_matter.matter_name
ttConflictMatter.cDescription = pdm_matter.matter_long_description
ttConflictMatter.lChargeable = pdm_matter.matter_chargeable
ttConflictMatter.cRelationship = "Client":U
ttConflictMatter.dMatterObj = pdm_matter.matter_obj
ttConflictMatter.dMatterPartyObj = ?
ttConflictMatter.dOwningOrganisationObj = pdOrganisationObj
ttConflictMatter.dOwningAddressUsageObj = pdAddressUsageObj.

IF plExtraDetails THEN
DO:
ASSIGN ttConflictMatter.tOpenDate = pdm_matter.matter_open_date
ttConflictMatter.cStatus = (IF pdm_matter.matter_status = {&pdmmaMatterStatusOpen} THEN
"Open":U
ELSE
IF pdm_matter.matter_status = {&pdmmaMatterStatusOnHold} THEN
"On Hold":U
ELSE
IF pdm_matter.matter_status = {&pdmmaMatterStatusClosed} THEN
"Closed":U
ELSE
"Archived":U).

RUN extraDetails IN TARGET-PROCEDURE (pdm_matter.branch_obj,
pdm_matter.fee_earner_obj,
pdm_matter.partner_obj,
pdm_matter.department_obj,
pdm_matter.work_type_obj,
OUTPUT ttConflictMatter.cBranchName,
OUTPUT ttConflictMatter.cFeeEarnerName,
OUTPUT ttConflictMatter.cPartnerName,
OUTPUT ttConflictMatter.cDepartmentName,
OUTPUT ttConflictMatter.cWorkTypeName).

RELEASE ttConflictMatter.

END. /* plExtraDetails */
END. /* FOR EACH pdm_matter */
END. /* FOR FIRST pdm_client */
 
Nobody is likely to be able to make any sense of this without the definition of ttConflictMatter and without seeing the code in extraDetails. (And where is target-procedure? Is that on an app-server?)

And please use [ C O D E ] tags when posting code. It makes it so that it is actually readable.

Whatever else is going on I also see that you are using RELEASE. It isn't doing any harm but it almost certainly isn't doing whatever it is that you are hoping it is doing.
 
Without looking too hard at this -- you are probably making "deep copies" of the temp-table and passing them across the app-server boundary with every call to extraDetails. As the TT gets bigger more and more data is passed back and forth and it takes longer and longer. You could probably see this (crudely) by monitoring network traffic as it runs.
 
Code: 
  DEFINE TEMP-TABLE ttConflictMatter NO-UNDO
  FIELD cCode                   AS CHARACTER FORMAT "x(20)":U
  FIELD cDescription            AS CHARACTER FORMAT "x(25)":U
  FIELD cRelationship           AS CHARACTER FORMAT "x(20)":U
  FIELD cConflict               AS CHARACTER FORMAT "x(20)":U
  FIELD cName                   AS CHARACTER
  FIELD cBranchName             AS CHARACTER
  FIELD cFeeEarnerName          AS CHARACTER
  FIELD cPartnerName            AS CHARACTER
  FIELD cDepartmentName         AS CHARACTER
  FIELD cOrganisationName       AS CHARACTER
  FIELD cWorkTypeName           AS CHARACTER
  FIELD tOpenDate               AS DATE
  FIELD cStatus                 AS CHARACTER
  FIELD dMatterObj              AS DECIMAL
  FIELD dMatterPartyObj         AS DECIMAL
  FIELD dOrganisationObj        AS DECIMAL
  FIELD dOwningOrganisationObj  AS DECIMAL
  FIELD dOwningAddressUsageObj  AS DECIMAL
  FIELD dAddressUsageObj        AS DECIMAL
  FIELD lChargeable             AS LOGICAL
  FIELD iWeight                 AS INTEGER
  INDEX dMatterObj IS PRIMARY UNIQUE dMatterObj dMatterPartyObj
  INDEX dOwningObj                   dOwningOrganisationObj dOwningAddressUsageObj
  INDEX iWeight                      iWeight.


the extra details is on the appserver side which is where the create ttconflictmatter takes place so traffic should not be an issue right?
 
I see a CREATE ttConflictMatter in your client side code as well.

I don't think this code does what you think it does.
 
hi

its passing the table once populated back to the client side. The client side program accepts the table as table handle.

all creates are defintely done on appserver side
 
Have you verified that it's the tt creation that's slowing it down? You could check that by moving the create statement to above the loop, so that on each iteration it's working on the same tt entry.

Also, how much is performance degrading? Can you post some numbers of the 500 vs. 3000 runs?
 
Hi

Fixed the issue. We were using static temp tables but were being passed through the procedure as table-handles which when removed and using static tables it speeded it up!

also in one of my loops the table-handle was being returned when it needed not be in the loop

thanks for the help guys

cheers
 
You must log in or register to reply here.
Back
Top