First GTRR is S-L-O-W

[doughemi]

In a remotely-hosted solution, I have a Payments TO which is the many side of a relationship with a Members TO.  I have written a script that uses Go To Related Record from the child table (from a found set of around 2500 out of 50K+ records) to retrieve the related Members records (generating a found set of approximately 2500 records out of 11K).  The found records in Payments are those which are current.  The objective is to find those members whose payments are current.

 

The  first time the script is run after opening the file, the GTRR step takes over 4 minutes.  Thereafter, the script is almost instantaneous. The fields at both ends of the relationship are indexed number fields.

 

Is this expected behavior, or have I made an error somewhere?  If it is expected, is there a workaround to not have that 258 second delay after startup?

[Rick Whitelaw]

Seems odd, yes? Maybe post the script so folks can check it.

[doughemi]

It's one script step:

 

Go to Related Record[show only related records; Match found set; From table: "Members"; Using layout: "MS_member entry" (Members)]

[Wim Decorte]

Well, you're building an actual found set through the GTRR so FMS has to send a bunch of data to the client.  If your only intent is to find members that have current payments you could do all of this in memory through ExecuteSQL without having to build a found set and incur the overhead that comes with that.

Once you have the member list narrowed down then you can ask FMS to send you info for just those members

[doughemi]

Thanks, Wim; that looks like it will work great.

 

But now, I am having a problem with the ExecuteSQL syntax.  When I set a variable  in which I hard code the date, it works fine:

ExecuteSQL (
"SELECT DISTINCT KeyId 
FROM "Payments"
WHERE CurrentEndDate > Date '2013-12-31'
ORDER BY KeyId
" ;"" ; "" )

But, if I calculate the date in another variable in the script, and use the variable as an argument in the ExecuteSQL statement, it doesn't work (result is blank).

ExecuteSQL (
"SELECT DISTINCT KeyId 
FROM "Payments"
WHERE CurrentEndDate > ?
ORDER BY KeyId
" ;"" ; ""; $eoq_date )

$eoq_date shows in the Data Viewer correctly as Date '2013-12-31' .

 

What is the correct syntax?

[Wim Decorte]

You don't need to format $eoq_date to look like "2013-12-31", just pass it in as a real date and FM will take care of the conversion.  So Date( 12 ; 31 ; 2013) as the parameter should work fine provided that the "CurrentEndDate" field is a real date field.