Why's my database running slow?

Bandwidth

The obvious difference is bandwidth differences.  If you've used your database only on your local computer you have experienced zero latency — no bottlenecks.  Meaning your database is metaphorically tied to your processor and goes as fast as your computer’s processor.

Now move the database to a remote computer in your office and open it remotely on your computer.  Now your passing through your processor to a restricted network communications bus and through an Ethernet switch that runs at a fraction of the speed of your computer’s processor.  A 1000 MB per second switch probably runs at 1/10 the speed of your processor.  You typically don’t notice that much of a speed loss with your database because FileMaker has been optimized for this kind of use.  Optimize to help but not complete overcome the bottleneck.

Now move the database to a remote server outside your office (like us) and open it remotely on your computer.  Now you’re passing through your processor to a restricted network communications bus, through an Ethernet switch that runs at a fraction of the speed of your processor, to a router that operates at a fraction of the speed of the Ethernet switch.  An internet connection probably runs at 1/1000 the speed of your computer’s processor.  You will notice much more of a speed loss.

Then there’s the typical Comcast connection, as well as all cable service connections, shares bandwidth with your neighbors.  So the more neighbors using your provider’s services the slower service you get.  I'm sure your results will differ when the neighborhood kids get to their computers.

Then there’s the upload/download speed differences.  In our area Comcast offers 8 GB download service.  No where on their site do they mention upload speed numbers.  Which is about 1/10 of the download speed.  FileMaker performs checksums of all data for the best data integrity policies.  Meaning the same amount of data is going back and forth when you do most operations.  If the upload speed is restricted to a much lesser amount this causes much of a slow down.

Cable Versus DSL Video

Database Design

One of the most frequently discovered reason why a database is slow is due to it’s design.  Some examples of this are several database redevelopment projects we did over the past few months.  It was incomprehensible to us how these customers were able to be productive with how slow their databases ran due to the database design.  You may want to consider having a professional FileMaker developer take another look to see if he can squeeze a bit more performance out of your database.  Unnecessary related fields, too large container field contents, unnecessary indexed fields, necessary index fields not index, and more.  There’s a lot of tweaks that may be performed to assist with speed.

Below is an in-depth explanation from Weetbicks on the filemakertoday.com forum

The FileMaker network architecture is built in a way that records must be downloaded from the server to your client machine in order to view them. Hence why it is so slow first time, and faster subsequent times, they are thusly cached on your machine. If the server machine has a slow upload speed, then that affects the speed at which records can be sent to your client machine. Conversely, a slow download speed on client machine will affect the speed at which you can receive those records. the ideal situation is both fast download/upload speeds on both server & client.

Record size is a factor also, because entire records are downloaded, if your records contain many many fields with lots of data, then record sizes are larger and thus take longer to download. The tip here is to choose your storage options wisely on fields. If you have calculation on the table that are indexed but don't need to be, consider making them unstored instead. You cannot request and get a single field value from a record from the FileMaker server, if you request any single field, the entire record is sent to you.

Having said that there are a few factors which contribute to slow speed. First is your internet connection, obviously the slower the connection, the slower records are downloaded and the slower they display.

Next is what is actually on this layout. Portals are interesting things. Lets take a portal that shows 10 rows. If that portal is unsorted, and the relationship the portal uses is also unsorted, then FileMaker server will just send you the first 10 records visible for the portal. No sort means no complexity. When you scroll down in the portal, your client sends a request for the next record, which it receives, that's all fine.

If, however, the portal/relationship is sorted, FileMaker sends you EVERY SINGLE RECORD through that relationship in one hit. this is because sorting occurs on the client-side, and in order to sort records it must have every single record. In short, sorting is a huge performance hit on slow remote connections. If you can avoid sorting portals on your layouts, I suggest you do so. Either that or modify the relationship to get fewer records...

Again everything comes back to what FileMaker server has to send you. So container fields with large graphics will affect performance. Scripts that create a record can be slow if that new record appears in a sorted portal, the portal re-sorts itself.

A refresh window script step which has flush cache turned on is a real No-no for databases accessed over remote connection. A flush cache essentially clears your client-side cache of all records, which means you need to re-download all the records all over again, avoid it where you can.

Any calculation fields, calculations (ie conditional formatting) that appears on the layout can have a performance hit. Every field/record the calculation references must be downloaded. If you have a calculation that references fields on the current record only, then that is ok. If you have a calc though that references related records, this can be a big performance hit - again if the relationship it references is sorted, then all records are downloaded otherwise the calculation will not know which record is the correct record it is referencing.

Aggregate functions in calculations are also an issue, ie List/Count/Sum and so on. These require all referenced records they are listing/summing/counting to be downloaded to the client.

Us

For certain the issues are not with our servers or bandwidth.  Our average bandwidth usage over the past 6 months is less than 25% and our servers are Dell's and Apple’s finest — optimized for FileMaker use.