Suggestions to improve code

[longhair]

morning all,
hpux 11i
progress 9.1c
i have a single user db with 2 tables that are wiped and repopulated everyday.
for the wipe i do:

def var i as int no-undo.

outer_loop: do for db.table while true transaction:
 for each db.table exclusive-lock:
  delete db.table.
  i = i + 1.
  if i modulo 100 = 0 then do:
   next outer_loop.
  end.
 end.
 leave outer_loop.
end.
pause 3.

to repopulate i do:
def var lead_rotor_part like zldtm.zitem.
def var lead_lead_time as int.
def var lead_lead_soft as int.
def var lead_next_item like zldtm.zitem.
def var i as int no-undo.
def var j as int no-undo.

input from /files/filename.txt.

repeat transaction:
 do:
  lead_part = "".
  lead_time = 000.
  lead_soft = 000.
  lead_item = "".
  import delimiter ";" lead_part lead_time
  lead_soft lead_item.
  do:
   do:
    i = i + 1.
    if i modulo 100 = 0 then do:
     pause 1.
    end.
    create db.table.
    db.table = lead_part.
    db.table = lead_time.
    db.table = lead_soft.
    db.table = lead_item.
    release db.table.
   end.
  end.
 end.
end.
input close.

the db's size is about 3mb. if i run the code from the explorer it the bi file grows to about 5mb, if i run the programs in a script the bi file grows to over 6mb. it was worse before, but the addition of the pause statements has helped.
any suggestions on rearranging the code that would help with the bi file growth, or is it just that the data blocks from the bi file are not getting written to the db fast enough and causing progress to allocate more blocks to the bi file?
any suggestions would be greatly appreciated.
regards,
longhair

 

[tamhas]

If you are actually deleting the whole table every day, then you might consider putting the table in a separate database. Then you can just delete the database to empty and recreate from a copy, even with all the data already populated, if that is what you want to do. Back in the days before temp-tables, we used to do this for reporting.

 

[TomBascom]

1) You should use the same "chunking" approach with the import that you're using with the delete.

2) All of your variables should be declared NO-UNDO.

3) Increase your bi cluster size (unless you have a WG license). Pre-grow the bi file with proutil dbname -C bigrow.

4) If you can, run a server with a BIW and APWs and all of the usual db tuning. It will be faster than single user.

5) If the db is not being used for any other purpose you can use -i for these processes. But be careful. If it crashes you get to start over (from a backup).

6) Consider running multiple processes in parallel. Especially if you have multiple CPUs.

7) Put the tables being wiped and reloaded in dedicated storage areas with an appropriate rows-per-block value.

8) Use "truncate area" to perform the deletions once you have split out the tables.

9) Dedicated areas will also greatly improve the import time.

 

[longhair]

tom,
tried suggestion 1 with the following:

def var lead_rotor_part like db.table.
def var lead_lead_time as int.
def var lead_lead_soft as int.
def var lead_next_item like db.table.
def var i as int no-undo.
def var j as int no-undo.

input from /files/filename.txt.
do:
 i = 0.
 outer_loop: do for db.table while true transaction:
  repeat:
   i = i + 1.
   lead_part = "".
   lead_time = 000.
   lead_soft = 000.
   lead_item = "".
   import delimiter ";" lead_part lead_time
   lead_soft lead_item.
   do:
    do:
     create db.table.
     table.field1 = lead_part.
     table.field2 = lead_time.
     table.field3 = lead_soft.
     table.field4 = lead_item.
     release db.table.
    end.
   end.
   if i modulo 100 = 0 then next outer_loop.
  end.
  leave outer_loop.
 end.
end.
input close.

bi file still grew to the same size about 6.5mb.
i will pass your other suggestions on to the dba.
regards,
longhair

 

[TomBascom]

It was early, my morning coffee hadn't kicked in yet... I see bi size is what you are most concerned about not performance per se.

Along with my other suggestions you might also try adding -G 0 to your startup parameters to allow clusters to be reused sooner.

To decrease bi growth your best bet, overall, is to make darned sure that you do not run this code from within a transaction. You might want to add a debugging message just above your repeat loop:

if transaction = yes then message "A TRANSACTION IS ACTIVE!!!  FIX ME!!!".

If you see this message then your entire procedure is running within the context of a transaction and no bi clusters can be reused -- thus the bi file grows with every additional sub-transaction. If that is happening then that is the first thing to fix.

Once that is done then increase the cluster size and to enlist page writers to offload the io. But 6MB is a trivial amount of data -- are you sure it isn't some more interesting amount like 6GB?

 

[longhair]

tom,

i'll try your code to see if there are any 'alive' transactions.
nope, sorry to say that it is only a 3mb db and 6mb bi file.
the tables were once part of a larger single user db, but we ran into conflicts when i needed to access it and so did another person.
according the the dba there was not a bi file issue when the tables were part of the other db.
this leads me to believe that it is possible that this isn't a coding problem but an issue in the way the db was created / structured.
changing the modulo line to be:

if i modulo 100 = 0 then do:
    pause 1.
    release db.table.
    next outer_loop.
   end.

and accessing the db after a trucate but before running the code has dropped the bi file to about 2.75mb - around .5mb above the initial size of the file.
looking at it this way, it appears that the writes are not happening fast enough for the bi clusters to be reused.
the progress storage areas and extent information report shows:
area #1 is for data (control area) with a block size of 1024.
area #3 is for the bi file with a block size of 8192.
area #6 is for date (schema area) with a block size of 1024.
each area has 1 variable length extent.
regards,
longhair

 

[TomBascom]

It's such a tiny amount of space that I have a hard time calling this an "issue"

A "curiosity" or a "quirk" perhaps.

 

[longhair]

tom,
i do not believe it to be much of an 'issue' either, however, my boss does.
regards,
longhair