really big files

[michaelzap]

Howdy -

I have a database of donors and donations to a foundation with various associated related files (programs, campaigns, correspondence, etc.). One of the files (Gifts, the one that keeps track of the donations) is really huge (250+ megs), and I wonder if I've done something stupid to make it so big. We're also finding network access of the database to be very slow, and we need to have several people answering the phone and entering donations staring next week.

The Gifts database has something like 120,000 records. The newest of these records have related line items in another database (imagine your basic invoice setup), but the vast majority have none because they were imported from the old system (before they kept track of individual line items). It's related to several other files and I used indexing wherever possible (which I know can bloat a file's size), but it still seems to me that it just shouldn't be that big.

So I guess my questions are:

1) Does this in fact sound too big to be healthy to y'all?

2) Could it's girth be what's affecting our network performance?

and, 3) What could I do to reduce the file's size without sacrificing performance (for finds, etc.)?

Thanks in advance for your replies.

[dbhill]

Your file size is not too big for FileMaker. The official file size limit is 2GB. Your record set is not too great for FileMaker. There are many FileMaker databases over one million records in use. You can optimize your performance in many ways. For finds, you can check the field options and turn indexing off for all fields that will not be searched.

~Dennis

[Anatoli]

As next step you can also try to use more of relational design. Especially repeating fields are better in another relational database and in separate record.

[Vaughan]

A 250 MB FMP database?!? Try saving a compressed copy and see if it makes a difference. Allow literally several hours of down time to perform the save -- do it overnight or on the weekend rather than during business hours when people will want to use it.

[michaelzap]

Thanks for your replies.

I know that I *can* use a file this big in FileMaker, but I'm wondering if I shouldn't, or if something I've done has bloated the file more than necessary. I'm not using any repeating fields, and I'm doing most everything using related databases. Maybe I'm just being obsessive, but if there's a way to make this database leaner and meaner I want to try it.

One thing I will try is turning off indexing for fields I don't expect to use for finds.

Do y'all think that my network problems could have to do with the size of this file, or does that not really matter (since it only passes the data the client's actually viewing, right?)?

Again, thanks for your help.

[dbhill]

Okay; what 'are' your networking problems? Are you using gigabit ethernet? A fast SCSI hard drive on your server? FileMaker Server 'only' running on the server machine, and no other apps? No FileMaker client loaded on the server?

~Dennis

[Anatoli]

Denis is on Mac for sure

We are running FM with 150MB file, another 24 large files, 15 users with 10Mb coaxial Ethernet from HP 500MHz server which is serving FM, SQL, Mail server and Proxy/firewall without any problems or performance hits.

Nobody can have worse scenario or cheaper network and HW, but it is OK.

[Vaughan]

My comment above relates to the fact that I cannot imagine a 250MB FMP database without either a billion records or container fields full of images.

I have a db here (not mine) with 130,000+ records and it's only 46 MB. A just made a compressed copy of it and brought it down to 23 MB.

Try saving a compressed copy, or perform a recover just to see what happens. If it's a simple file, rebuild a new db from scratch and import the records.

[ November 11, 2001: Message edited by: Vaughan ]

[dbhill]

Vaughan makes a good point... do you have graphics in your database, michaelzap? If you have "Store Compatable Graphics" enabled in your Document Preferences, then FileMaker will include a Pict image along with the JPEG graphic and add substantial weight to your file. If this is so, turn off Store Compatable Graphics. Then reimport your graphics.

~Dennis

[michaelzap]

Sorry to have taken so long to respond to everyone's suggestions. Feels as if I haven't had a spare moment in weeks. Anyway...

I'm fairly certain now that the "network problems" I mentioned earlier have nothing whatsoever to do with the network. I find that I have the same issue when using the database locally. The problem is strange, and I can't figure out why it's happening (and I don't know that it has anything to do with file size, either - maybe I should rename the thread).

I have a set of related databases that are essentially the typical invoice and line item setup (with some variations - this is a foundation, not a business). Right now we are doing our big fundraising campaign for the year, so we have volunteers entering donations as calls and mail come in. To do this without giving any and everyone access to our database, I created a set of entry databases that allow for these volunteers to enter their data (in a set of related databases virtually identical to the main databases) and have a script that copies what they entered into a new record in the main database and then deletes the temporary records from the entry databases.

This is working really well, and overall we're very happy with it. However, the very first order that's entered by each volunteer takes FOREVER to be copied to the main database. After the first one, they all go pretty quickly, but the first one can sometimes take more than five minutes!

I tried to debug this by adding alerts to the script to figure out exactly where it was delaying so long. It seems that it's getting bogged down at the point where it's copying the related line items from the temporary (entered) database to the main (permanant) database. But why? It's a mystery to me. The script is not sorting the records or anything like that, and all the files are opened by a script when the volunteer logs in, so it's not because FileMaker needs to open the file.

Any ideas as to what might cause a one-time delay like this? If you need more information, please let me know. I didn't want to innundate y'all with all my scripting, and I wasn't sure what info would actually be relevant to this problem.

Also, we're using regular FileMaker (NOT Server or Unlimited). Would we get a significant speed or reliability boost by purchasing Server? And the Gifts file just keeps getting bigger - 320 megs and growing!

In response to the person who asked about images: Yes, there is a logo on the invoice and tax letter printouts, but that's all. As I understand it, FileMaker only keeps one copy of the image in the file and just uses it where necessary. I don't think a single image would make much difference, right?

Again, thanks to everyone for your help.