You haven't provided any info about your data.
Chances are one of the select statements you are running on a regular basis is searching on an unindexed and/or unstored field in a large table and has to wait for that calc to evaluate on the x number of records you have.
You can try switching to indexed fields only in the select statement to see if that improves performance (it should).
All of my queries are selecting indexed fields or stored indexed calcs. We also have the performance issue when inserting new data which would not force any unstored calcs to run.
The select query at issue has an inner join with a where and order by clause, but that's pretty standard for SQL.
But in filemaker an inner join requires filemaker to evaluate all of the related data as well to parse a result back to you, so essentially if there are any unstored calcs, summary fields, etc.. in either the parent or the join table, you’ll see a performance hit.
Try this out:
Make a form view in filemaker for the parent table.
Add all of the fields for the parent table and join table onto that layout.
Do a find similar to your select.
Does it take a while to perform the find?
It also matters how MUCH data you have to sift through. Usually ODBC calls to filemaker get slower over time if the table is growing by a large amount daily. EG a complex join may be instantaneous for a few hundred records, but a few thousand is a bit slower, a few hundred thousand is much slower, etc..
Have you tried doing your inserts into an empty temp table and moving them in filemaker afterwards?
A similar find in FileMaker has significantly better performance. The data is coming out of a legacy database that has thousands of records in the main tables so that is probably the source of the slowness. And of course I do have some summary and calculation fields in both tables even though they are not being queried.
What is surprising to me is not just that the OBDC queries run slowly but that it can affect the responsiveness of all of the users accessing the database through FileMaker Pro. I guess the extra load of the queries affects the entire server.
Hmmm, inserting data can be slowed by having many indexed fields as the inserts have to update the indexes as well as the table into which records are being inserted. But I normally see such issues with record counts near a million or more...
I wonder if those indexes are healthy or need to be rebuilt?
How often should indexes be rebuilt?
Maybe never. I've never really figured out why sometimes there's an issue. Usually, the key indicator is that finds or sorts aren't working as expected or you get a record where all the fields are question marks. In your case, isn't the field from an external SQL data source and not an actual FileMaker file or did I misunderstand?
If so, I truly don't know as it would depend on the other Database software.
If the table is a standard FileMaker table, you can use advanced recover options to rebuild the file to rebuild all indexes or you can turn indexing on an off in Manage database to rebuild them one at a time.
In advanced recover options, select "copy file blocks as is" and "rebuild indexes now". To get a new file with rebuilt indexes.
If you are using external account authentication, make sure you have the external accounts listed at the bottom of the accounts list. Failure to do so will cause any ODBC or CWP request to get very slow and when there is so much authentication going on (authenticate for each query in the case of CWP/ODBC), it will cause a bottleneck on your FMS, thus affecting performance of FMP clients as well.
philmodjunk I am pulling data out of a FileMaker database using ODBC so updating the indexes could definitely apply. I upgraded the database to fmp12 last year which would have rebuilt the indexes, I can try again now and see if it makes a difference.
Looks like mikebeargie had the right answer. After testing all my queries, I found one query with an inner join that would pull about 175 to 200 records at a time. It would take 900ms to 1000ms to run. When I removed the join, it dropped to 300ms. Looks like FileMaker Server struggles with receiving queries with joins, though since this is a legacy system, the tables aren't well designed so I'm sure that is part of it.
Thanks for all the help!