I have an SQL query I am trying to run through the ExecuteSQL query.  The problem I'm having (I think) is that because it's running off a table with over 5 million rows and I'm trying to grab a sum, not just the data, each query is taking anywhere from 30-60 seconds to run - and during this script, I have to run the query up to 36 times (monthly sums going back three years), so that causes this report to take too long in my opinion.

 

I *think* I would be able to speed it up if I can do the heavy lifting (i.e. the sum) across a subset of the 5 million rows, and logic tells me that I should be able to do that by using a 2nd Select statement (select sum(my field) from (select (subset of records) from Table)), but I can't seem to get anything to work within the ExecuteSQL statement.

 

Multiple web searches have returned no usable results for me, so I'm not sure if I'm searching for the wrong thing, or if ExecuteSQL just can't handle this.

 

Does anyone know of a way for me to be able to accomplish this, or a if I'm just totally barking up the wrong tree?

 

 

You can just use a "where" clause to limit the sum, i.e.:

   select sum(my field) from table where serialNum >= 5000 serialNum < 10000

 

So, you could sum up each month (with the appropriate where clause, not the above) using something like that. 

I've found that "Fast Summaries" method is faster than SQL: http://fmforums.com/forum/topic/85844-only-show-5-highest-values/

Thanks, I'll take a look.

So I'm trying out the Fast Summaries method, but I'm getting stuck on one thing:

 

When I go to the Line items layout in order to do the find, Filemaker gets stuck trying to update the two summary fields - on nearly 6 million records.  I hoped that not including the summary field on the layout would keep that from happening, but that doesn't seem to have an effect.

 

Is there something I'm missing that would allow me to utilize this method without FileMaker trying to update the summary fields before the GetSummary function is called?

Oops, I think I figured out my problem.  I had the table sorted on the wrong field.

 

It does still take a little while to update when I do a new search now, but only when my search is over a large selection - when I do a tiny subset, it's lightning fast.

Do you have a Freeze Window step near the beginning of your script? That can help speed it up a little bit. Another idea is to use a layout that has no fields on it - I'm not sure if this matters if you use a Freeze Window step, but if you are looking to maximize performance you might want to give it a try.

Also, you can use an undocumented function Get ( UTCmSecs ) to get sub-second time, to test how long the script takes to run.