Deleting the contents of a table...fast! - Forum - OpenEdge General - Progress Community

Deleting the contents of a table...fast!

 Forum

Deleting the contents of a table...fast!

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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?

    --
    Tom Bascom
    tom@wss.com

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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"?