ExecuteSQL in calculated field is SLOW...

[RyanESmith7]

I have an ExecuteSQL statement in a calculated field that finds the most recent Job that was referred by a Company. The calculation looks like this:

 

ExecuteSQL ( "select  MAX("Call Date") 

from Jobs 

where "Referral Company" = ? AND "Sale Amount" > 0"; "" ; ""; Companies::COMPANY ID)

 

I have unchecked "Do not evaluate if all referenced fields are empty". 

 

So here is my dilemma:

 

If "Do not store calculation results -- recalculate when needed" is unchecked, then my data is not always accurate. This field is used in another calculated to determine a "Rating" which is displayed to the user.

 

If "Do not store..." is CHECKED, then my data is accurate, but any layout that has "Rating" displayed is impossibly slow. As in well over 3 minutes to return 45 records. 

 

Any suggestions on how to speed this up?

[jbante]

Rather than doing this via a calculated field that FileMaker will evaluate when it pleases, make the field a stored date field and take control of when it evaluates for yourself. Whenever the Jobs::Call Date field is updated, or a new record is created, update the field in your Companies table at that point.

[comment]

Have you tried using native Filemaker tools for this?

[Wim Decorte]

Adding to what comment and Jeremy are saying:  that is not something that needs to be calculated for *EACH* record in your table.  It should be calculated once during a workflow and stored as static until the next "event" that creates a new record.

 

ExecuteSQL() can be blazingly fast but there are many places where it will extremely slow:

 

- if you have an open record in the table that is targeted, FMS sends all the data to the client; if your table it will carry a big penalty

- certain SQL functions carry the same sort of penalty.  MAX() and COUNT() for instance.  Using those on a table with 1,500,000 records takes over 5 minutes on my test server

- JOINs incur a performance hit too, to a point where (if the tables are big enough) it may be much faster to do two separate SELECT queries and do the joining yourself.

 

For that reason we are shying away from using ExecuteSQL unless we have full control over the record's open state.  So no ExecuteSQL in field definitions, conditional formats, ...

(unless the data sets are really small so that the penalty is not noticeable).

[RyanESmith7]

Thank you very much for your responses. I was shying away with using a stored field as there are many different places where the underlying data can be changed and so I was hesitant to do it that way. I also worry about "forgetting" in the future to set these calculated fields if we add something else that might update these fields. For example, I currently have 3 different layouts where the Sale Amount is updated and I foresee a future script updating it as well (once I have my QuickBooks integration complete).

 

It would be simpler if we could have "field triggers" rather than relying on layout based triggers to update the related fields.

 

At any rate, I will "bite the bullet" and do it the way suggested.

 

Again, thank you!

Ryan

One more thing... comment, what do you mean by "native Filemaker tools"?

Thanks!

[comment]

One more thing... comment, what do you mean by "native Filemaker tools"?

 

I mean relationships and summary/calculation fields. It's difficult to be more specific without having some context, but as an example (or more likely, a guess):

 

To find the date of the most recent job that was referred by a company from the context of Companies, you could define a genuine Filemaker relationship as:

 

Companies::CompanyID = Jobs 2::ReferralCompanyID

 

and a calculation field in the Companies table =

Last ( Jobs 2::Call Date )

Now this should be instant and I suspect that the real problem is with the Sale Amount > 0 condition you have*. We don't know how that is being arrived at. Assuming it's a calculation field =

Sum ( Sales::Amount )

and further assuming that no record in Sales has an amount ≤ 0, we could define a field in the Jobs table =

Case ( Sales::JobsID ; ReferralCompanyID )

and use it as the matchfield in the above relationship, instead of ReferralCompanyID.

 

 

This could prove to be really fast or just as slow as your current solution or anywhere in between - but IMHO it's well worth a try before going to the extreme of de-normalizing your solution and putting your trust in script triggers.

 

 

---

(*) But of course it could equally well be the Rating field, of which we know absolutely nothing.

[RyanESmith7]

Comment... you are awesome. Thank you for being so descriptive. 

[comment]

Please make sure to report your findings, if you do try it.

[RyanESmith7]

Comment... this worked very well for the example that I gave. I'm having a difficult time with another field where the relationship is more complicated. My other field is also a date and the SQL looks like this:

ExecuteSQL ( "SELECT MAX(J."Call Date") 

FROM Jobs J

   LEFT JOIN Job_Contacts_REL JC ON J."Job ID"=JC."JOB ID"

   LEFT JOIN Contacts_Jobs_TO CJT ON JC."CONTACT ID" = CJT."CONTACT ID"

WHERE CJT."COMPANY ID"=?" ; ""; ""; Companies::COMPANY ID )

 

So where the relationship above was very simple, this one is more complicated.

Each of the Jobs has multiple Contacts related to it (through the Job_Contacts_REL). The contact information is then in a TO named Contact_Jobs_TO and that's where I find the Company ID. Again, I'm trying to find the most recent "Call Date" of the job for that Job ID. 

 

I'm guessing that I should put a calculated field on the Job_Contacts_REL that has the Jobs::Call Date brought over.

Then I'm guessing that I need to add a calculated field to the Contacts table to find the most recent Job_Contacts_REL::Job Call Date (assuming that's what I name it). 

And then I'm guessing I can get that through the Companies <-> Contacts relationship. 

 

The first one I reported definitely so a speed performance, so thank you very much for that!

[comment]

I believe you need a relationship starting from Companies (or a TO of Companies from which this calculation will be evaluated) and extending through Contacts and JobContacts to Jobs. Perhaps you just need to tack on a TO of Companies to what you already have:

 

Jobs -< Job_Contacts_REL >- Contact_Jobs_TO >- Companies 4

 

Or maybe you already have a more conventional arrangement of (or similar to):

 

Companies -< Contacts -< JobContacts >- Jobs

 

That is if I understand correctly the purpose of this exercise as "show the latest call date of any job for which (any of) this company's contacts serve as a contact" (phew!).

[RyanESmith7]

LOL, yah, that's essentially what I have. 

 

Jobs -< Job_Contacts_REL >- Contact_Jobs_TO >- Job_Contacts_Companies_TO

 

Job_Contacts_Companies_TO is a table occurance of Companies. From Companies, I'm trying to find the most recent Jobs::Call Date via the above relationship... so far I haven't been successful. Your last sentence is completely accurate!

[RyanESmith7]

Oh Joy! I just found Evaluate this calculation from the context of...