Share this post

Link to post

Share on other sites

ExecuteSQL works outside the FileMaker relationships; a TO name serves merely as an access point into its base table, and you're always starting out with the complete table. So it doesn't matter where you look at.

Being in the right context is only necessary to have any FileMaker Expressions used as parameters evaluated correctly. If you're not using those, then it also doesn't matter where you're looking from.

The reason for my question is that the data loads very quick on the local PC for this one layout, but when hosted the page loads extremely slow. One of the reason, I determined was a value list where the customer has built over 500 within the list. You didn't notice it on the local PC but again when the datafile was hosted, as you navigated to, and as you tried to scroll, the value list would build for each record ( apparently ) causing this painful slow down on this particular page. We disabling the Value List.

The same problem with the SQL page, the page loads extremely slow ( 42 SQL Select Statements ) as compared to the ( 42 SQL Select Statements ) on the local PC, the page loads extremely fast. But when we host the file … painful slow down ( loading ) and I have been unsuccessful in finding the field (s) that may be causing the delay. ( Strangely enough, the SQL SELECT queries the same table where the value list of 500 values had been assigned through a relationship within the Value List Index. Though the "Value List" is no longer assigned to that layout ... to that field … the SQL Select queries the same table where the initial slow down occurs.

Could the value list still be effecting the Table ?? Even thought ( as far as I can tell ) the value list is not assign to a field on that particular table occurrence anymore.

Link to post

Share on other sites

Are users typically on a Calendar layout when they enter new Remedy records?

No … they click the calendar button and to print.

I use to have a Portal Filter for each day based on the field "cDateofFirstPortal" + … That was way to slow, and I started my SQL Tutorials trying to speed up the calendar and "It works great!" on the local machine but on the networks hosting Filemaker, it is still very slow.

I kept the field name "cDateOfFirstPortal" since it is just a calculation to determine the first square of the calendar it is defined as follows:

Link to post

Share on other sites

1) your calculation seems to include at least one global field and that means that it can only be calculated on the client. And since you are calling it 42 times you are causing a lot of data to flow between the client and the server.

Find a way to set that field as part of a scripted workflow instead of using calculations.

2) ExecuteSQL may force FMS to send the client all the data in the Remedy table if there is an open record in Remedy. Can't tell if that is the case in your scenario but it is one of the main reasons why I stay away from ExecuteSQL in field definitions and conditional formats etc: you have no control over when the calc fires so you can not prevent it with open records.

Since you are only seeing the slowdown when the files is hosted, it is a clear sign that the inefficiency is in data flow to and from the server.

1

Share this post

Link to post

Share on other sites

I get no emails from this forum even though I've selected the "Follow" and I've been in my preferences and I believe I've selected all that is appropriate but yet now email … so anyway, could you force the forum engine to email me when you reply this this

tomtheriault@me.com

Global field for my date reference only all other fields not global.

So are you saying that if I have a script that sets the 42 fields ( one record on the calendar table ) it will be faster ?? Rather than having the SQL in a calculated field 42 times ?? Here is the calc in each fields … ( Not global )

ExecuteSQL ( "

Select ClientFNameLInitial, RemedyPotencyCalendar

From Calendar_Dates_to_Remedy_DateStart

Where DateStart = ? and ClientType = ?" ; " - " ; "" ;

cDateOfFirstPortal + 1 ; Type )

Also, can you clarify "OPEN" records. I am helping a guy who developed his file now he needs help continue his development and I would have to say that I am to sure how the client is one the file. If they select a field and not do anything while the consult with a client, I would imagine that you would have an open record. But, can you clarify more, the "OPEN" record concern I would be grateful

Share this post

Link to post

Share on other sites

Yes, I'm saying that if you can get away from your calculations being unstored (which I'm guessing they are) then it will be faster.

Unstored calcs will be calculated on-the-fly whenever FM needs them and you do not have much control over when that is.

Open records: when the user puts the cursor in the field: that does not open a record. When they start editing a field then FM opens the record (or if the developer takes control and uses the "open record" script step).

Obviously I need more understanding … calendar page load is still very slow. Not sure what I'm doing wrong here. I am still referencing three global fields ( Month, Day, Year ) within the "cDateOfFirstPortal" which is within each SQL Select Statement. That has not changed.

I've moved the SQL Select Statement's that were calculating within each field ( 42 of them ) to a "SET FIELD" step within a Script … 42 set fields script steps as instructed.

