I have a number of tables containing session specific data. We delete this data every other day and it takes time. What I do is
FOR EACH <tablename> EXCLUSIVE-LOCK WHERE <tablename>.createDate < TODAY:
DELETE <tablename>.
END.
createDate is a relevant indexed field.
The problem is that this takes lots of time since the tables are filled with lots of data.
Now I'm considering doing tricks with transactions, I've seen examples of how that can improve speed somewhat. Something like:
loop:
REPEAT:
DO TRANSACTION:
num = 0.
FOR EACH <tablename> EXCLUSIVE-LOCK WHERE <tablename>.createDate < TODAY:
DELETE <tablename>.
num = num + 1.
IF num MOD 100 = 0 THEN NEXT loop.
END.
IF num = 0 THEN LEAVE loop.
END.
END.
Are there other options? I've also thougth about moving these tables to a specific area and truncate that area nightly, could that work?
Are there other options? I've also thougth about moving these tables to a specific area and truncate that area nightly, could that work?
Put them into a separate DB an delete that nightly.
Or drop and recreate the tables using two delta.df files. Use the load_df.p from the data dictionary to script this.
Wouldnt I need recompiling? That wouldn't be a big problem though.
Wouldnt I need recompiling? That wouldn't be a big problem
Not on current OpenEdge releases. You can create a DF including field positions for R Code compatibility.
When deleting the DB it's even possible with an old OE release, as the new copy would not have different CRC values.
What about this idea:
1) Create an empty template database on the right location.
2) Store a copy of it locally on disk.
3) Every night just shutdown, copy the empty template database to the right location and then connect again.
That seems a bit simpler than dropping tables and loading df files.
This is another form that works well - and doesn't require re-opening a query condition after every 100 records that've been deleted.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE QUERY q-name
FOR table-name
.
OPEN QUERY q-name
FOR EACH table-name
WHERE table-name.date-field
NO-LOCK.
GET FIRST q-name NO-LOCK.
tx-block:
REPEAT TRANSACTION:
GET CURRENT q-name EXCLUSIVE-LOCK.
DO i = 1 TO 100:
IF NOT AVAILABLE table-name THEN
LEAVE tx-block.
DELETE table-name.
GET NEXT q-name EXCLUSIVE-LOCK.
END.
END.
This is exactly what I would suggest. Create an empty database with the definitions and set aside that copy. Compile against that copy. At the transition point, do an OS delete or move of the old version and a fresh prodb of the empty database to your use location and name. Nearly instantaneous.
We did this sort of thing back in V5 and V6 before there were temp-tables for work tables since it would a large percentage of the time required to fill a work table in order to empty it for the next process.
This is exactly what I would suggest. Create an empty database with the definitions and set aside that copy. Compile against that copy. At the transition point, do an OS delete or move of the old version and a fresh prodb of the empty database to your use location and name. Nearly instantaneous.
Which was among my initial suggestions.
But for the sake of completion, this will require OS access on the database server. Dropping a table and reloading the DB is possible from an OpenEdge client, networked or locally connected.
fixed typo
If you do not have any index by createdate create that one and use it.
Also better to have the table on another DB that one can help a lot but if not try to do this:
This way you will have less overhead reading and blocking data, Progress read blocks of records no single records.
def buffer b for .
FOR EACH b no-LOCK WHERE b.createDate
find where rowid() = rowid(b) exclusive-lock no-error.
if avail then
DELETE .
END.