"Cleaning up" the database

[Jonathan Smith]

I currently have a "PARTS" table with over 120,000 records... This makes the database unbelievably slow (that is, tables that used records related from this table)! What I want to do is write a script that sorts record by modified/accessed date; any records that have not been accessed in 3,6,or 9 months (drop-down value) I would like to duplicate them, put them into a "BACKUP PARTS" table (that will not be accessed other than manually), and then delete the found set.

Is it possible to sort records by a access/modification date?

[bcooney]

Won't this mess up any invoices, POs, etc. that relate back to the Parts table? Why not add an "active, inactive" flag field and use that as a filter in any value lists or popup portals?

[Jonathan Smith]

That's a good idea...

I was thinking of adding a BACKUP checkbox that, if checked, refers the record to the PARTS BACKUP table.

If I were to make the field(s) inactive, would this reduce the file size and thus speed up searching?

[Jonathan Smith]

How would I go about doing it the way you specified?

[bcooney]

Well, before we get to that. What exactly is slow? Having a lot of records, doesn't necessarily make for a slow system. Is it value list displays? Calculated inventory levels?

[Jonathan Smith]

Tables that link to the file are slow in loading, some take up to 20 seconds to display. The PARTS table is almost 17MB.. There are a number of tables with portals that are related to the file, all of which load incredibly slow.

[bcooney]

I have a customer with well over 100K records. No speed problems. I'm thinking it is unstored calculation fields that are causing the slow display, or portal sorts based on unstored calcs.

[Ender]

Yes, large portals are slow to load, especially when they are sorted.

Anyway, it makes little sense to give the user a portal with hundreds of thousands of records (or even thousands) to sort through. Help the users by giving them a way to filter large portals (by default) so they only show small subsets based on a chosen category or something.

This can easily be done by adding another TO (table occurrence) of the related table and changing the relationship to that TO so that it uses a global on the parent side matched to a corresponding field on the child side. Changing the global filter then changes what shows in the portal.

[bcooney]

Thanks for chiming in Ender. It's very hard from this distance to determine what exactly is causing your system to be slow, so we're mentioning the usual suspects.

[Jonathan Smith]

I see what you mean. Let me try to explain the issue a little more in depth.

A number of tables use the PARTS table; The QUOTING system, the CONTRACTING system, the PRODUCTION system, the DELIVERY system, and a few other smaller tables. If a company orders, for example, 800 parts, each item (though the same part number) is assigned a unique extended part number. Therefore, even if the customer orders 200 pavers that are all identical, 200 records are created in the PARTS table.

The issue, however, mostly occurs in the CONTRACTS and QUOTES tables. There are number of portals on these two pages; QUOTES for example has 8 portals setup in 7 different tabs. CONTRACTS has a total of 13 portals, split across 8 tabs.

When you spoke of doing another TO, what exactly does that mean. I am relatively new to filemaker, this database was already written when I came onboard. Your help is appreciated!

[Ender]

First, I don't see why you would create 200 records for 200 identical parts ordered on the same quote/invoice. Presumably, things like pavers are are all the same, so just tracking the quantity of each different item would be sufficient. This is usually how POs, Quotes, and Invoices work. If this is possible in your solution, I'd imagine there'd be a pretty significant performance improvement as those line-item portals would be reduced to just the unique parts that are quoted or sold.

The issue, however, mostly occurs in the CONTRACTS and QUOTES tables. There are number of portals on these two pages; QUOTES for example has 8 portals setup in 7 different tabs. CONTRACTS has a total of 13 portals, split across 8 tabs.

The number of portals on each layout or tab isn't so much a factor for performance, but rather the number of related records, the complexity of the relationships and sorts for those portals, and the speed of the network between the host and the client.

When you spoke of doing another TO, what exactly does that mean.

Adding another table occurrence is just a matter of adding it to the relationship graph using the one of those little buttons on the lower left, and picking the base table. Then you link the new TO to another by dragging a line between the keys.