I feel your pain. I've run into similar weirdness with ExecuteSQL, where it seems like FileMaker should be able to execute the query that I've thrown at it with ease, and yet it just churns on it. In those cases, what I've been doing is to try figure out what, exactly, FM is struggling with.
In your case, have you tried running the query without the CASE statement to see if that's the problem? For example, maybe hard-wire for one of the unit types to see if it helps at all?
ExecuteSQL ( "SELECT SUM(liter9_CM_TY) from DATA WHERE PRODUCT_KEY = ? and STORE_KEY = ? and Premise = ? and Yr_Mth = ?"; "" ; "" ; PRODUCT_KEY ; STORE_KEY ; Premise ; Yr_Mth - 11 ) ;
If FM can process that one easily, then you might able to rework your code a little to support the CASE in another way. For example, you could either branch your code so that you have multiple ExecuteSQL calls (one for each unit type) or use a dynamically generated ExecuteSQL statement (along with the Evaluate function).
Also - and you've probably already done this - check to see that the fields that you're using in the WHERE clause (PRODUCT_KEY, STORE_KEY, Premise, and Yr_Mth) all have indexes on them.
Hope this helps!
Thanks very much for the advice Tim. I considered letting FMP handle the CASE statement, but since one simple report with my code above would have taken several hours (long scrolls to get one number), I'm reverting to a tried and tested FMP method that does not involve additional table occurrences or relationships.
I'll simply find 12x the number of rows and add the sparse table to form the columns. Find time increase is negligible, sort time is x12, but sums are pretty fast (much MUCH faster than ExecuteSQL). Because I'm not using sums of related values (hops of any length are always a major concern in a huge table) the speed is probably two magnitudes faster. Cleaner relationship graph to boot.
It's great that remote find requests in 60M rows on FMS is really fast. I've taken a product that had been zipped and placed on an FTP server for decades to a FMS hosted version, and the clients love it. FileMaker 12 more than anything made this possible.
Scott can you explain that a little more? 12X the number of rows. I assume that doesn't mean 720M rows? But then what does it mean?
And - your not adding to the graph; but you are adding a "sparse table"?
We fInd 12x the number of rows. We don't find the single month in question for each product (as we'd like to) but we find all 12 months and summarize, hence we find 12x the number of rows and 12x the sort time.
Adding a sparse table: I guess "adding" was ambiguous here -- we are adding the column values of a sparse table, not adding the table.
We would be on average adding 11 NULLs for every one number for a total, hence a sparse table (i.e., sparse matrix with many zero or NULL values).
Not the best way to do things from a strict relational DB standpoint, but one that FileMaker handles quite nicely. We gladly take the 12x hit in sort speed to see a 100x speed increase in adding lots of numbers. I'm not sure of the real speed increase, but when a report goes from unusable to less than a minute (most under 15 seconds) then we gladly adjust for FileMaker's strengths.
It sounds like you gave up on ExecuteSQL, and just summarize a layout now instead
you are correct that if you require "sums of related values", it will be horribly slow
it would be interesting to remove the "SUM" from your query, to check that it "finds" the proper number of records. A SUM over millions can also be a killer
For one solution with multiple SUMS, I ended up exporting the FileMaker records to a "real" SQL database, then queried that for the results. This was over 1000 x faster