Need to compact database

[trackmanpete]

I am finding that I need to compact my database almost at all times when I delete a child record in a 1 to many set (my reports come out incorrectly without the compacting taking place). My need to delete a record is required, but from the readings, I was under the impression compacting was mainly necessary during development and not during actual use.

I don't do anything fancy with the delete, using an Action Script to delete the child record. I leave the parent record in tact (I do not want to delete it).

Can anyone suggest what I am missing, please?

[Tom Cone Jr]

Re: Need to compact database

You haven't told us what "my reports come out incorrectly" means. Among other things compacting rebuilds indexes, if I recall correctly. Might your misbehaving reports be the result of corrupted indexes? What index expressions are used to link records in the set? What index expressions are used in the report? You wouldn't be using filtered indexes by chance? More details. Or a sample database for us to examine. Would help us help you.

In the meantime, try rebuilding your indexes instead of compacting the next time you have the problem.

[trackmanpete]

Re: Need to compact database

Tom:
Thanks for getting back to me so quickly. Clarifying reports output....wrong individual (child) coming up in parent file. Once I compact child and parent are then properly connected again.

Ah, indexing, an area that I am pretty clueless on, as in my previous life of using flat databases, I had no requirement for their use. So let me start reading up on indexing and see where I am going wrong in that area. I am guessing that the only indexing that is going on is the one Alpha creates for me automatically when I create the initial auto-increment id field, with one exception. I did create an index that Gabriel used in generating an order routine in a report (found that in another thread that I believe you also read.)

dbsum("results","Sum",Results->Eventathid,"Points")

"Sum" is the name of the index and is based on Eventathid (a character field of 6 spaces that is a child field filled by using a lookup to the parent field id, thus creating a link between parent and child.

However, it isn't only the report where I see the incorrect parent-child connection. I sometimes also see it in a form with an embedded browse- correct parent, wrong child. So, I am thinking that something goes out of synch with the parent-child link when I delete a child. At least one child ends up linking with a different parent. Once I compact, all children are correctly linked to their rightful parent. Maybe it's part of "Trading Places"....

I will take a closer look at indexes and see if I can understand that area better.

[MikeC]

Re: Need to compact database

Pete,

I was under the impression compacting was mainly necessary during development and not during actual use.

True.
Instead of Compacting the database, I think most would just Pack the table in question--which I have read will also rebuild the indices.

[trackmanpete]

Re: Need to compact database

Mike:
Hmm, another new concept that I don't think I really caught on to. For some reason I thought compact and pack were the same. More late reading for me.

Thanks for your thoughts on this....

Here is what I find for compact: The A5.COMPACT() method compacts the current database. This rebuilds all indexes, removes all deleted records and reclaims unused space in all dictionaries and memo field files.



This is what I found for packing: Packing a table reclaims the space taken up by deleted records and rebuilds indexes.

Not sure if there is a difference and how it would affect what happens with my problem of parent child separation.

[Alastair]

Re: Need to compact database

Pete,

Don't know if this helps but I have a button on my Main Menu which I press to pack all the tables in just one click. If you use the Genie to create an onPush event for the button then pick "Pack" and either select "All tables in Database" or "Selected tables" and tick each table you want to pack. When you press the button each table you selected will be packed - its that easy!

[trackmanpete]

Re: Need to compact database

Alastair:
Thanks for your suggestion. I have seen that option. My concern is/was that I was doing something wrong when deleting to cause my problem of links no longer being connected properly after I do as few as five deletes of children records. In an Access commercial database I use, they also have a Pack button, but I have never needed to use it, but then I haven't deleted many entries there, either.

I guess I am trying to figure out if there is something I should include in my code when I delete a child record, so that another child from another parent doesn't show up. That is what happens on occasion now. Once I compact or pack everything links up properly.

I did go into the database and rebuilt my indexes, but I do need to do more reading on how they are created and what they do...

Thanks, again.

[Tom Cone Jr]

Re: Need to compact database

Pete,

PACKing a table will physically remove records previously marked for deletion (deleted). It rebuilds indexes. It does nothing to the DICTIONARIES for the table.

COMPACTing will do much more.

In my experience COMPACTing should never be necessary for an application that's been released. It's useful during development because it cleans up the dictionaries which store all your layouts, field rules, and scripts. Depending upon the requirements of the application PACKing should be rarely needed, too. Something you might do on a monthly or quarterly basis, perhaps, as part of regular maintenance.

It seems to me you have an indexing problem and I am glad you will be investigating it. Ordinarly, deleting a child table record should not corrupt indexes.

[trackmanpete]

Re: Need to compact database

Tom:
Thanks for your insight. I just read Cal's viewpoint on packing and indexing where he is saying what you are. I will continue to investigate how corruption of indexes might occur and see if coding is not working correctly to see if I can come up with an answer. There is still a foot of snow outside and temps are in the teens, so I have plenty of time to research...