This question is probably already out there, but bit hard to find it.

We have a LISTS table which is 1.1 gig and our network sometimes times out when trying to access it.

So I deleted about 30000 of the largest records and then did a SELECT on it, which I read should compress the file at DOS since the sizelock is 0.

But it didnt, should it? and if not whats a better way to do this?

Most of the records have a date in them - they can be made to go away. Saved lists can also be made to go away. When you think that you are at some safe minimum, create a new file (NEW_LISTS) and copy all of LISTS to it. Then delete lists, and then rename new_lists as LISTS.

Consider writing a routine to regularly clean up the lists file. Consider keeping the LISTS file on the local hard drive of each user.

As Curt said, the system generated LISTS rows have a date/time stamp. I wrote a little for our applications that go in a only delete the rows that have a date/time stamp on them of yestorday or older. That way, anything someone might be using today (right now, at the time the program is run) won't be touched. Plus this doesn't touch any rows that a user may have saved using a SAVELIST command.

The other thing that we have done is gone into the application's settings and set it so that it doesn't save any system generated LISTS rows. The users or programs can still do a SAVELIST as normal. It's just the rows that are generated by TCL LIST and SELECT statements won't be saved.

You still have to keep an eye on the user saved LISTS rows. When I go in to clean those up, I do something along the lines of what Curt was talking about.

I hope this helps.

Michael Slack

We suspect you're running into the primary vs overflow conundrum. LISTS by their nature are LARGE so the biggest part of them resides in the OV portion of the file. Now when you delete the list you free up the PRIMARY space but until you issue a compress the OV space remains taken (to speed up future expansions).

So it sounds as though you need to log everyone off then issue a COMPRESS on the LISTS file.

The Sprezzatura Group

World Leaders in all things RevSoft

A resizing of the file will only resize the .LK portion of the file. The .OV portion is essentially just a bucket of unordered frames. If your delete operation happens to delete the last frame, the file can reduce in size. However, most likely, the frames holding the records being deleted are somewhere in the middle. In this case, if the frame is completely depleted of data, the frame is added to the overflow free list.

The overflow free list is used to allocate new frames as needed before the .OV file is extended. Sometimes, though, for example if DUMP is used to fix a GFE, the overflow free list is dropped. The result is a bunch of wasted space.

You can fix this by issuing a COMPRESS in DUMP. Note that no other users can use the file while you are doing the compress. The compress essentially takes the frames in group order (the way they would appear in an unsorted select) and rebuilds the .OV file with no gaps. It is like an AREV version of DEFRAG.

JIT

The Sprezzatura Group

World Leaders in all things RevSoft

Just want to add a little info (my opinion)…

30,000 items in your LISTS file (table), that is quite alot! You might consider clearing the file (table) when everyone is off the system. That is if none of the saved lists are required by programs.

Normally, these saved lists are temporary, normally!!! I think it is rare for a program to require the saved list after the user is logged off. If you find that the lists are not needed after the user loggs off, then I suggest when all users are logged off you should clear the LISTS file (table).

Also, I find the SYSTEMP file (table) is similar the the LISTS file (table).

Be sure it is ok to clear these files (tables) before you do it.

Sometimes your LISTS & SYSTEMP files (tables) holds saved stack commands for all users. I believe the record (row) ids start like &STACK*. Then there are record (row) ids that start like T*, those are temporary and are a result of saved lists that exceed oh about 32,000 bytes or due to sorting. Kinda like a fragmented saved list.

It is possible (I strongly believe) that your LISTS & SYSTEMP files (tables) could impact the processing performance of your AREV system. Keeping those files (tables) "clean" probably would be a good idea.

Thanks for your thoughts everyone.

Our LISTS table does contain lists I can not remove as they are used to relate various processes.

Our SYSTEMP I do clear using the indexer if no other users are logged on.

I have reduced the number of saved queries the users have and trimmed out lots of old and large lists.

I thought I read some where that DUMPLH compress could not handle big files as this would be the easiest way, after backing it up of cause.

An alternative to compressing the file is to do a remaketable. I'd suggest, though, doing a backup and attempting the compress. If it fails restore from backup and do a remaketable.

Thanks for all the help.

My last reply appears at the top of this list.

As Im unsure if DUMPLH compress can handle a table of this size.

View this thread on the forum...