FileMaker is not SQL based dbms, sql queries will be translated to internal functions. Most of the time you will not get performance boost by switching to SQL.
On a smaller set as one off I would just sort the records and look up the las one. I do not think sort will be any faster.
If id is auto incremented you can use GetNextSerialValue ( fileName ; fieldName ).
There is MAX() function in FIleMaker but you can't use it on the current set.
I think, there are custom functions doing this, but I do not think you will get improved performance out of them.
Did you try to run query through JDBC instead of FQL?
Re: "There is MAX() function in FIleMaker but you can't use it on the current set."
No, but you can create a self-join relationship where all records equal all records and then create a calc like Max (related self-join::ID).
Yes, but this will lead to further performance decrease. You will be running MAX on 500000 related records
Maybe following information could be useful.
ExecuteSQL is much faster with a currently committed record.
In all fairness, I can build a really slow find natively in FM as well
As for improving performance of executeSQL calls, you will want to have control over the open state of records. Make sure there are no open, uncommitted records before using this in a script step.
Thanks for your response! Good info there.
Yeah, I was doing that huge data import into FM yesterday and then started messing around with queries. All of them were so slow in the JDBC connection (Java IDE), I quickly gave up and moved into FMP where it wasn't a whole lot better - but I did get results eventually.
I like your idea about GetNextSerial, but it really could be any function I'd want to use to "Find" something in a table.
It sounds like I need to keep looking for a solution, but I wanted to thank you for your posting.
Nope, just the Data Viewer was open. No layouts, etc.
I would have thought that 500,000 records is a walk in the park for Filemaker so I did a test. And on my old i3 laptop it does max on 500,000 related records in 4 seconds which I think is okay.
Tricks are a good unsorted relationship, and an indexed field.
An example file with a good relationship and a max() script step is attached.
MaxTest.fmp12.zip 1.8 MB
4 seconds would be OK. MySQL actually does it in 1 second when I re-checked it.
To Clarify the FileMaker Setup:
1. The table is not a related table.
2. The table has about 200 fields with tons of data (not that that should matter).
3. The table does have an index on the field (ID), in this case.
4. No Layouts are open.
But both you and Allen came up with a workable solution.
I created a self-join (Cartesian join) and now I got the result in less than 3 seconds.
I just needed someway to do some basic data analysis. I should have tried the self-join idea first.
Thanks very much!!!
Yup, that worked well.
Since you were the first to suggest this idea, I marked this as "Correct".
I was thinking about trying this, but I was thinking there must be a way to do a fast query on a table without a self-join. I was baffled with the SQL was so slow given the index and such.
Happily, actually not. About 3 seconds using this technique.
Just now I have also loaded up that same table with 6 gigs of random junk text data spread throughout the records in some other fields.
I expected Filemaker just to operate max() on the indexed field but it appears that it is pulling all the data down including the unrelated stuff... So in-order to find max() on 500,000 records make sure those records are stored in their own separate table and then it will be fast. When it finally finishes "summarising" my field I'll let you know if the slowdown is permanent or temporary...
One last reply... the slow down was temporary! Max() on 500,000 records is still around 4 seconds. So if the majority of the data doesn't change much then max() should work fine. I won't upload the 6 gig database
A bit off topic, but I thought
"In MySQL, this query takes 2 seconds."
also very slow.
I tested using PostgreSQL 9.3 on Windows7 (core i5-2430M),
make 500,000 records in new table
SELECT generate_series(1,500000) AS id INTO data
SELECT MAX(id) FROM data
takes 90ms without making index.
After making index, it become 10...20ms.