​So, Wim … One Table, One Record … that is may calendar with … 42 fields.

Since I am still very slow … can you restate the global issue with Filemaker again. I have 42 set fields within a script now ( field calculations removed ) … but within each SQL STATEMENT I am still referencing this field "cDateOfFirstPortal" ( which has three global fields within it … Let Statement Above ).

​Are you saying that each "Set Field" script step is forcing some "LOAD" on the FM SERVER due to the the fact that I'm referencing this "cDateOfFirstPortal" within each SQL Statement ?? which the global fields resides on the clients machine … which does what again to the FM server

And this is why I have a "SLOW" page load. Yes / No / Maybe ?? :-)

If I change the three global field ( Month, Day, Year ) to three number fields ( since I only have one record ) the calendar will still build. Will I have any issues with more than one user on the calendar ??

1) your calculation seems to include at least one global field and that means that it can only be calculated on the client. And since you are calling it 42 times you are causing a lot of data to flow between the client and the server.

Find a way to set that field as part of a scripted workflow instead of using calculations.

The 3 global fields ( Month, Day, Year ) are set on page load. So I shouldn't need to worry about globals on my calendar … Yes / No ??

Thank you for any insight here, and thank for your previous suggestions.

Share this post

Link to post

Share on other sites

At this point we're all confused I think, this discussion is a little too abstract without being able to inspect the file and the actual flow.

To troubleshoot:

- if the file is local and all is fast then it is a data-flow issue because of the calcs & scripting that forces client and server to communicate too much and send too much data

- with the file hosted again, disable the 42 set fields and see if it is fast. My guess is that it will be fast. That demonstrates that the issue is caused by the calc that is used in the Set Field

- disable only 21 set fields and see if the performance penalty is linear

- in general; find a more efficient way to produce the Set Field calc

From looking at the cDateOfFirstPortal field; I guess it is going to e unstored because of the globals. Does it need to be a field? Does it need to be calculated? Sounds like something that could be set fairly easily as part of the workflow and not calculated

Link to post

Share on other sites

I'm helping the person who developed the datafile and it is difficult to work within his methodology ...

Reviewing your suggestions …

Yes … Each Day I add the calendars slows.

Yes … Both fields within the SQL statement can not be stored … Filemaker will not let me. And the information is coming from another relationship.

Last questions promise … If I replace each SQL with an actual relationship map for each day rather than using SQL which if I'm not mistaken, looks at the entire data table 42 times, would I be able to work around the slow page load by using relationships for each day instead ?? No filtering ??

Thank you.

Tom :-)

Share this post

Link to post

Share on other sites

If I replace each SQL with an actual relationship map for each day rather than using SQL which if I'm not mistaken, looks at the entire data table 42 times, would I be able to work around the slow page load by using relationships for each day instead ?

That's a very good question. Why don't you try it and let us know; I'd like to know if my bet is correct.

In any case, 42 relationships - native or SQL - is no way to construct a calendar - see:

The only records that have actual data in the REMEDIES table are "Set" with the ID's … so customer ID, Consultation ID, Pharmacy ID ...

And from these key fields, he populate the other data fields with calculation / relationships which are non - indexing and non storing ...

He wants to list in the calendar those records on the days the remedy were written with "client name and potency" and now my recent underdeveloped understanding of the Calendar results which are based on un-stored calculations fields have cause an extreme slow down.

My Solution … I'm thinking … have the user ( owner of the database ) create ( write data / copy / import / set … what ever ) to the Calendar Table every time a record is created on the Remedy Table where there would be NO RELATIONSHIP anymore ( only real data ) than use a self relationship to the DATA on the Calendar Table via a GLOBAL DATE FIELD.

That should speed things up … Yes ? No ?

Thank you so much for your guidance and assistance here. ( Everybody ) I am grateful.

Tom :-)

Share this post

Link to post

Share on other sites

I am afraid you have lost me completely. I have no idea what this is about. For example, you say that the REMEDIES table has Customer ID, Consultation ID, etc. - but just before that you said that each record holds "Customer Name :: Relationship to Customer Table ( No-Indexing allowed )". This makes no sense whatsoever, esp. since you cannot have a relationship with no indexing.

In any case, I don't see what all this has to do with the calendar issue. A calendar is a device to display events on a grid. An event is something that has a date attribute. Is there a table there that has a date field? If so, that would be your "events" table. And the calendar should be able to show the "real" data from this table, with no need for "copy / import / set … what ever".