Thank you for your post.
First, FileMaker is not a SQL database.
Second, our Development and Testing would like to get a copy of your solution so they can run instruments on it to determine the cause of the slowdown. I have sent you a private message with instructions where to send the file.
TS is right! Not a SQL db, but I've seen some of the big boys choke on "<>" but be ok with "!=" (though they are supposed to be the same thing).
Worth a test here?
so far != doesn't help, it's been 15 minutes still no results
Thank you for testing.
Yes, Filemaker is no SQL*, but ExecuteSQL is provided by filemaker and using it by the book should behave decently, with reasonable performance. It should also be properly optimized, as a vendor provided function should.
So when a such a low performance occurs from correct usage, there's something wrong.
So I created a solution just for that, with a subset of the production tables (I just got the 4 fields involves, whereas in the orginal table there's 40 fields in the records). But I had to duplicate 4 time to be able to find one that's working in a suitable timeframe
the full records count. Workaround query 6,481 seconds (same hosted in production) 93s.
Real query <> couldn't complete I lost patience and killed it after 20 minutes
slow_different_small.fmp12,slow_different_very_small.fmp12 : lost patience
workaround query 0,05s
query with <> 24,529 s : that's 490 x time slower
so if that's linear but I feel it's not, that would mean 52 minutes for slow_different_full.fmp12 to execute
, or 12 hours in my production environment
All files unlocked, login Admin, blank password
You make sure you're in the interface layout (normally that should be the case when you open it)
You press the write less slow query, you clic the Execute Query button, you'll get the time
Then you press the write slow query, you get the one with <> and Execute Query, and then you can spend the weekend with your familly if you use any of the non ultra_small file
* Not investing in ExecuteSQL is a big short sighted strategic mistake, if Filemaker had more solide SQL function, it's market share would sky rocket
have you tried a query like the:
SELECT P.product_id FROM product P
JOIN files_index F ON P.product_id=F.Product_ID and P.supplier_id<>F.Supplier_id
No because adding more than one criteria in a JOIN positively destroys performance in Filemaker, as shown here
Anyways, I tried it on my ultra_small solution and it took 26,824 s, which is about the same as in the <> in the where
Thank you for the files. I have sent everything to our Development and Testing departments for review. When I receive any feedback, I will let you know.
For the record, as I felt, it doesn't seem to be linearly slow, but much more than that.
I launched my full test at 12h20 PM, now it's 17h08 PM = 4h30 ago, and still not finished. Linear 490x slower would have meant it would have been finished in 52 minutes.
Testing confirms this is the same design limitation as the "FQL LEFT OUTER JOIN" link above.
I can confirm that this also unnaceptable, especially as nothing can help predict that's unbelievably bad behavior, and that this needs to be fixed asap