Hello,

 

I have been using FMS to host my databases for quite some time and I have had relatively no issues. Recently, we have had a bunch of employees adding records and data. As the database gets larger it seems that we are having problems. 

 

Right now the server is running on a dedicated machine with 4 VCPU cores, 4 Gb Ram, and windows server 2008 R2. The data is transferred via the hospital network that the database is hosted in, which is relatively fast.

 

The problem I am having is so bizarre. All of my scripts run slow except when they have the script step "New Record request". Even clicking the button "New record" causes the database to load for a good 3 minutes just to add a record. Finds are fast, loops are fast, GTRR are fast. Only adding a new record is extremely slow. But here is the catch, its slow only on the first attempt. I am guessing the server caches something into the host machine for faster processes but I wouldn't think a simple add record would be so tedious. 

 

Does anyone know how I can make this faster? Maybe running the add record request on the server itself? 

 

Thank you

start with going through the FMS event log and the system logs to find out if it crashes or not.

 

Also make sure FMS is configured to collect statistics in a log.  Those logs will tell you exactly where the bottleneck is and you need to know what it is before you can start fixing.

 

In the table where the slow record creation happens: how many fields?  How many of those are stored calculations, how many are unstored calculations?  How many summary fields?

Does the layout where the user is on have a lot of those calc fields and summary fields on it?

Hi Wim

 

Thank you for the response. I will check out the logs and to answer your questions:

1. there are about 36 fields with 6 of them being calculations. 3 of which are unstored

2. there are no summary fields

3. the layout where the user is on has only 1 calculation field. 

 

Should I restart the server you think? 

 

Thanks again

 

Should I restart the server you think? 

 

 

Can't hurt.  Also check to see if there are any DMP files in the FMS logs folder, that would indicate some sort of crash.

Your server is anemic.  It should have at least 12 GB of RAM.  And about 4 GB of that should be reserved for cache.  Also check the cache settings for your workstation copies of FIleMaker Pro.  And also be sure that Server hardware including NIC card and network connection are both good.

 

Also, please be sure that all versions of both Server and Client are running the latest v-rev.

 

Steven

Thanks Steve,

 

12 Gb really? The server we are using is using an intel xeon processor. All of the hardware is brand new and server compatible. We did spend $3000 on it. I may ask the hospital to bump up the Ram on it. I think the most my department will do is double it. My cache setting on my worker computer is 256mb, is that good? The amount of cache used by FM server is 2Gb. In the meantime, I am going to se if there are any calculation fields I can dump. It seems that when only adding records on that table, is there a long delay the first time. So I will play around with them there. 

 

Can't hurt.  Also check to see if there are any DMP files in the FMS logs folder, that would indicate some sort of crash.

 

Wim where would I find this?

Okay so, I requested to have the RAM in my server to be upgraded to 12 Gb. I hope this works or else its a waste of valuable $$$. In the meantime, do you think a PSOS script would make the related record creation faster? Adding a record after the first attempt requires no wait at all. I know that filemaker server makes me have to download all of the records the first time, which is making the first attempt to creating a record very long but is there a way to just make it download the related records. I don't see why its taking so long when my database is only about 100mb. 

Using PSoS is just circumventing the problem instead of solving it.  You have to solve the issue otherwise it may crop up in different areas.  Something is clearly off, the database is small, the table is narrow so record creation should be very fast.

 

You probably need to have somebody look over your shoulder at the database, scripts and certainly the deployment (FMS stats etc).

I have no idea. Every script is fast and it just seems that on the first new record request for this table is taking the longest. The table does have about 10 indexed fields. (foreign key fields) because i have a portal on the main screen that shows all the related records from a bunch of other tables. But would indexed fields make it this slow when its only storing a number?

But would indexed fields make it this slow when its only storing a number?

 

If you populate all 10 fields, then that process will be a bit slower than not having those values, since FM has to update the index for those fields.  But it should not be slow to the extent that you describe, we're taling milliseconds, not minutes.

 

Something is definitely wrong with your file or your deployment.

Can you look at this cloned file? The specific relationship is very easy pk_main---->fk_entries. Adding a record in the entries table is what is slowing down. a cloned file seems to be fine when adding records but using my current one with many records does not. I added a fake patient to show you how my db works 

Seems i can't upload on here...uploader keeps saying that no file was selected for upload... weird

This tread can be closed. Found out it was the SQL calculation I had lying around in the table which was taking up the time. So happy it turned out to be that simple. 

This tread can be closed. Found out it was the SQL calculation I had lying around in the table which was taking up the time. So happy it turned out to be that simple. 

 

This was a key part of my devcon presentation... ExecuteSQL() calls have an enormous performance hit when they run on a table that the user has an open record in.  That obviously happens when you create a new record.

This behavior is why you should very carefully consider whether to use ExecuteSQL() calls in:

- field calcs

- conditional formats

- tooltips

- custom functions used in any of the above

 

Basically anywhere that you don't have full control over the open state of the record.

Thanks wim. That got really frustrating for a bit