sluggish database, want to try to optimize design

[oilcan]

Hello,

I work in a biochemical production lab, and we use Filemaker v5.5 to archive data regarding our production. As of late, our database has started to become quite sluggish. I have some ideas that may help speed it up, but i need to make an inquiry as to whether one of my ideas is worth the effort.

I believe that in large part the database is getting bogged down because of the number of containers in each record, and the enormous size of picture files being inserted by some of the techs. There are 3 containers in each record, containing images of size exclusion chromatogram, anion exchange chromatogram, and biotinylation assay. Now, I'm almost certain there could be an advantage to turning the biotinylation assay container into a portal that looks at another related database. the reason being that a biotinylation assay image may, and in fact normally does, contain info on multiple records. So what ends up happening is the same image gets stuck into each record, drastically increasing the size of the database, and I believe this is contributing to the growing sluggishness of its operation. If I were to make a related file, using the image just once and pointing each relevant record in the main database to the image, this would almost certainly improve the database functionality.

Now this is my question...the other two containers are unique to each record. Each image contains data on only that record. Would there exist any benefit to making these containers portals as well to another related file, with say 2 containers to hold both of these images? In one respect, it would reduce the size of the main file dramatically. But being that each image is unique to each record, I don't know if this would improve the functionality of other aspects of the database or not.

Any advice regarding this issue would be appreciated, as it looks as though it would be a major undertaking to rearrange the data, and I want to be sure it would be worthwhile before doing it.

thanks in advance!

[Leb i Sol]

I think that you'r on the right track....1 portal is definetly in place...choice u need to make is on "unique images" ...are they ONLY 2 or will you be adding more? If no. of those image is >2,3 I would say relate them...

More importantly is how u have stored your images? Embeded or "linked"?

Linking-saving relative path would improve your "Current File Performance and Viewing" but overall DB peformance would depend no DB connection to where images are located. You have to consider also the size of images.

One nice thing about having Linked images is that you can alsways use Thumb approach and riseze them ...then use those images for "quick" view at which time you will be "calling" significantly smaller image that is NOT stored in DB and will NOT add to its "lag of loading 1000 x 1000 px image"

...sorry if it was an overkill

HTH

take care!

[Ender]

Moving images to a related file can improve performance of that file. It will also make maintenance easier as the file will be considerable smaller (assuming the images are stored in the database).

Sluggishness is also a result of having a large number of fields, a large number of records, or using related fields in finds, calcs, or sorts.

What actions are sluggish for you? What are you using to host the databases? What's the speed of the network? How many records? With answers to these, we might have more suggestions.

[oilcan]

Ender said:

Moving images to a related file can improve performance of that file. It will also make maintenance easier as the file will be considerable smaller (assuming the images are stored in the database).

Sluggishness is also a result of having a large number of fields, a large number of records, or using related fields in finds, calcs, or sorts.

What actions are sluggish for you? What are you using to host the databases? What's the speed of the network? How many records? With answers to these, we might have more suggestions.

seems like most actions have become sluggish in the past several months.

the databases are hosted on a G3, 640MB RAM, OS 8.6. I am admittedly a PC person, and so I don't know how to determine the CPU speed on the server, I only know that it is a G3.

network is 100mbps.

we've 2000+ records in the database. This is what has accumulated in about 5 years, so the database seems to grow on average by about 400 new records per year.

I have also recently implemented a smart range for one of the fields. maybe this is contributing to the problem? perhaps it might be more in order to implement something a little simpler than this...

and as regards linking to image files, this might not be possible, as it may be impossible to find all of the original images entered into the database.

thanks for the responses everyone...i'll continue to work with this problem to see if i can make things better.

[Fitch]

In FileMaker versions prior to 7, the file size limit is 2 gb, so you better be careful about the size of your images. You haven't mentioned the file size of your images, but if you have 1 mb in each record, you may be very close already! Once you hit the limit, your file will be hosed for good.

[Fitch]

2000 records is not enough to bog down much of anything, unless you have thousands of fields or something crazy like that. Or you're sharing the files using OS file sharing or something crazy like that. My bet is that is indeed the pictures that are bogging it down.

This is easily tested by imported the records into a clone, and don't import the pictures, and see how well it performs. You could just move the pictures to a different layout, so they only load on request.