We all know the "for each " in SQL?
Some know how to use "proutil -C idxfix" to complete the data wipe.
Are you sure? I thought truncate area just reset the high-water marks,which resulted in all tables in that area being emptied, and indexes disabled.
From the help:
All you should need to do would be to re-activate the indexes in the truncated area and proceed with your load. Or do the load and then activate / build the indexes.
Your right about the schema... the 4 GL program in between the -C truncate and the -C idxfix just gathers the names of the tables and indexes to perform the fix for every table in the area.
However, the question still remains: What's the fastest 4GL method to erase a table content?
Something like the following. Change the cur-cnt upper limit from 100 to whatever works best with your system.
DEFINE VARIABLE cur-cnt AS INTEGER NO-UNDO.
DEFINE QUERY q-table
FOR table-name
.
OPEN QUERY q-table
FOR table-name
NO-LOCK
.
GET FIRST q-table NO-LOCK.
tx-block:
REPEAT TRANSACTION:
DO WHILE cur-cnt = 1 TO 100:
IF NOT AVAILABLE table-name THEN
LEAVE tx-block.
GET CURRENT q-table EXCLUSIVE-LOCK.
DELETE table-name.
GET NEXT q-table EXCLUSIVE-LOCK.
END.
END.
If this is an on-going requirement instead of a one-time issue, then the fastest way is to create a separate database with that table, make a copy, fill it, use it, then delete the database when done. Rinse, Lather, Repeat.
Of course, if it only needs to live for one session, then use a temp-table.
So, there is no quick way to delete the contents of a table using 4GL? These tables are too big for temp tables, and clearing them after use is essential. We currently let them build for a week, but have to take the db down to use proutil. This does not bode well for a 24-7-365 operation.
If we could clear the tables at the 4GL level, we could improve up-time.
Like I say, for that kind of application, just put that table in its own database. Attach the database when needed, use it, and then when you want to empty it, detach it, delete it, and make a fresh copy from the empty one.
We did this a lot back before temp-tables for reporting. We had an empty database with a couple of generic tables in it, and the report front end would make a copy, which was very fast because the database was empty and small, attach to it, then run the report which would fill the table with values, do the report and return. The front end would then detach and delete the database. Stunningly fast compared to trying to actually delete the records!
Yes, we did that too. Now that the external db has its own area in a larger, consolidated, type II database, we use the -C truncate area. I seem to recall the addition of some kind of "delete all" command being added to the 4GL when OE10 came to release.
In SQL, truncate table , takes the contents of the table out with a sub-second response time. Loading the SQL process engine works to do this, but the 4GL application does not have access to either the SQL process engine, nor proutil.
If there were a 4GL command like it, problem solved, from the application side.
commands and times to delete 100,000 rows:
/* delete from transfer. */ 15 seconds
/* for each transfer exclusive-lock: delete transfer. end. */ 15 seconds
REPEAT TRANSACTION:
DO cur-cnt = 1 TO 100000:
IF NOT AVAILABLE transfer THEN LEAVE tx-block.
GET CURRENT q-table EXCLUSIVE-LOCK.
DELETE transfer.
GET NEXT q-table EXCLUSIVE-LOCK.
END.
END. 14 seconds
See other thread reply for more info.
The idea isn't to delete the entire table in one TX, but to break the delete up into multiple smaller TXs that the db can process faster.
So it's no wonder this isn't showing much improvement. Try setting cur-cnt's upper limit to something like 500 or 1000 and see what happens.
by splits with limits:
limit seconds
100 13
1000 14
500 14
50 13
10 14
5000 14
No significant difference with the limits imposed.
You could use OS-COMMAND to run the SQL Explorer with a script.
Something like -
sqlexp -db dbname -H host -S servicename delete_script.log
But I imagine you're looking for some explantation why these features are not supported in ABL.
No significant difference with the limits imposed.
Something's weird then. There should be some kind of difference in speed.
What does your bi blocksize / cluster size look like? Are you using the default values?
Are you restarting the database session between runs? Otherwise, this might just represent all activity to -B buffer.
If he's using the default block / cluster size, then it's going to be hard to get more performance out of the system.
Blocksize=8, Clustersize=32768
proserve -B 4000
new session every run...
1000 16 sec
100 15 sec
5000 16 sec
500 15 sec
Looks like that's as fast as it can go.
That's it? Good grief, no wonder it's so slow.
How many users on this system? How much RAM in the system? DB blocksize?
It's a development HP-UX database only server. Tier 1. There are a lot of idle databases on it, about 100, in v8, v9 and oe10. There are only a couple oe10 sharing the resources.
The blocksize=512 to match the OS and that's as fast as that will get. There's about 8gig of RAM available to the process, when it goes to production about 64 gig. There are 4 CPU's barely breathing hard with only 10% usage.
In character client mode, the old "for each, delete" statement was too slow, so we went with the convoluted truncate area method which still requires sole access to the db. We considered swapping tables between two areas with the "move table" command but that still requires sole access too. Maybe in OE10C?
But what I did hear was that there was a command in the 4GL, that erased a table in the background, giving control back to the program. I could not find it.
I'm not that familiar w/HPUX, but a file-system blocksize of 512 bytes sounds rather odd - and awfully small. I'd expect the block sizes to be either 4K or 8K.
Be that as it may, the first thing to do is increase -B to 40000(yes, add another 0). 40K * 0.5K blocks = 20MB, which is still pretty small. If -B was 400K, that's still only 200MB.
The next thing to do would be to increase the .bi clustersize.
As for there being a way to drop a table in the 4GL, I've never heard of such a statement.
The 1/2K "block" is an ancient Unix standard ... but doesn't relate to the actual physical block size used by any even vaguely modern Unix. You should check into this and redo your databases accordingly because you are clearly doing **way** more physical reads than you need to.
I suspect that you must be getting poor performance on this box all the way around with that kind of block size and such an infinitesimal -B. Perhaps you are only noticing the performance with the delete because it is one of the few ways that you are stressing this machine since it is a development box.
I might suggest fixing these absolute basics and then posting a complete set of start up parameters and a reasonable sample of promon output to PEG to get some further tuning suggestions. There is no current magic bullet for table deletion ... other than the suggestions you have already been given ... but I am sure that you can boost the performance of this system dramatically by doing some tuning.
It's not a tuning issue. HPUX is set to an 8K blocksize, the same as the db for bi, ai as when the db was created. 512 is the number of records per block as calculated by 32768/64. The -B is set to 4000 x 1k so it is set to 4,000,000 or 4mg of memory allocated.
The other parameters are set up to optimize usage for a production server and are not being used during this test.
It's fine if there are no other options to delete the contents of the table. We've explored every option we could think of, and have not found anything better than the proutil option. When OE10C comes out, we have been promised more real-time structure changes. Until then, the 4GL is just not capable of a better solution.
A tech peaks session a couple years ago, when 10.0 was released had noted the new command to delete the contents of a table with a single 4GL delete/remove/clear/truncate statement of some kind, but I guess it didn't make it into the product. I cannot find my notes on the subject or any reference to a delete function in the documentation. That would not be the first time there is undocumented functionality that someone found how to use...
512 is the number of records per block as calculated by 32768/64.
That's not possible, because 256 is the max RPB for an area.
4MB of database buffer space is tiny - even on my "just me" PC databases I'll allocate 40-80MB. More if I'm doing intensive data work. Trying to do anything with 4MB of buffer space is just painful.
It's not a tuning issue. Just so we're talking the same language:
from the ST file:
...
d "tran Data 101":101,256 /u01/.../101.d1 f 2048000
...
prostrct create blocksize=8192 was used to create the db
...
BI Blocksize= 8k, AI Blocksize=8k, BI clustersize=32768
...
Start parameters also, not an issue:
-aibufs 200
-aistall
-B 1200000
-baseindex 1
-basetable 1
-bibufs 200
-bithold 3800
-indexrangesize 2000
-L 24000
-n 515
-Ma 5
-Mi 5
-Mn 100
-Mpb 84
-PendConnTime 5
-semsets 4
-ServerType 4gl
-spin 100000
-tablerangesize 2000
My question was and still is, has anybody heard of a faster way to delete records from a table from the 4GL? The answer is not yet. We can use SQL, proutil, or 4GL to do it, or isolate the tables in a database and delete the db. Only SQL & 4GL allow for 24hr user access, and SQL only works with an external command, like OS-COMMAND.
So, the message seems to be shifting here, but I think we are left with:
1. You came looking for a magic bullet in the ABL and the answer is that it doesn't exist. It might exist in the future, but it doesn't exist now.
2. You have been given alternatives which are fast, but you apparently don't want to go that route.
3. You have been given some suggestions about ways to make incremental improvements ... not magic shifts ... and they don't seem to work for you even though they have worked over and over again for other people. This suggests some performance issues with your system, but you are convinced that this is not an issue. Unlikely, but even if you were getting improvements here, they would be incremental, not multiple orders of magnitude. And, the fact remains that the performance is less than we think you should be seeing.
What else can we do?
I notice that at 16:20 you mention that -B is 4000 and at 17:16 you list all of the parameters where it sets -B to 1200000. What is the real value of -B when you try to delete the records?
I don't know whether you run bi & page writers. With -B 4K, I'd suspect not. With -B 1.2M, I'd suspect so. They would certainly help. I agree that you should be getting better performance than you are seeing. Perhaps you could post some of the stats from promon during the delete??
A tech peaks session a couple years ago, when 10.0
was released had noted the new command to delete the
contents of a table with a single 4GL
delete/remove/clear/truncate statement of some kind,
but I guess it didn't make it into the product. I
cannot find my notes on the subject or any reference
to a delete function in the documentation. That would
not be the first time there is undocumented
functionality that someone found how to use...
Are you perhaps thinking of the SQL-92 "drop table" statement?
Yes,
There are three settings for -B during my tests:
-B nothing, -B 4000 and -B 1200000. The times for deletes are 15 seconds, 13 seconds and 1 minute 55 seconds respectively by setting the -B.
I think we found the fastest way to delete records with the PROUTIL option. The second fastest is the SQL, and the third which is the ABL/4GL.
Tuning for the Production db cannot be changed easily and must remain with -B at 1200000 for all the other jobs that are running. Testing the various options to delete records from a table is fun, but when it goes into production, it ultimately can't be the only tuned option.
If there is no ABL alternative to make the process acceptable, so be it.
-B = 1200000 results in 1:55 minute delete time?
Something's really odd there...I'd expect the -B 4000 or -B nothing to be the slow ones.
Tim, apparently you missed the part about "", so obviously it must be gremlins.
Tim, apparently you missed the part about "", so obviously it must be gremlins.
You mean like the movie where they're cute and cuddly during the day, but feed them after midnight or get them wet and "look out"?
Maybe its like tribbles ... give them too much room and they just reproduce and fill the available space.
After so many years I can confirm that in 11.5 4GL Fast table deletion is implemented ( actually in 11.4 but I do not have it to test). So when a table with one million records takes 2.5 minutes to drop in 10.2B. On the same server in 11.5 one million record table was dropped in 1 second ( same as empty table ). The table has to be in SAT 2 of course. But the indexes do not have to be in SAT 2.
you use this feature by deleting a table in the 4GL data dictionary tool.
note that this works for tables and indexes in type ii data areas but not for type i.
> On Nov 13, 2015, at 9:50 PM, Dmitri Levin wrote:
>
> Update from Progress Community [https://community.progress.com/]
>
> Dmitri Levin [https://community.progress.com/members/broder]
>
> After so many years I can confirm that in 11.5 Fast table deletion is implemented ( actually in 11.4 but I do not have it to test). So when a table with one million records takes 2.5 minutes to drop in 10.2B. On the same server in 11.5 one million record table was dropped in 1 second ( same as empty table ).
>
> View online [https://community.progress.com/community_groups/openedge_general/f/26/p/8292/75199#75199]
>
> You received this notification because you subscribed to the forum. To unsubscribe from only this thread, go here [https://community.progress.com/community_groups/openedge_general/f/26/t/8292/mute].
>
> Flag [https://community.progress.com/community_groups/openedge_general/f/26/p/8292/75199?AbuseContentId=ef3243fa-583f-46a2-b3fa-a34bfc72f26c&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true] this post as spam/abuse.
> After so many years I can confirm that in 11.5 4GL Fast table deletion is implemented ( actually in 11.4 but I do not have it to test).
I have Progress V11.4 and can confirm that the fast table deletion is working there.
> The table has to be in SAT 2 of course.
No matter how large is the table - Progress will delete its contents generating just 9 recovery notes:
RL_RMDEL
RL_RMCR
RL_BKBBOT
RL_BKMBOT
RL_BK2EB
RL_CLUSTER_OWNER
RL_BKMBOT
RL_BKFAM
RL_OLIST_DEL
> But the indexes do not have to be in SAT 2.
This is not exactly correct.
If the indexes are in SAT2 then Progress will use the fast object deletion.
If the indexes are in SAT1 then Progress will delete the indexes block by block - generating 3 recovery notes per each index block:
RL_IXDBLK
RL_BKFAB
RL_BKFAM
It's fast but not instant.
Thanks for correction. I guess my test with one million records in sports2000 was not enough to see the difference of slow index deletion. Nobody should have anything in SAT I any way (other then a schema).
I used this last week to delete a multi-GB table from a test DB. It was in 11.3, if I recall correctly. It works great.