relationship slowness

classic Classic list List threaded Threaded
26 messages Options
12
Reply | Threaded
Open this post in threaded view
|

relationship slowness

Keith M. Rettig
I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.

I have the following relationship criteria;
   promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
   promotional_codes::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed
   promotional_codes::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed

All five relationship fields are indexed.  promotional_codes_redeemed has 7 fields (also all indexed).  300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.

Is there a smarter way to do this?
Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If so, I suppose I need to cull the promotional codes that have been redeemed.

Thanks for any other ideas.

Keith.
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Mark Rubenstein
Keith,

I have heard from reliable sources that relationships that use <= or >= are much slower than relationships that use =.
And this has been my experience too.

Mark
-------------------------------------------------------------------------------------------------------------------------------------
Mark Rubenstein
FileMaker Certified Developer  |  Easy As Pi  |  [hidden email]  |  (847) 869-6020  |  www.easyaspi.com
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Jonathan Fletcher-2
Mark’s right. Inequalities can really slow you down.

1. You can convert those inequalities to equalities asynchronously, though. You can have a scheduled server script that runs and updates a status on all records that have changed or been used recently. You then use that status in an equality relationship.

2. Another approach would be to update the status of the individual record at the time that it is used. Then use equality relationships when you need to access them.

Shun the inequalities, shun, shu-uu-uu-un…

j.





> On Jun 20, 2017, at 5:09 PM, Mark Rubenstein <[hidden email]> wrote:
>
> Keith,
>
> I have heard from reliable sources that relationships that use <= or >= are much slower than relationships that use =.
> And this has been my experience too.
>
> Mark
> -------------------------------------------------------------------------------------------------------------------------------------
> Mark Rubenstein
> FileMaker Certified Developer  |  Easy As Pi  |  [hidden email]  |  (847) 869-6020  |  www.easyaspi.com
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Jonathan Fletcher
[hidden email]

Kentuckiana FileMaker Developers Group
Next Meeting: 6/27/17

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

RE: relationship slowness

Jonn Howell at DataExperience
In reply to this post by Mark Rubenstein
And multiple predicates will slow render as well....

All of this may not affect you, if you have moderate size structure, limited
records, large RAM on server, good NIC, multiple processors,  (theme usage
on WebDirect), indexed searches,  and a good WAN....each factor affects
performance.

All must be considered.

Thanks,

Jonn


Jonn Howell
DataExperience, Inc.
Certified FileMaker Developer
dba Bootcamp for FileMaker
[hidden email]
www.dataexperience.com
www.youtube.com/dataexperience
805-368-4748



-----Original Message-----
From: FMPexperts [mailto:[hidden email]] On Behalf
Of Mark Rubenstein
Sent: Tuesday, June 20, 2017 2:09 PM
To: [hidden email]
Subject: Re: relationship slowness

Keith,

I have heard from reliable sources that relationships that use <= or >= are
much slower than relationships that use =.
And this has been my experience too.

Mark
----------------------------------------------------------------------------
---------------------------------------------------------
Mark Rubenstein
FileMaker Certified Developer  |  Easy As Pi  |  [hidden email]  |  (847)
869-6020  |  www.easyaspi.com
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Richard DeShong
In reply to this post by Keith M. Rettig
What happens if you switch to a script?

Script: IsValid

$Code = <the promo code>
$When = <the redeem timestamp>
freeze window
goto layout <promo code util>
enter find mode
set field promocode to $Code
perform find
$IsValid = Case(
    get( foundcount ) <> 1 ; False ;
    $When < code_publish ; False ;
    $When > code_expire ; False ;
    True
)
goto layout <original>
Exit Script [$IsValid]

Notes:
1) The "promo code util" layout should have NO fields;
2) The IsValid Case statement could use the positive tests first like:
    get(foundcount) = 1  and  $When >= publish  and $When <= expire


On 6/20/2017 2:00 PM, Keith M. Rettig wrote:

> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>
> I have the following relationship criteria;
>     promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>     promotional_codes::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed
>     promotional_codes::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed
>
> All five relationship fields are indexed.  promotional_codes_redeemed has 7 fields (also all indexed).  300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>
> Is there a smarter way to do this?
> Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If so, I suppose I need to cull the promotional codes that have been redeemed.
>
> Thanks for any other ideas.
>
> Keith.
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Richard DeShong
Logic Tools
510-642-5123 office
925-285-1088 cell

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Richard DeShong
My previous post about using a script with a utility (no fields) layout
has been my preferred method over creating a relationship to answer a
question.

Another (newer) method would be to use the SQL functionality to get the
details of the record.  You could then build the question into a Custom
Function to return your answer.

Both methods do not need a special relationship which, if not needed for
another purpose, can then be removed from your schema (keeping it simple).


On 6/20/2017 4:12 PM, Richard DeShong wrote:

> What happens if you switch to a script?
>
> Script: IsValid
>
> $Code = <the promo code>
> $When = <the redeem timestamp>
> freeze window
> goto layout <promo code util>
> enter find mode
> set field promocode to $Code
> perform find
> $IsValid = Case(
>    get( foundcount ) <> 1 ; False ;
>    $When < code_publish ; False ;
>    $When > code_expire ; False ;
>    True
> )
> goto layout <original>
> Exit Script [$IsValid]
>
> Notes:
> 1) The "promo code util" layout should have NO fields;
> 2) The IsValid Case statement could use the positive tests first like:
>    get(foundcount) = 1  and  $When >= publish  and $When <= expire
>
>
> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>> I have to monitor usage of promotional codes to ensure that they are
>> not used more than the prescribed number of times (though some are
>> unlimited) within two given timestamps (code_publish and
>> code_expire).   It works perfectly but the relationship from the
>> promotional_codes table to the promotional_codes_redeemed table seems
>> slow; I am wondering if there is a better way to do it.  “Slow” is
>> defined as when I check on a code I have to wait for multiple seconds
>> for the record to display while a modal window tells me “Find in
>> progress, Performing query”.
>>
>> I have the following relationship criteria;
>>     promotional_codes::promocode  =  promotional_codes_redeemed
>> ::promocode
>>     promotional_codes::code_publish  <=
>> promotional_codes_redeemed::timestamp_redeemed
>>     promotional_codes::code_expire  >=
>> promotional_codes_redeemed::timestamp_redeemed
>>
>> All five relationship fields are indexed. promotional_codes_redeemed
>> has 7 fields (also all indexed). 300,000 records in the
>> promotional_codes table and 80,000 in the promotional_codes_redeemed
>> table.
>>
>> Is there a smarter way to do this?
>> Is this simply a 300,000 times 80,000 problem?  [24 billion…] If so,
>> I suppose I need to cull the promotional codes that have been redeemed.
>>
>> Thanks for any other ideas.
>>
>> Keith.
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>

--
Richard DeShong
Logic Tools
510-642-5123 office
925-285-1088 cell

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Michael Kupietz
In reply to this post by Keith M. Rettig
At the very least, it seems like once a promo code has been redeemed,
the publish date (and maybe the expire date, that's up to you - let's
assume so for purposes of this explanation) is not likely to change
again. So I would try using lookups or autoenter calcs to copy that
value/those values locally to the promotional_codes_redeemed record at
the time it is redeemed. Then I would have a calculated field

promocode_redeemed_foreignKey =

  if (promotional_codes_redeemed::code_publish  <=  
promotional_codes_redeemed::timestamp_redeemed and
promotional_codes_redeemed::code_expire  >=  
promotional_codes_redeemed::timestamp_redeemed,
promotional_codes_redeemed::timestamp_redeemed,"")

Note that I used all local promotional_codes_redeemed fields, not the
promotional_codes.

Then you only need one relationship criteria,
promotional_codes::promocode  = promocode_redeemed_foreignKey. All other
calculation is done at the time each code is redeemed, so your database
isn't doing up to 160,000 mathematical calculations every time you try
to check a code, it's just doing a single index match on each side
because the heavy lifting was already done at record entry time.

Mike

--
Michael E. Kupietz
IT consulting solutions for FileMaker Pro • Web • OS X
https://www.kupietz.com
415.545.8743


On 20 Jun 2017, at 14:00, Keith M. Rettig wrote:

> I have to monitor usage of promotional codes to ensure that they are
> not used more than the prescribed number of times (though some are
> unlimited) within two given timestamps (code_publish and code_expire).
>   It works perfectly but the relationship from the promotional_codes
> table to the promotional_codes_redeemed table seems slow; I am
> wondering if there is a better way to do it.  “Slow” is defined as
> when I check on a code I have to wait for multiple seconds for the
> record to display while a modal window tells me “Find in progress,
> Performing query”.
>
> I have the following relationship criteria;
>    promotional_codes::promocode  =  promotional_codes_redeemed
> ::promocode
>    promotional_codes::code_publish  <=  
> promotional_codes_redeemed::timestamp_redeemed
>    promotional_codes::code_expire  >=  
> promotional_codes_redeemed::timestamp_redeemed
>
> All five relationship fields are indexed.  promotional_codes_redeemed
> has 7 fields (also all indexed).  300,000 records in the
> promotional_codes table and 80,000 in the promotional_codes_redeemed
> table.
>
> Is there a smarter way to do this?
> Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If
> so, I suppose I need to cull the promotional codes that have been
> redeemed.
>
> Thanks for any other ideas.
>
> Keith.
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Correction. Re: relationship slowness

Michael Kupietz
Sorry, I made a typo. The calculation should be:

   if (promotional_codes_redeemed::code_publish  <=  
promotional_codes_redeemed::timestamp_redeemed and
  promotional_codes_redeemed::code_expire  >=  
promotional_codes_redeemed::timestamp_redeemed,
  promotional_codes_redeemed::promocode,"") /* <---- note change here */


not

>  if (promotional_codes_redeemed::code_publish  <=  
> promotional_codes_redeemed::timestamp_redeemed and
> promotional_codes_redeemed::code_expire  >=  
> promotional_codes_redeemed::timestamp_redeemed,
> promotional_codes_redeemed::timestamp_redeemed,"")

Mike

--
Michael E. Kupietz
IT consulting solutions for FileMaker Pro • Web • OS X
https://www.kupietz.com
415.545.8743


On 21 Jun 2017, at 12:25, Michael Kupietz wrote:

> At the very least, it seems like once a promo code has been redeemed,
> the publish date (and maybe the expire date, that's up to you - let's
> assume so for purposes of this explanation) is not likely to change
> again. So I would try using lookups or autoenter calcs to copy that
> value/those values locally to the promotional_codes_redeemed record at
> the time it is redeemed. Then I would have a calculated field
>
> promocode_redeemed_foreignKey =
>
>  if (promotional_codes_redeemed::code_publish  <=  
> promotional_codes_redeemed::timestamp_redeemed and
> promotional_codes_redeemed::code_expire  >=  
> promotional_codes_redeemed::timestamp_redeemed,
> promotional_codes_redeemed::timestamp_redeemed,"")
>
> Note that I used all local promotional_codes_redeemed fields, not the
> promotional_codes.
>
> Then you only need one relationship criteria,
> promotional_codes::promocode  = promocode_redeemed_foreignKey. All
> other calculation is done at the time each code is redeemed, so your
> database isn't doing up to 160,000 mathematical calculations every
> time you try to check a code, it's just doing a single index match on
> each side because the heavy lifting was already done at record entry
> time.
>
> Mike
> --
> Michael E. Kupietz
> IT consulting solutions for FileMaker Pro • Web • OS X
> https://www.kupietz.com
> 415.545.8743
>
>
> On 20 Jun 2017, at 14:00, Keith M. Rettig wrote:
>
>> I have to monitor usage of promotional codes to ensure that they are
>> not used more than the prescribed number of times (though some are
>> unlimited) within two given timestamps (code_publish and
>> code_expire).   It works perfectly but the relationship from the
>> promotional_codes table to the promotional_codes_redeemed table seems
>> slow; I am wondering if there is a better way to do it.  “Slow”
>> is defined as when I check on a code I have to wait for multiple
>> seconds for the record to display while a modal window tells me
>> “Find in progress, Performing query”.
>>
>> I have the following relationship criteria;
>>    promotional_codes::promocode  =  promotional_codes_redeemed
>> ::promocode
>>    promotional_codes::code_publish  <=  
>> promotional_codes_redeemed::timestamp_redeemed
>>    promotional_codes::code_expire  >=  
>> promotional_codes_redeemed::timestamp_redeemed
>>
>> All five relationship fields are indexed.  promotional_codes_redeemed
>> has 7 fields (also all indexed).  300,000 records in the
>> promotional_codes table and 80,000 in the promotional_codes_redeemed
>> table.
>>
>> Is there a smarter way to do this?
>> Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If
>> so, I suppose I need to cull the promotional codes that have been
>> redeemed.
>>
>> Thanks for any other ideas.
>>
>> Keith.
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Keith M. Rettig
I like where you went however I think there is an issue [that you all didn’t know about].  Please correct me if I am wrong as I don’t think I have completely grok-d your idea.

A portion of the promotional codes are reusable.  That is, the code itself is an employee’s employeeID and it can be used twice per month only at the kiosk at their store (and the employees indeed try to use their code at the kiosks at other stores :) ).  So every month a script comes along and updates the code_publish and the code_expire timestamps.  Now I don’t want to lose the ability to know that the employee has used the code 12 times total but the record does need to know that it has only been used once for this month and thus allow the second use of the code (if at the correct kiosk).

As a result of some previous emails (and the act of writing the original email), I switched the redeemed counter field to not count uses once the promotional code is marked as availability=0.  I have a script that runs every so often that goes through all of the promotional codes that are use_is_limited=1 and availability=1 and checks to see if they were redeemed the number of times that are allowed (use_limit=n).  The purpose of availability=1 is I can have unlimited codes that are on or off easily such as "15%off” without changing the code_publish and code_expire timestamps.  I can also pre-load a batch of codes and not have them available until the customer calls me to say they want all the codes to work  [I have several customers using the promotional_codes table and each customer has its own particular way of using promotional codes].  Once I turned off the counting for all of the single_use codes already redeemed, the table sped up a lot.  But of course we add tens of thousands of codes every other month…so presumably the slow down point could be reached again…however, codes are continually also being redeemed.

I reread your email and it clicked this time.
The fact that the code_publish and code_expire timestamps could change means your suggestion doesn’t work perfectly.  That said, it is not normal that they change so the value of your suggestion might still be there for me.

Thanks so much to all for the conversation!

Keith.



> On Jun 21, 2017, at 1:07 PM, Michael Kupietz <[hidden email]> wrote:
>
> Sorry, I made a typo. The calculation should be:
>
>  if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and
> promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed,
> promotional_codes_redeemed::promocode,"") /* <---- note change here */
>
>
> not
>
>> if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed, promotional_codes_redeemed::timestamp_redeemed,"")
>
> Mike
>
> --
> Michael E. Kupietz
> IT consulting solutions for FileMaker Pro • Web • OS X
> https://www.kupietz.com
> 415.545.8743
>
>
> On 21 Jun 2017, at 12:25, Michael Kupietz wrote:
>
>> At the very least, it seems like once a promo code has been redeemed, the publish date (and maybe the expire date, that's up to you - let's assume so for purposes of this explanation) is not likely to change again. So I would try using lookups or autoenter calcs to copy that value/those values locally to the promotional_codes_redeemed record at the time it is redeemed. Then I would have a calculated field
>>
>> promocode_redeemed_foreignKey =
>>
>> if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed, promotional_codes_redeemed::timestamp_redeemed,"")
>>
>> Note that I used all local promotional_codes_redeemed fields, not the promotional_codes.
>>
>> Then you only need one relationship criteria, promotional_codes::promocode  = promocode_redeemed_foreignKey. All other calculation is done at the time each code is redeemed, so your database isn't doing up to 160,000 mathematical calculations every time you try to check a code, it's just doing a single index match on each side because the heavy lifting was already done at record entry time.
>>
>> Mike
>> --
>> Michael E. Kupietz
>> IT consulting solutions for FileMaker Pro • Web • OS X
>> https://www.kupietz.com
>> 415.545.8743
>>
>>
>> On 20 Jun 2017, at 14:00, Keith M. Rettig wrote:
>>
>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>
>>> I have the following relationship criteria;
>>>   promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>   promotional_codes::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed
>>>   promotional_codes::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed
>>>
>>> All five relationship fields are indexed.  promotional_codes_redeemed has 7 fields (also all indexed).  300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>
>>> Is there a smarter way to do this?
>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>
>>> Thanks for any other ideas.
>>>
>>> Keith.
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

RE: relationship slowness

Richard DeShong
In reply to this post by Richard DeShong
Hi Keith,
You sent this directly to me (not the list), but I think that it is important for the list to know, so I am replying to the list.  I also see that you sent a second post where you mention more details about your particular system.

In answer to your question about layouts, "will a scripted find work on a layout with no fields", the answer is "yes", if you are using Set Field to set the criteria.  Using Set Field, you can do everything with your tables using layouts with no fields.

In my systems, I create at least one blank layout for each table.  I basically use these "utility" layouts for everything in my scripts.  So I end up with User Interface (UI) layouts and these "get the work done" utility layouts.  I use the utility layouts for creating, finding, updating, and deleting records.

And to your other question, "... because it allows the script to run that much faster?", the answer is "yes".   Using this technique rivals PSOS for speed when you need to process a bunch of records.

The reason I might have more than one for a given table is relationships.  If I am using the layout to answer a question about 1 or more records, then I might also want to know about related records.  So I need to make sure the layout is based on the Table Object that has those relationships.

Hope this helps with your system.
--
Richard

-----Original Message-----
From: Keith M. Rettig [mailto:[hidden email]]
Sent: Wednesday, June 21, 2017 6:00 PM
To: Richard DeShong <[hidden email]>
Subject: Re: relationship slowness

You confused me a bit there…

In your previous post, the script says to go to utility layout and do a find.  But now you say there should not be any fields on the utility layout.  Will a scripted find work on a layout with no fields showing?
Do you use this strategy because it allows the script to run that much faster?  When I first read your post I was assuming that you sent it to a utility layout as a place “to go run a script that follows a bunch of logic but doesn’t need to interact with any data until the end” so that the portion not needing data would run faster.  But if I can do a scripted find on a layout without fields showing for speed and manipulate that data as well as a result of the script then I have a lot of rework to do!

Keith.


> On Jun 20, 2017, at 4:28 PM, Richard DeShong <[hidden email]> wrote:
>
> My previous post about using a script with a utility (no fields) layout has been my preferred method over creating a relationship to answer a question.
>
> Another (newer) method would be to use the SQL functionality to get the details of the record.  You could then build the question into a Custom Function to return your answer.
>
> Both methods do not need a special relationship which, if not needed for another purpose, can then be removed from your schema (keeping it simple).
>
>
> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>> What happens if you switch to a script?
>>
>> Script: IsValid
>>
>> $Code = <the promo code>
>> $When = <the redeem timestamp>
>> freeze window
>> goto layout <promo code util>
>> enter find mode
>> set field promocode to $Code
>> perform find
>> $IsValid = Case(
>>   get( foundcount ) <> 1 ; False ;
>>   $When < code_publish ; False ;
>>   $When > code_expire ; False ;
>>   True
>> )
>> goto layout <original>
>> Exit Script [$IsValid]
>>
>> Notes:
>> 1) The "promo code util" layout should have NO fields;
>> 2) The IsValid Case statement could use the positive tests first like:
>>   get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>
>>
>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>
>>> I have the following relationship criteria;
>>>    promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>    promotional_codes::code_publish  <= promotional_codes_redeemed::timestamp_redeemed
>>>    promotional_codes::code_expire  >= promotional_codes_redeemed::timestamp_redeemed
>>>
>>> All five relationship fields are indexed. promotional_codes_redeemed has 7 fields (also all indexed). 300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>
>>> Is there a smarter way to do this?
>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…] If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>
>>> Thanks for any other ideas.
>>>
>>> Keith.
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>
> --
> Richard DeShong
> Logic Tools
> 510-642-5123 office
> 925-285-1088 cell
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Joe Gilson-2
Richard,

Thank you…just learned how to implement a speed improvement.

Joe
 
On Jun 22, 2017, at 11:33 AM, Richard DeShong <[hidden email]> wrote:

Hi Keith,
You sent this directly to me (not the list), but I think that it is important for the list to know, so I am replying to the list.  I also see that you sent a second post where you mention more details about your particular system.

In answer to your question about layouts, "will a scripted find work on a layout with no fields", the answer is "yes", if you are using Set Field to set the criteria.  Using Set Field, you can do everything with your tables using layouts with no fields.

In my systems, I create at least one blank layout for each table.  I basically use these "utility" layouts for everything in my scripts.  So I end up with User Interface (UI) layouts and these "get the work done" utility layouts.  I use the utility layouts for creating, finding, updating, and deleting records.

And to your other question, "... because it allows the script to run that much faster?", the answer is "yes".   Using this technique rivals PSOS for speed when you need to process a bunch of records.

The reason I might have more than one for a given table is relationships.  If I am using the layout to answer a question about 1 or more records, then I might also want to know about related records.  So I need to make sure the layout is based on the Table Object that has those relationships.

Hope this helps with your system.
--
Richard

-----Original Message-----
From: Keith M. Rettig [mailto:[hidden email]]
Sent: Wednesday, June 21, 2017 6:00 PM
To: Richard DeShong <[hidden email]>
Subject: Re: relationship slowness

You confused me a bit there…

In your previous post, the script says to go to utility layout and do a find.  But now you say there should not be any fields on the utility layout.  Will a scripted find work on a layout with no fields showing?
Do you use this strategy because it allows the script to run that much faster?  When I first read your post I was assuming that you sent it to a utility layout as a place “to go run a script that follows a bunch of logic but doesn’t need to interact with any data until the end” so that the portion not needing data would run faster.  But if I can do a scripted find on a layout without fields showing for speed and manipulate that data as well as a result of the script then I have a lot of rework to do!

Keith.


> On Jun 20, 2017, at 4:28 PM, Richard DeShong <[hidden email]> wrote:
>
> My previous post about using a script with a utility (no fields) layout has been my preferred method over creating a relationship to answer a question.
>
> Another (newer) method would be to use the SQL functionality to get the details of the record.  You could then build the question into a Custom Function to return your answer.
>
> Both methods do not need a special relationship which, if not needed for another purpose, can then be removed from your schema (keeping it simple).
>
>
> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>> What happens if you switch to a script?
>>
>> Script: IsValid
>>
>> $Code = <the promo code>
>> $When = <the redeem timestamp>
>> freeze window
>> goto layout <promo code util>
>> enter find mode
>> set field promocode to $Code
>> perform find
>> $IsValid = Case(
>>  get( foundcount ) <> 1 ; False ;
>>  $When < code_publish ; False ;
>>  $When > code_expire ; False ;
>>  True
>> )
>> goto layout <original>
>> Exit Script [$IsValid]
>>
>> Notes:
>> 1) The "promo code util" layout should have NO fields;
>> 2) The IsValid Case statement could use the positive tests first like:
>>  get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>
>>
>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>
>>> I have the following relationship criteria;
>>>   promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>   promotional_codes::code_publish  <= promotional_codes_redeemed::timestamp_redeemed
>>>   promotional_codes::code_expire  >= promotional_codes_redeemed::timestamp_redeemed
>>>
>>> All five relationship fields are indexed. promotional_codes_redeemed has 7 fields (also all indexed). 300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>
>>> Is there a smarter way to do this?
>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…] If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>
>>> Thanks for any other ideas.
>>>
>>> Keith.
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>
> --
> Richard DeShong
> Logic Tools
> 510-642-5123 office
> 925-285-1088 cell
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

RE: relationship slowness

Richard DeShong
In reply to this post by Keith M. Rettig
Hi Keith,
So I see that you have a "parent" table, promotional_codes (PC), and a "child" table, promotional_codes_redeemed (PCR) where, it seems, you keep the details of each code redemption.

Based on your descriptions, I do not see a need for a relationship that uses the data range (publish-expire).  I was thinking that you were asking about point-of-sale transactions.  That is, a user is at a kiosk, enters a promo code, and you want to find out if it is valid.

Using the blank layout technique,  create a blank layout based on the PC table, call in "PC Util", and another called "PCR Util".
When the user enters the promo code, you need to switch to PC Util, find the code, check if it is valid, and return back to the UI layout with the result and the details of the discount.  No relationship needed.  Once the user continues with the purchase, switch to PCR Util, create a record with the details of the transaction, and then switch to PC Util and update the use count, and finally return back to the UI layout.

Of course, this is based on how I am thinking about your system based on your description and on how I know promo codes work in sales systems, in general.


-----Original Message-----
From: Keith M. Rettig Sent: Wednesday, June 21, 2017 6:03 PM

I like where you went however I think there is an issue [that you all didn’t know about].  Please correct me if I am wrong as I don’t think I have completely grok-d your idea.

A portion of the promotional codes are reusable.  That is, the code itself is an employee’s employeeID and it can be used twice per month only at the kiosk at their store (and the employees indeed try to use their code at the kiosks at other stores :) ).  So every month a script comes along and updates the code_publish and the code_expire timestamps.  Now I don’t want to lose the ability to know that the employee has used the code 12 times total but the record does need to know that it has only been used once for this month and thus allow the second use of the code (if at the correct kiosk).

As a result of some previous emails (and the act of writing the original email), I switched the redeemed counter field to not count uses once the promotional code is marked as availability=0.  I have a script that runs every so often that goes through all of the promotional codes that are use_is_limited=1 and availability=1 and checks to see if they were redeemed the number of times that are allowed (use_limit=n).  The purpose of availability=1 is I can have unlimited codes that are on or off easily such as "15%off” without changing the code_publish and code_expire timestamps.  I can also pre-load a batch of codes and not have them available until the customer calls me to say they want all the codes to work  [I have several customers using the promotional_codes table and each customer has its own particular way of using promotional codes].  Once I turned off the counting for all of the single_use codes already redeemed, the table sped up a lot.  But of course we add tens of thousands of codes every other month…so presumably the slow down point could be reached again…however, codes are continually also being redeemed.

I reread your email and it clicked this time.
The fact that the code_publish and code_expire timestamps could change means your suggestion doesn’t work perfectly.  That said, it is not normal that they change so the value of your suggestion might still be there for me.

Thanks so much to all for the conversation!

Keith.



> On Jun 21, 2017, at 1:07 PM, Michael Kupietz <[hidden email]> wrote:
>
> Sorry, I made a typo. The calculation should be:
>
>  if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and
> promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed,
> promotional_codes_redeemed::promocode,"") /* <---- note change here */
>
>
> not
>
>> if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed, promotional_codes_redeemed::timestamp_redeemed,"")
>
> Mike
>
> --
> Michael E. Kupietz
> IT consulting solutions for FileMaker Pro • Web • OS X
> https://www.kupietz.com
> 415.545.8743
>
>
> On 21 Jun 2017, at 12:25, Michael Kupietz wrote:
>
>> At the very least, it seems like once a promo code has been redeemed, the publish date (and maybe the expire date, that's up to you - let's assume so for purposes of this explanation) is not likely to change again. So I would try using lookups or autoenter calcs to copy that value/those values locally to the promotional_codes_redeemed record at the time it is redeemed. Then I would have a calculated field
>>
>> promocode_redeemed_foreignKey =
>>
>> if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed, promotional_codes_redeemed::timestamp_redeemed,"")
>>
>> Note that I used all local promotional_codes_redeemed fields, not the promotional_codes.
>>
>> Then you only need one relationship criteria, promotional_codes::promocode  = promocode_redeemed_foreignKey. All other calculation is done at the time each code is redeemed, so your database isn't doing up to 160,000 mathematical calculations every time you try to check a code, it's just doing a single index match on each side because the heavy lifting was already done at record entry time.
>>
>> Mike
>> --
>> Michael E. Kupietz
>> IT consulting solutions for FileMaker Pro • Web • OS X
>> https://www.kupietz.com
>> 415.545.8743
>>
>>
>> On 20 Jun 2017, at 14:00, Keith M. Rettig wrote:
>>
>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>
>>> I have the following relationship criteria;
>>>   promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>   promotional_codes::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed
>>>   promotional_codes::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed
>>>
>>> All five relationship fields are indexed.  promotional_codes_redeemed has 7 fields (also all indexed).  300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>
>>> Is there a smarter way to do this?
>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>
>>> Thanks for any other ideas.
>>>
>>> Keith.
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Jonathan Fletcher-2
In reply to this post by Joe Gilson-2
Another one not to miss that Richard had in his earlier script was the Freeze Window step. Even if your target layout has a lot of fields on it, this will save you much time.

j.


> On Jun 22, 2017, at 11:56 AM, Joe Gilson <[hidden email]> wrote:
>
> Richard,
>
> Thank you…just learned how to implement a speed improvement.
>
> Joe
>
> On Jun 22, 2017, at 11:33 AM, Richard DeShong <[hidden email]> wrote:
>
> Hi Keith,
> You sent this directly to me (not the list), but I think that it is important for the list to know, so I am replying to the list.  I also see that you sent a second post where you mention more details about your particular system.
>
> In answer to your question about layouts, "will a scripted find work on a layout with no fields", the answer is "yes", if you are using Set Field to set the criteria.  Using Set Field, you can do everything with your tables using layouts with no fields.
>
> In my systems, I create at least one blank layout for each table.  I basically use these "utility" layouts for everything in my scripts.  So I end up with User Interface (UI) layouts and these "get the work done" utility layouts.  I use the utility layouts for creating, finding, updating, and deleting records.
>
> And to your other question, "... because it allows the script to run that much faster?", the answer is "yes".   Using this technique rivals PSOS for speed when you need to process a bunch of records.
>
> The reason I might have more than one for a given table is relationships.  If I am using the layout to answer a question about 1 or more records, then I might also want to know about related records.  So I need to make sure the layout is based on the Table Object that has those relationships.
>
> Hope this helps with your system.
> --
> Richard
>
> -----Original Message-----
> From: Keith M. Rettig [mailto:[hidden email]]
> Sent: Wednesday, June 21, 2017 6:00 PM
> To: Richard DeShong <[hidden email]>
> Subject: Re: relationship slowness
>
> You confused me a bit there…
>
> In your previous post, the script says to go to utility layout and do a find.  But now you say there should not be any fields on the utility layout.  Will a scripted find work on a layout with no fields showing?
> Do you use this strategy because it allows the script to run that much faster?  When I first read your post I was assuming that you sent it to a utility layout as a place “to go run a script that follows a bunch of logic but doesn’t need to interact with any data until the end” so that the portion not needing data would run faster.  But if I can do a scripted find on a layout without fields showing for speed and manipulate that data as well as a result of the script then I have a lot of rework to do!
>
> Keith.
>
>
>> On Jun 20, 2017, at 4:28 PM, Richard DeShong <[hidden email]> wrote:
>>
>> My previous post about using a script with a utility (no fields) layout has been my preferred method over creating a relationship to answer a question.
>>
>> Another (newer) method would be to use the SQL functionality to get the details of the record.  You could then build the question into a Custom Function to return your answer.
>>
>> Both methods do not need a special relationship which, if not needed for another purpose, can then be removed from your schema (keeping it simple).
>>
>>
>> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>>> What happens if you switch to a script?
>>>
>>> Script: IsValid
>>>
>>> $Code = <the promo code>
>>> $When = <the redeem timestamp>
>>> freeze window
>>> goto layout <promo code util>
>>> enter find mode
>>> set field promocode to $Code
>>> perform find
>>> $IsValid = Case(
>>> get( foundcount ) <> 1 ; False ;
>>> $When < code_publish ; False ;
>>> $When > code_expire ; False ;
>>> True
>>> )
>>> goto layout <original>
>>> Exit Script [$IsValid]
>>>
>>> Notes:
>>> 1) The "promo code util" layout should have NO fields;
>>> 2) The IsValid Case statement could use the positive tests first like:
>>> get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>>
>>>
>>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>>
>>>> I have the following relationship criteria;
>>>>  promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>>  promotional_codes::code_publish  <= promotional_codes_redeemed::timestamp_redeemed
>>>>  promotional_codes::code_expire  >= promotional_codes_redeemed::timestamp_redeemed
>>>>
>>>> All five relationship fields are indexed. promotional_codes_redeemed has 7 fields (also all indexed). 300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>>
>>>> Is there a smarter way to do this?
>>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…] If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>>
>>>> Thanks for any other ideas.
>>>>
>>>> Keith.
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>
>>
>> --
>> Richard DeShong
>> Logic Tools
>> 510-642-5123 office
>> 925-285-1088 cell
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Jonathan Fletcher
[hidden email]

Kentuckiana FileMaker Developers Group
Next Meeting: 6/27/17

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Joe Gilson-2
+1 Jonathan

Thanks

On Jun 22, 2017, at 12:07 PM, Jonathan Fletcher <[hidden email]> wrote:

Another one not to miss that Richard had in his earlier script was the Freeze Window step. Even if your target layout has a lot of fields on it, this will save you much time.

j.


> On Jun 22, 2017, at 11:56 AM, Joe Gilson <[hidden email]> wrote:
>
> Richard,
>
> Thank you…just learned how to implement a speed improvement.
>
> Joe
>
> On Jun 22, 2017, at 11:33 AM, Richard DeShong <[hidden email]> wrote:
>
> Hi Keith,
> You sent this directly to me (not the list), but I think that it is important for the list to know, so I am replying to the list.  I also see that you sent a second post where you mention more details about your particular system.
>
> In answer to your question about layouts, "will a scripted find work on a layout with no fields", the answer is "yes", if you are using Set Field to set the criteria.  Using Set Field, you can do everything with your tables using layouts with no fields.
>
> In my systems, I create at least one blank layout for each table.  I basically use these "utility" layouts for everything in my scripts.  So I end up with User Interface (UI) layouts and these "get the work done" utility layouts.  I use the utility layouts for creating, finding, updating, and deleting records.
>
> And to your other question, "... because it allows the script to run that much faster?", the answer is "yes".   Using this technique rivals PSOS for speed when you need to process a bunch of records.
>
> The reason I might have more than one for a given table is relationships.  If I am using the layout to answer a question about 1 or more records, then I might also want to know about related records.  So I need to make sure the layout is based on the Table Object that has those relationships.
>
> Hope this helps with your system.
> --
> Richard
>
> -----Original Message-----
> From: Keith M. Rettig [mailto:[hidden email]]
> Sent: Wednesday, June 21, 2017 6:00 PM
> To: Richard DeShong <[hidden email]>
> Subject: Re: relationship slowness
>
> You confused me a bit there…
>
> In your previous post, the script says to go to utility layout and do a find.  But now you say there should not be any fields on the utility layout.  Will a scripted find work on a layout with no fields showing?
> Do you use this strategy because it allows the script to run that much faster?  When I first read your post I was assuming that you sent it to a utility layout as a place “to go run a script that follows a bunch of logic but doesn’t need to interact with any data until the end” so that the portion not needing data would run faster.  But if I can do a scripted find on a layout without fields showing for speed and manipulate that data as well as a result of the script then I have a lot of rework to do!
>
> Keith.
>
>
>> On Jun 20, 2017, at 4:28 PM, Richard DeShong <[hidden email]> wrote:
>>
>> My previous post about using a script with a utility (no fields) layout has been my preferred method over creating a relationship to answer a question.
>>
>> Another (newer) method would be to use the SQL functionality to get the details of the record.  You could then build the question into a Custom Function to return your answer.
>>
>> Both methods do not need a special relationship which, if not needed for another purpose, can then be removed from your schema (keeping it simple).
>>
>>
>> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>>> What happens if you switch to a script?
>>>
>>> Script: IsValid
>>>
>>> $Code = <the promo code>
>>> $When = <the redeem timestamp>
>>> freeze window
>>> goto layout <promo code util>
>>> enter find mode
>>> set field promocode to $Code
>>> perform find
>>> $IsValid = Case(
>>> get( foundcount ) <> 1 ; False ;
>>> $When < code_publish ; False ;
>>> $When > code_expire ; False ;
>>> True
>>> )
>>> goto layout <original>
>>> Exit Script [$IsValid]
>>>
>>> Notes:
>>> 1) The "promo code util" layout should have NO fields;
>>> 2) The IsValid Case statement could use the positive tests first like:
>>> get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>>
>>>
>>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>>
>>>> I have the following relationship criteria;
>>>> promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>> promotional_codes::code_publish  <= promotional_codes_redeemed::timestamp_redeemed
>>>> promotional_codes::code_expire  >= promotional_codes_redeemed::timestamp_redeemed
>>>>
>>>> All five relationship fields are indexed. promotional_codes_redeemed has 7 fields (also all indexed). 300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>>
>>>> Is there a smarter way to do this?
>>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…] If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>>
>>>> Thanks for any other ideas.
>>>>
>>>> Keith.
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>
>>
>> --
>> Richard DeShong
>> Logic Tools
>> 510-642-5123 office
>> 925-285-1088 cell
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Jonathan Fletcher
[hidden email]

Kentuckiana FileMaker Developers Group
Next Meeting: 6/27/17

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Keith M. Rettig
In reply to this post by Richard DeShong
You are basically describing how it is done at our self-serve shredding kiosk customer’s kiosks (codes are local to the kiosk).  So I do agree with you there.

But my issue is for managing promotional codes for our coffee kiosk customer; they have 600+ kiosks that check on my server for promotional codes being valid.  For them, I have to keep track of every attempt (whether the code has expired or not) so that they can track the movement of their codes through the social media space.  [college kids try to cheat the system the most; first heavily at a local kiosk and then it spreads to other colleges]

The coffee kiosk people have the most demanding criteria for if a code is valid or not; twelve different variables can be in play; the kiosk itself, the operator or two might have its own codes, the region or two might have a code, a channel (such as grocery stores versus libraries) or two might have a code and all of those criterion have up to two that won’t allow a code to work (a walmart code can be used in any walmart except those in the west region for instance).  And of course the code can be unlimited use, single use, or limited use (good for 100 times for instance).  And, as previously described, will work between June 1, 2017 @ 12:00:01 and June 30, 2017 @ 23:59:59.

So I actually have two child tables; redeemed and submitted.  The submitted table is pretty slick (in my opinion) with creating a record, comparing all of the criterion (they can be passed, failed, or not a concern) and determining if the code is good or not.  The redeemed table records are created by a different server that is processing the point of sale logs; if a code was used the poslog processer steps out and submits the record to this server.  When a customer attempts to use a promotional code, the kiosk submits a record with code being used and the kiosk identifier; the record is scored and returned as good or not (I could tell the kiosk as to why the code failed but this customer is not concerned with that information at this point).

So that is why the relationships have remained live.  I probably could decrease a counter in the promotional code record when the redeemed record was created but given some vagaries of how the point of sale logs are submitted (there are a good percentage of duplicates and other issues that are out of my control), it just seemed too risky.



I just love these kind of conversations.
This is what is good about an open layout for a work space.
Having to listen to your coworker’s country music when you prefer death metal is not such a good feature of said open work space.
Thankfully I have a workspace of one :)  and I have all of you.

Keith.


> On Jun 22, 2017, at 9:01 AM, Richard DeShong <[hidden email]> wrote:
>
> Hi Keith,
> So I see that you have a "parent" table, promotional_codes (PC), and a "child" table, promotional_codes_redeemed (PCR) where, it seems, you keep the details of each code redemption.
>
> Based on your descriptions, I do not see a need for a relationship that uses the data range (publish-expire).  I was thinking that you were asking about point-of-sale transactions.  That is, a user is at a kiosk, enters a promo code, and you want to find out if it is valid.
>
> Using the blank layout technique,  create a blank layout based on the PC table, call in "PC Util", and another called "PCR Util".
> When the user enters the promo code, you need to switch to PC Util, find the code, check if it is valid, and return back to the UI layout with the result and the details of the discount.  No relationship needed.  Once the user continues with the purchase, switch to PCR Util, create a record with the details of the transaction, and then switch to PC Util and update the use count, and finally return back to the UI layout.
>
> Of course, this is based on how I am thinking about your system based on your description and on how I know promo codes work in sales systems, in general.
>
>
> -----Original Message-----
> From: Keith M. Rettig Sent: Wednesday, June 21, 2017 6:03 PM
>
> I like where you went however I think there is an issue [that you all didn’t know about].  Please correct me if I am wrong as I don’t think I have completely grok-d your idea.
>
> A portion of the promotional codes are reusable.  That is, the code itself is an employee’s employeeID and it can be used twice per month only at the kiosk at their store (and the employees indeed try to use their code at the kiosks at other stores :) ).  So every month a script comes along and updates the code_publish and the code_expire timestamps.  Now I don’t want to lose the ability to know that the employee has used the code 12 times total but the record does need to know that it has only been used once for this month and thus allow the second use of the code (if at the correct kiosk).
>
> As a result of some previous emails (and the act of writing the original email), I switched the redeemed counter field to not count uses once the promotional code is marked as availability=0.  I have a script that runs every so often that goes through all of the promotional codes that are use_is_limited=1 and availability=1 and checks to see if they were redeemed the number of times that are allowed (use_limit=n).  The purpose of availability=1 is I can have unlimited codes that are on or off easily such as "15%off” without changing the code_publish and code_expire timestamps.  I can also pre-load a batch of codes and not have them available until the customer calls me to say they want all the codes to work  [I have several customers using the promotional_codes table and each customer has its own particular way of using promotional codes].  Once I turned off the counting for all of the single_use codes already redeemed, the table sped up a lot.  But of course we add tens of thousands of codes every other month…so presumably the slow down point could be reached again…however, codes are continually also being redeemed.
>
> I reread your email and it clicked this time.
> The fact that the code_publish and code_expire timestamps could change means your suggestion doesn’t work perfectly.  That said, it is not normal that they change so the value of your suggestion might still be there for me.
>
> Thanks so much to all for the conversation!
>
> Keith.
>
>
>
>> On Jun 21, 2017, at 1:07 PM, Michael Kupietz <[hidden email]> wrote:
>>
>> Sorry, I made a typo. The calculation should be:
>>
>> if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and
>> promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed,
>> promotional_codes_redeemed::promocode,"") /* <---- note change here */
>>
>>
>> not
>>
>>> if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed, promotional_codes_redeemed::timestamp_redeemed,"")
>>
>> Mike
>>
>> --
>> Michael E. Kupietz
>> IT consulting solutions for FileMaker Pro • Web • OS X
>> https://www.kupietz.com
>> 415.545.8743
>>
>>
>> On 21 Jun 2017, at 12:25, Michael Kupietz wrote:
>>
>>> At the very least, it seems like once a promo code has been redeemed, the publish date (and maybe the expire date, that's up to you - let's assume so for purposes of this explanation) is not likely to change again. So I would try using lookups or autoenter calcs to copy that value/those values locally to the promotional_codes_redeemed record at the time it is redeemed. Then I would have a calculated field
>>>
>>> promocode_redeemed_foreignKey =
>>>
>>> if (promotional_codes_redeemed::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed and promotional_codes_redeemed::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed, promotional_codes_redeemed::timestamp_redeemed,"")
>>>
>>> Note that I used all local promotional_codes_redeemed fields, not the promotional_codes.
>>>
>>> Then you only need one relationship criteria, promotional_codes::promocode  = promocode_redeemed_foreignKey. All other calculation is done at the time each code is redeemed, so your database isn't doing up to 160,000 mathematical calculations every time you try to check a code, it's just doing a single index match on each side because the heavy lifting was already done at record entry time.
>>>
>>> Mike
>>> --
>>> Michael E. Kupietz
>>> IT consulting solutions for FileMaker Pro • Web • OS X
>>> https://www.kupietz.com
>>> 415.545.8743
>>>
>>>
>>> On 20 Jun 2017, at 14:00, Keith M. Rettig wrote:
>>>
>>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>>
>>>> I have the following relationship criteria;
>>>>  promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>>  promotional_codes::code_publish  <=  promotional_codes_redeemed::timestamp_redeemed
>>>>  promotional_codes::code_expire  >=  promotional_codes_redeemed::timestamp_redeemed
>>>>
>>>> All five relationship fields are indexed.  promotional_codes_redeemed has 7 fields (also all indexed).  300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>>
>>>> Is there a smarter way to do this?
>>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>>
>>>> Thanks for any other ideas.
>>>>
>>>> Keith.
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Michael Kupietz
In reply to this post by Keith M. Rettig
To be honest, I think I'm not following all the intricacies, but just a
thought, maybe whenever the publish/expire dates are updated, a script
(or script trigger, if they're updated manually) could run for each one
that updates and does a GTRR and updates the existing
promotional_codes_redeemed records for that code with the new dates.

Mike

--
Michael E. Kupietz
IT consulting solutions for FileMaker Pro • Web • OS X
https://www.kupietz.com
415.545.8743


On 21 Jun 2017, at 18:03, Keith M. Rettig wrote:

> I like where you went however I think there is an issue [that you all
> didn’t know about].  Please correct me if I am wrong as I don’t
> think I have completely grok-d your idea.
>
> A portion of the promotional codes are reusable.  That is, the code
> itself is an employee’s employeeID and it can be used twice per
> month only at the kiosk at their store (and the employees indeed try
> to use their code at the kiosks at other stores :) ).  So every month
> a script comes along and updates the code_publish and the code_expire
> timestamps.  Now I don’t want to lose the ability to know that the
> employee has used the code 12 times total but the record does need to
> know that it has only been used once for this month and thus allow the
> second use of the code (if at the correct kiosk).
>
> As a result of some previous emails (and the act of writing the
> original email), I switched the redeemed counter field to not count
> uses once the promotional code is marked as availability=0.  I have a
> script that runs every so often that goes through all of the
> promotional codes that are use_is_limited=1 and availability=1 and
> checks to see if they were redeemed the number of times that are
> allowed (use_limit=n).  The purpose of availability=1 is I can have
> unlimited codes that are on or off easily such as "15%off” without
> changing the code_publish and code_expire timestamps.  I can also
> pre-load a batch of codes and not have them available until the
> customer calls me to say they want all the codes to work  [I have
> several customers using the promotional_codes table and each customer
> has its own particular way of using promotional codes].  Once I turned
> off the counting for all of the single_use codes already redeemed, the
> table sped up a lot.  But of course we add tens of thousands of codes
> every other month…so presumably the slow down point could be reached
> again…however, codes are continually also being redeemed.
>
> I reread your email and it clicked this time.
> The fact that the code_publish and code_expire timestamps could change
> means your suggestion doesn’t work perfectly.  That said, it is not
> normal that they change so the value of your suggestion might still be
> there for me.
>
> Thanks so much to all for the conversation!
>
> Keith.
>
>
>
>> On Jun 21, 2017, at 1:07 PM, Michael Kupietz <[hidden email]>
>> wrote:
>>
>> Sorry, I made a typo. The calculation should be:
>>
>>  if (promotional_codes_redeemed::code_publish  <=  
>> promotional_codes_redeemed::timestamp_redeemed and
>> promotional_codes_redeemed::code_expire  >=  
>> promotional_codes_redeemed::timestamp_redeemed,
>> promotional_codes_redeemed::promocode,"") /* <---- note change here
>> */
>>
>>
>> not
>>
>>> if (promotional_codes_redeemed::code_publish  <=  
>>> promotional_codes_redeemed::timestamp_redeemed and
>>> promotional_codes_redeemed::code_expire  >=  
>>> promotional_codes_redeemed::timestamp_redeemed,
>>> promotional_codes_redeemed::timestamp_redeemed,"")
>>
>> Mike
>>
>> --
>> Michael E. Kupietz
>> IT consulting solutions for FileMaker Pro • Web • OS X
>> https://www.kupietz.com
>> 415.545.8743
>>
>>
>> On 21 Jun 2017, at 12:25, Michael Kupietz wrote:
>>
>>> At the very least, it seems like once a promo code has been
>>> redeemed, the publish date (and maybe the expire date, that's up to
>>> you - let's assume so for purposes of this explanation) is not
>>> likely to change again. So I would try using lookups or autoenter
>>> calcs to copy that value/those values locally to the
>>> promotional_codes_redeemed record at the time it is redeemed. Then I
>>> would have a calculated field
>>>
>>> promocode_redeemed_foreignKey =
>>>
>>> if (promotional_codes_redeemed::code_publish  <=  
>>> promotional_codes_redeemed::timestamp_redeemed and
>>> promotional_codes_redeemed::code_expire  >=  
>>> promotional_codes_redeemed::timestamp_redeemed,
>>> promotional_codes_redeemed::timestamp_redeemed,"")
>>>
>>> Note that I used all local promotional_codes_redeemed fields, not
>>> the promotional_codes.
>>>
>>> Then you only need one relationship criteria,
>>> promotional_codes::promocode  = promocode_redeemed_foreignKey. All
>>> other calculation is done at the time each code is redeemed, so your
>>> database isn't doing up to 160,000 mathematical calculations every
>>> time you try to check a code, it's just doing a single index match
>>> on each side because the heavy lifting was already done at record
>>> entry time.
>>>
>>> Mike
>>> --
>>> Michael E. Kupietz
>>> IT consulting solutions for FileMaker Pro • Web • OS X
>>> https://www.kupietz.com
>>> 415.545.8743
>>>
>>>
>>> On 20 Jun 2017, at 14:00, Keith M. Rettig wrote:
>>>
>>>> I have to monitor usage of promotional codes to ensure that they
>>>> are not used more than the prescribed number of times (though some
>>>> are unlimited) within two given timestamps (code_publish and
>>>> code_expire).   It works perfectly but the relationship from the
>>>> promotional_codes table to the promotional_codes_redeemed table
>>>> seems slow; I am wondering if there is a better way to do it.  
>>>> “Slow” is defined as when I check on a code I have to wait for
>>>> multiple seconds for the record to display while a modal window
>>>> tells me “Find in progress, Performing query”.
>>>>
>>>> I have the following relationship criteria;
>>>>   promotional_codes::promocode  =  promotional_codes_redeemed
>>>> ::promocode
>>>>   promotional_codes::code_publish  <=  
>>>> promotional_codes_redeemed::timestamp_redeemed
>>>>   promotional_codes::code_expire  >=  
>>>> promotional_codes_redeemed::timestamp_redeemed
>>>>
>>>> All five relationship fields are indexed.  
>>>> promotional_codes_redeemed has 7 fields (also all indexed).  
>>>> 300,000 records in the promotional_codes table and 80,000 in the
>>>> promotional_codes_redeemed table.
>>>>
>>>> Is there a smarter way to do this?
>>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…]  If
>>>> so, I suppose I need to cull the promotional codes that have been
>>>> redeemed.
>>>>
>>>> Thanks for any other ideas.
>>>>
>>>> Keith.
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Keith M. Rettig
In reply to this post by Jonathan Fletcher-2
Do the “Freeze Window” and Richard’s “Utility Layouts” (with no fields) strategies matter if the database if the script is actually running on the server?
I would think the the UI and layouts only slow things down for the scripts when the script is running locally.

Keith.

> On Jun 22, 2017, at 9:07 AM, Jonathan Fletcher <[hidden email]> wrote:
>
> Another one not to miss that Richard had in his earlier script was the Freeze Window step. Even if your target layout has a lot of fields on it, this will save you much time.
>
> j.
>
>
>> On Jun 22, 2017, at 11:56 AM, Joe Gilson <[hidden email]> wrote:
>>
>> Richard,
>>
>> Thank you…just learned how to implement a speed improvement.
>>
>> Joe
>>
>> On Jun 22, 2017, at 11:33 AM, Richard DeShong <[hidden email]> wrote:
>>
>> Hi Keith,
>> You sent this directly to me (not the list), but I think that it is important for the list to know, so I am replying to the list.  I also see that you sent a second post where you mention more details about your particular system.
>>
>> In answer to your question about layouts, "will a scripted find work on a layout with no fields", the answer is "yes", if you are using Set Field to set the criteria.  Using Set Field, you can do everything with your tables using layouts with no fields.
>>
>> In my systems, I create at least one blank layout for each table.  I basically use these "utility" layouts for everything in my scripts.  So I end up with User Interface (UI) layouts and these "get the work done" utility layouts.  I use the utility layouts for creating, finding, updating, and deleting records.
>>
>> And to your other question, "... because it allows the script to run that much faster?", the answer is "yes".   Using this technique rivals PSOS for speed when you need to process a bunch of records.
>>
>> The reason I might have more than one for a given table is relationships.  If I am using the layout to answer a question about 1 or more records, then I might also want to know about related records.  So I need to make sure the layout is based on the Table Object that has those relationships.
>>
>> Hope this helps with your system.
>> --
>> Richard
>>
>> -----Original Message-----
>> From: Keith M. Rettig [mailto:[hidden email]]
>> Sent: Wednesday, June 21, 2017 6:00 PM
>> To: Richard DeShong <[hidden email]>
>> Subject: Re: relationship slowness
>>
>> You confused me a bit there…
>>
>> In your previous post, the script says to go to utility layout and do a find.  But now you say there should not be any fields on the utility layout.  Will a scripted find work on a layout with no fields showing?
>> Do you use this strategy because it allows the script to run that much faster?  When I first read your post I was assuming that you sent it to a utility layout as a place “to go run a script that follows a bunch of logic but doesn’t need to interact with any data until the end” so that the portion not needing data would run faster.  But if I can do a scripted find on a layout without fields showing for speed and manipulate that data as well as a result of the script then I have a lot of rework to do!
>>
>> Keith.
>>
>>
>>> On Jun 20, 2017, at 4:28 PM, Richard DeShong <[hidden email]> wrote:
>>>
>>> My previous post about using a script with a utility (no fields) layout has been my preferred method over creating a relationship to answer a question.
>>>
>>> Another (newer) method would be to use the SQL functionality to get the details of the record.  You could then build the question into a Custom Function to return your answer.
>>>
>>> Both methods do not need a special relationship which, if not needed for another purpose, can then be removed from your schema (keeping it simple).
>>>
>>>
>>> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>>>> What happens if you switch to a script?
>>>>
>>>> Script: IsValid
>>>>
>>>> $Code = <the promo code>
>>>> $When = <the redeem timestamp>
>>>> freeze window
>>>> goto layout <promo code util>
>>>> enter find mode
>>>> set field promocode to $Code
>>>> perform find
>>>> $IsValid = Case(
>>>> get( foundcount ) <> 1 ; False ;
>>>> $When < code_publish ; False ;
>>>> $When > code_expire ; False ;
>>>> True
>>>> )
>>>> goto layout <original>
>>>> Exit Script [$IsValid]
>>>>
>>>> Notes:
>>>> 1) The "promo code util" layout should have NO fields;
>>>> 2) The IsValid Case statement could use the positive tests first like:
>>>> get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>>>
>>>>
>>>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>>>> I have to monitor usage of promotional codes to ensure that they are not used more than the prescribed number of times (though some are unlimited) within two given timestamps (code_publish and code_expire).   It works perfectly but the relationship from the promotional_codes table to the promotional_codes_redeemed table seems slow; I am wondering if there is a better way to do it.  “Slow” is defined as when I check on a code I have to wait for multiple seconds for the record to display while a modal window tells me “Find in progress, Performing query”.
>>>>>
>>>>> I have the following relationship criteria;
>>>>> promotional_codes::promocode  =  promotional_codes_redeemed ::promocode
>>>>> promotional_codes::code_publish  <= promotional_codes_redeemed::timestamp_redeemed
>>>>> promotional_codes::code_expire  >= promotional_codes_redeemed::timestamp_redeemed
>>>>>
>>>>> All five relationship fields are indexed. promotional_codes_redeemed has 7 fields (also all indexed). 300,000 records in the promotional_codes table and 80,000 in the promotional_codes_redeemed table.
>>>>>
>>>>> Is there a smarter way to do this?
>>>>> Is this simply a 300,000 times 80,000 problem?  [24 billion…] If so, I suppose I need to cull the promotional codes that have been redeemed.
>>>>>
>>>>> Thanks for any other ideas.
>>>>>
>>>>> Keith.
>>>>> _______________________________________________
>>>>> FMPexperts mailing list
>>>>> [hidden email]
>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>>
>>>
>>> --
>>> Richard DeShong
>>> Logic Tools
>>> 510-642-5123 office
>>> 925-285-1088 cell
>>>
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> --
> Jonathan Fletcher
> [hidden email]
>
> Kentuckiana FileMaker Developers Group
> Next Meeting: 6/27/17
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

John Weinshel
No and probably.

Note that the Freeze Window script step is greyed out when you filter
steps by server, as windows don't exist on the server.  They are a GUI
object, and there is no GUI there.

Layouts do, though, as they provide required context. Does that mean they
also bear the same onus that burden client layouts-- joins on the layout
must be resolved when it's loaded? Given that 'Go to Portal Row' is not
greyed out, I would think so. That resolution may execute faster than on a
client, but a blank layout absolves the server of the need in the first
place.

Freeeze Window feels long in the tooth-- it was introduced when the server
itself was just finding its sea legs; I'd have expected anything that
needs hiding to have by now been shuttled to the server, or at least to
blank layouts in offscreen windows.

In general, it's rare* that the server won't resolve un-equal joins faster
than a client, even taking into account its need to pass back a tall stack
of id's. But test real life results.

I'm a fan of SQL in Filemaker, but un-equal joins will resolve more slowly
than native TO's. There are times when the tradeoff is useful-- LIKE works
slightly differently than basic Filemaker searches. It's slow, but in the
right situation the results may be worth the delay. In Keith's situation,
the only advantage to SQL would be removing a heavy TO from the graph.

John

*A heavily used, and/or under-powered server might have fewer resources
than a client. PSoS loses some steam on a WAN, more on a slow one. And
PSoS calls are metered




On 6/23/17, 4:51 AM, "Keith M. Rettig" <[hidden email]> wrote:

>Do the ³Freeze Window² and Richard¹s ³Utility Layouts² (with no fields)
>strategies matter if the database if the script is actually running on
>the server?
>I would think the the UI and layouts only slow things down for the
>scripts when the script is running locally.
>
>Keith.
>
>> On Jun 22, 2017, at 9:07 AM, Jonathan Fletcher <[hidden email]>
>>wrote:
>>
>> Another one not to miss that Richard had in his earlier script was the
>>Freeze Window step. Even if your target layout has a lot of fields on
>>it, this will save you much time.
>>
>> j.
>>
>>
>>> On Jun 22, 2017, at 11:56 AM, Joe Gilson <[hidden email]>
>>>wrote:
>>>
>>> Richard,
>>>
>>> Thank youŠjust learned how to implement a speed improvement.
>>>
>>> Joe
>>>
>>> On Jun 22, 2017, at 11:33 AM, Richard DeShong <[hidden email]>
>>>wrote:
>>>
>>> Hi Keith,
>>> You sent this directly to me (not the list), but I think that it is
>>>important for the list to know, so I am replying to the list.  I also
>>>see that you sent a second post where you mention more details about
>>>your particular system.
>>>
>>> In answer to your question about layouts, "will a scripted find work
>>>on a layout with no fields", the answer is "yes", if you are using Set
>>>Field to set the criteria.  Using Set Field, you can do everything with
>>>your tables using layouts with no fields.
>>>
>>> In my systems, I create at least one blank layout for each table.  I
>>>basically use these "utility" layouts for everything in my scripts.  So
>>>I end up with User Interface (UI) layouts and these "get the work done"
>>>utility layouts.  I use the utility layouts for creating, finding,
>>>updating, and deleting records.
>>>
>>> And to your other question, "... because it allows the script to run
>>>that much faster?", the answer is "yes".   Using this technique rivals
>>>PSOS for speed when you need to process a bunch of records.
>>>
>>> The reason I might have more than one for a given table is
>>>relationships.  If I am using the layout to answer a question about 1
>>>or more records, then I might also want to know about related records.
>>>So I need to make sure the layout is based on the Table Object that has
>>>those relationships.
>>>
>>> Hope this helps with your system.
>>> --
>>> Richard
>>>
>>> -----Original Message-----
>>> From: Keith M. Rettig [mailto:[hidden email]]
>>> Sent: Wednesday, June 21, 2017 6:00 PM
>>> To: Richard DeShong <[hidden email]>
>>> Subject: Re: relationship slowness
>>>
>>> You confused me a bit thereŠ
>>>
>>> In your previous post, the script says to go to utility layout and do
>>>a find.  But now you say there should not be any fields on the utility
>>>layout.  Will a scripted find work on a layout with no fields showing?
>>> Do you use this strategy because it allows the script to run that much
>>>faster?  When I first read your post I was assuming that you sent it to
>>>a utility layout as a place ³to go run a script that follows a bunch of
>>>logic but doesn¹t need to interact with any data until the end² so that
>>>the portion not needing data would run faster.  But if I can do a
>>>scripted find on a layout without fields showing for speed and
>>>manipulate that data as well as a result of the script then I have a
>>>lot of rework to do!
>>>
>>> Keith.
>>>
>>>
>>>> On Jun 20, 2017, at 4:28 PM, Richard DeShong <[hidden email]>
>>>>wrote:
>>>>
>>>> My previous post about using a script with a utility (no fields)
>>>>layout has been my preferred method over creating a relationship to
>>>>answer a question.
>>>>
>>>> Another (newer) method would be to use the SQL functionality to get
>>>>the details of the record.  You could then build the question into a
>>>>Custom Function to return your answer.
>>>>
>>>> Both methods do not need a special relationship which, if not needed
>>>>for another purpose, can then be removed from your schema (keeping it
>>>>simple).
>>>>
>>>>
>>>> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>>>>> What happens if you switch to a script?
>>>>>
>>>>> Script: IsValid
>>>>>
>>>>> $Code = <the promo code>
>>>>> $When = <the redeem timestamp>
>>>>> freeze window
>>>>> goto layout <promo code util>
>>>>> enter find mode
>>>>> set field promocode to $Code
>>>>> perform find
>>>>> $IsValid = Case(
>>>>> get( foundcount ) <> 1 ; False ;
>>>>> $When < code_publish ; False ;
>>>>> $When > code_expire ; False ;
>>>>> True
>>>>> )
>>>>> goto layout <original>
>>>>> Exit Script [$IsValid]
>>>>>
>>>>> Notes:
>>>>> 1) The "promo code util" layout should have NO fields;
>>>>> 2) The IsValid Case statement could use the positive tests first
>>>>>like:
>>>>> get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>>>>
>>>>>
>>>>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>>>>> I have to monitor usage of promotional codes to ensure that they
>>>>>>are not used more than the prescribed number of times (though some
>>>>>>are unlimited) within two given timestamps (code_publish and
>>>>>>code_expire).   It works perfectly but the relationship from the
>>>>>>promotional_codes table to the promotional_codes_redeemed table
>>>>>>seems slow; I am wondering if there is a better way to do it.
>>>>>>³Slow² is defined as when I check on a code I have to wait for
>>>>>>multiple seconds for the record to display while a modal window
>>>>>>tells me ³Find in progress, Performing query².
>>>>>>
>>>>>> I have the following relationship criteria;
>>>>>> promotional_codes::promocode  =  promotional_codes_redeemed
>>>>>>::promocode
>>>>>> promotional_codes::code_publish  <=
>>>>>>promotional_codes_redeemed::timestamp_redeemed
>>>>>> promotional_codes::code_expire  >=
>>>>>>promotional_codes_redeemed::timestamp_redeemed
>>>>>>
>>>>>> All five relationship fields are indexed.
>>>>>>promotional_codes_redeemed has 7 fields (also all indexed). 300,000
>>>>>>records in the promotional_codes table and 80,000 in the
>>>>>>promotional_codes_redeemed table.
>>>>>>
>>>>>> Is there a smarter way to do this?
>>>>>> Is this simply a 300,000 times 80,000 problem?  [24 billionŠ] If
>>>>>>so, I suppose I need to cull the promotional codes that have been
>>>>>>redeemed.
>>>>>>
>>>>>> Thanks for any other ideas.
>>>>>>
>>>>>> Keith.
>>>>>> _______________________________________________
>>>>>> FMPexperts mailing list
>>>>>> [hidden email]
>>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>>>
>>>>
>>>> --
>>>> Richard DeShong
>>>> Logic Tools
>>>> 510-642-5123 office
>>>> 925-285-1088 cell
>>>>
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>
>>>
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>
>>>
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>> --
>> Jonathan Fletcher
>> [hidden email]
>>
>> Kentuckiana FileMaker Developers Group
>> Next Meeting: 6/27/17
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
>_______________________________________________
>FMPexperts mailing list
>[hidden email]
>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

Richard DeShong
First, I do not use PSOS (do not like the idea).  I do have a job
processing client for maintenance jobs, sending emails, and doing any
tasks as a response to a user changing something where I need to check
and adjust a variety of things.

My "blank layout" method, as John mentioned, is primarily for speeding
up the client.  Using Freeze Window is about the same for speed
considerations.

As John mentioned, for PSOS and Server-Scheduled-Scripts, the server
creates a workstation in memory.  Since there is no GUI for this
workstation, there is no need to manage video memory (for displaying
layouts).  So the only purpose of "goto layout" is to select the proper
Table Object for operating context - that is, so it has access to the
appropriate relationships.

For me (and my style of systems), Blank layouts also provide "visual
privacy".  When you open a new window and go to a layout, what record is
displayed?  Of course, the next script step is to switch to Find Mode
and find the appropriate records, but I don't like that.  Also,
sometimes I need to find the record(s) before I can tell whether or not
the user should be allowed to "do this" with them.  So I use the blank
layout to perform the initial find.  If all is okay, then I switch to
the UI layout to display the data.


On 6/23/2017 9:54 AM, John Weinshel wrote:

> No and probably.
>
> Note that the Freeze Window script step is greyed out when you filter
> steps by server, as windows don't exist on the server.  They are a GUI
> object, and there is no GUI there.
>
> Layouts do, though, as they provide required context. Does that mean they
> also bear the same onus that burden client layouts-- joins on the layout
> must be resolved when it's loaded? Given that 'Go to Portal Row' is not
> greyed out, I would think so. That resolution may execute faster than on a
> client, but a blank layout absolves the server of the need in the first
> place.
>
> Freeeze Window feels long in the tooth-- it was introduced when the server
> itself was just finding its sea legs; I'd have expected anything that
> needs hiding to have by now been shuttled to the server, or at least to
> blank layouts in offscreen windows.
>
> In general, it's rare* that the server won't resolve un-equal joins faster
> than a client, even taking into account its need to pass back a tall stack
> of id's. But test real life results.
>
> I'm a fan of SQL in Filemaker, but un-equal joins will resolve more slowly
> than native TO's. There are times when the tradeoff is useful-- LIKE works
> slightly differently than basic Filemaker searches. It's slow, but in the
> right situation the results may be worth the delay. In Keith's situation,
> the only advantage to SQL would be removing a heavy TO from the graph.
>
> John
>
> *A heavily used, and/or under-powered server might have fewer resources
> than a client. PSoS loses some steam on a WAN, more on a slow one. And
> PSoS calls are metered
>
>
>
>
> On 6/23/17, 4:51 AM, "Keith M. Rettig" <[hidden email]> wrote:
>
>> Do the ³Freeze Window² and Richard¹s ³Utility Layouts² (with no fields)
>> strategies matter if the database if the script is actually running on
>> the server?
>> I would think the the UI and layouts only slow things down for the
>> scripts when the script is running locally.
>>
>> Keith.
>>
>>> On Jun 22, 2017, at 9:07 AM, Jonathan Fletcher <[hidden email]>
>>> wrote:
>>>
>>> Another one not to miss that Richard had in his earlier script was the
>>> Freeze Window step. Even if your target layout has a lot of fields on
>>> it, this will save you much time.
>>>
>>> j.
>>>
>>>
>>>> On Jun 22, 2017, at 11:56 AM, Joe Gilson <[hidden email]>
>>>> wrote:
>>>>
>>>> Richard,
>>>>
>>>> Thank youŠjust learned how to implement a speed improvement.
>>>>
>>>> Joe
>>>>
>>>> On Jun 22, 2017, at 11:33 AM, Richard DeShong <[hidden email]>
>>>> wrote:
>>>>
>>>> Hi Keith,
>>>> You sent this directly to me (not the list), but I think that it is
>>>> important for the list to know, so I am replying to the list.  I also
>>>> see that you sent a second post where you mention more details about
>>>> your particular system.
>>>>
>>>> In answer to your question about layouts, "will a scripted find work
>>>> on a layout with no fields", the answer is "yes", if you are using Set
>>>> Field to set the criteria.  Using Set Field, you can do everything with
>>>> your tables using layouts with no fields.
>>>>
>>>> In my systems, I create at least one blank layout for each table.  I
>>>> basically use these "utility" layouts for everything in my scripts.  So
>>>> I end up with User Interface (UI) layouts and these "get the work done"
>>>> utility layouts.  I use the utility layouts for creating, finding,
>>>> updating, and deleting records.
>>>>
>>>> And to your other question, "... because it allows the script to run
>>>> that much faster?", the answer is "yes".   Using this technique rivals
>>>> PSOS for speed when you need to process a bunch of records.
>>>>
>>>> The reason I might have more than one for a given table is
>>>> relationships.  If I am using the layout to answer a question about 1
>>>> or more records, then I might also want to know about related records.
>>>> So I need to make sure the layout is based on the Table Object that has
>>>> those relationships.
>>>>
>>>> Hope this helps with your system.
>>>> --
>>>> Richard
>>>>
>>>> -----Original Message-----
>>>> From: Keith M. Rettig [mailto:[hidden email]]
>>>> Sent: Wednesday, June 21, 2017 6:00 PM
>>>> To: Richard DeShong <[hidden email]>
>>>> Subject: Re: relationship slowness
>>>>
>>>> You confused me a bit thereŠ
>>>>
>>>> In your previous post, the script says to go to utility layout and do
>>>> a find.  But now you say there should not be any fields on the utility
>>>> layout.  Will a scripted find work on a layout with no fields showing?
>>>> Do you use this strategy because it allows the script to run that much
>>>> faster?  When I first read your post I was assuming that you sent it to
>>>> a utility layout as a place ³to go run a script that follows a bunch of
>>>> logic but doesn¹t need to interact with any data until the end² so that
>>>> the portion not needing data would run faster.  But if I can do a
>>>> scripted find on a layout without fields showing for speed and
>>>> manipulate that data as well as a result of the script then I have a
>>>> lot of rework to do!
>>>>
>>>> Keith.
>>>>
>>>>
>>>>> On Jun 20, 2017, at 4:28 PM, Richard DeShong <[hidden email]>
>>>>> wrote:
>>>>>
>>>>> My previous post about using a script with a utility (no fields)
>>>>> layout has been my preferred method over creating a relationship to
>>>>> answer a question.
>>>>>
>>>>> Another (newer) method would be to use the SQL functionality to get
>>>>> the details of the record.  You could then build the question into a
>>>>> Custom Function to return your answer.
>>>>>
>>>>> Both methods do not need a special relationship which, if not needed
>>>>> for another purpose, can then be removed from your schema (keeping it
>>>>> simple).
>>>>>
>>>>>
>>>>> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>>>>>> What happens if you switch to a script?
>>>>>>
>>>>>> Script: IsValid
>>>>>>
>>>>>> $Code = <the promo code>
>>>>>> $When = <the redeem timestamp>
>>>>>> freeze window
>>>>>> goto layout <promo code util>
>>>>>> enter find mode
>>>>>> set field promocode to $Code
>>>>>> perform find
>>>>>> $IsValid = Case(
>>>>>> get( foundcount ) <> 1 ; False ;
>>>>>> $When < code_publish ; False ;
>>>>>> $When > code_expire ; False ;
>>>>>> True
>>>>>> )
>>>>>> goto layout <original>
>>>>>> Exit Script [$IsValid]
>>>>>>
>>>>>> Notes:
>>>>>> 1) The "promo code util" layout should have NO fields;
>>>>>> 2) The IsValid Case statement could use the positive tests first
>>>>>> like:
>>>>>> get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>>>>>
>>>>>>
>>>>>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>>>>>> I have to monitor usage of promotional codes to ensure that they
>>>>>>> are not used more than the prescribed number of times (though some
>>>>>>> are unlimited) within two given timestamps (code_publish and
>>>>>>> code_expire).   It works perfectly but the relationship from the
>>>>>>> promotional_codes table to the promotional_codes_redeemed table
>>>>>>> seems slow; I am wondering if there is a better way to do it.
>>>>>>> ³Slow² is defined as when I check on a code I have to wait for
>>>>>>> multiple seconds for the record to display while a modal window
>>>>>>> tells me ³Find in progress, Performing query².
>>>>>>>
>>>>>>> I have the following relationship criteria;
>>>>>>> promotional_codes::promocode  =  promotional_codes_redeemed
>>>>>>> ::promocode
>>>>>>> promotional_codes::code_publish  <=
>>>>>>> promotional_codes_redeemed::timestamp_redeemed
>>>>>>> promotional_codes::code_expire  >=
>>>>>>> promotional_codes_redeemed::timestamp_redeemed
>>>>>>>
>>>>>>> All five relationship fields are indexed.
>>>>>>> promotional_codes_redeemed has 7 fields (also all indexed). 300,000
>>>>>>> records in the promotional_codes table and 80,000 in the
>>>>>>> promotional_codes_redeemed table.
>>>>>>>
>>>>>>> Is there a smarter way to do this?
>>>>>>> Is this simply a 300,000 times 80,000 problem?  [24 billionŠ] If
>>>>>>> so, I suppose I need to cull the promotional codes that have been
>>>>>>> redeemed.
>>>>>>>
>>>>>>> Thanks for any other ideas.
>>>>>>>
>>>>>>> Keith.
>>>>>>> _______________________________________________
>>>>>>> FMPexperts mailing list
>>>>>>> [hidden email]
>>>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>>> --
>>>>> Richard DeShong
>>>>> Logic Tools
>>>>> 510-642-5123 office
>>>>> 925-285-1088 cell
>>>>>
>>>>> _______________________________________________
>>>>> FMPexperts mailing list
>>>>> [hidden email]
>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>>
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>>
>>>>
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>> --
>>> Jonathan Fletcher
>>> [hidden email]
>>>
>>> Kentuckiana FileMaker Developers Group
>>> Next Meeting: 6/27/17
>>>
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Richard DeShong
Logic Tools
510-642-5123 office
925-285-1088 cell

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: relationship slowness

John Weinshel
Richard, you are begging the question; I am curious, so I will ask it:
what do you not like about PSoS?

John

On 6/23/17, 12:20 PM, "Richard DeShong" <[hidden email]> wrote:

>First, I do not use PSOS (do not like the idea).  I do have a job
>processing client for maintenance jobs, sending emails, and doing any
>tasks as a response to a user changing something where I need to check
>and adjust a variety of things.
>
>My "blank layout" method, as John mentioned, is primarily for speeding
>up the client.  Using Freeze Window is about the same for speed
>considerations.
>
>As John mentioned, for PSOS and Server-Scheduled-Scripts, the server
>creates a workstation in memory.  Since there is no GUI for this
>workstation, there is no need to manage video memory (for displaying
>layouts).  So the only purpose of "goto layout" is to select the proper
>Table Object for operating context - that is, so it has access to the
>appropriate relationships.
>
>For me (and my style of systems), Blank layouts also provide "visual
>privacy".  When you open a new window and go to a layout, what record is
>displayed?  Of course, the next script step is to switch to Find Mode
>and find the appropriate records, but I don't like that.  Also,
>sometimes I need to find the record(s) before I can tell whether or not
>the user should be allowed to "do this" with them.  So I use the blank
>layout to perform the initial find.  If all is okay, then I switch to
>the UI layout to display the data.
>
>
>On 6/23/2017 9:54 AM, John Weinshel wrote:
>> No and probably.
>>
>> Note that the Freeze Window script step is greyed out when you filter
>> steps by server, as windows don't exist on the server.  They are a GUI
>> object, and there is no GUI there.
>>
>> Layouts do, though, as they provide required context. Does that mean
>>they
>> also bear the same onus that burden client layouts-- joins on the layout
>> must be resolved when it's loaded? Given that 'Go to Portal Row' is not
>> greyed out, I would think so. That resolution may execute faster than
>>on a
>> client, but a blank layout absolves the server of the need in the first
>> place.
>>
>> Freeeze Window feels long in the tooth-- it was introduced when the
>>server
>> itself was just finding its sea legs; I'd have expected anything that
>> needs hiding to have by now been shuttled to the server, or at least to
>> blank layouts in offscreen windows.
>>
>> In general, it's rare* that the server won't resolve un-equal joins
>>faster
>> than a client, even taking into account its need to pass back a tall
>>stack
>> of id's. But test real life results.
>>
>> I'm a fan of SQL in Filemaker, but un-equal joins will resolve more
>>slowly
>> than native TO's. There are times when the tradeoff is useful-- LIKE
>>works
>> slightly differently than basic Filemaker searches. It's slow, but in
>>the
>> right situation the results may be worth the delay. In Keith's
>>situation,
>> the only advantage to SQL would be removing a heavy TO from the graph.
>>
>> John
>>
>> *A heavily used, and/or under-powered server might have fewer resources
>> than a client. PSoS loses some steam on a WAN, more on a slow one. And
>> PSoS calls are metered
>>
>>
>>
>>
>> On 6/23/17, 4:51 AM, "Keith M. Rettig" <[hidden email]> wrote:
>>
>>> Do the ³Freeze Window² and Richard¹s ³Utility Layouts² (with no fields)
>>> strategies matter if the database if the script is actually running on
>>> the server?
>>> I would think the the UI and layouts only slow things down for the
>>> scripts when the script is running locally.
>>>
>>> Keith.
>>>
>>>> On Jun 22, 2017, at 9:07 AM, Jonathan Fletcher
>>>><[hidden email]>
>>>> wrote:
>>>>
>>>> Another one not to miss that Richard had in his earlier script was the
>>>> Freeze Window step. Even if your target layout has a lot of fields on
>>>> it, this will save you much time.
>>>>
>>>> j.
>>>>
>>>>
>>>>> On Jun 22, 2017, at 11:56 AM, Joe Gilson <[hidden email]>
>>>>> wrote:
>>>>>
>>>>> Richard,
>>>>>
>>>>> Thank youŠjust learned how to implement a speed improvement.
>>>>>
>>>>> Joe
>>>>>
>>>>> On Jun 22, 2017, at 11:33 AM, Richard DeShong
>>>>><[hidden email]>
>>>>> wrote:
>>>>>
>>>>> Hi Keith,
>>>>> You sent this directly to me (not the list), but I think that it is
>>>>> important for the list to know, so I am replying to the list.  I also
>>>>> see that you sent a second post where you mention more details about
>>>>> your particular system.
>>>>>
>>>>> In answer to your question about layouts, "will a scripted find work
>>>>> on a layout with no fields", the answer is "yes", if you are using
>>>>>Set
>>>>> Field to set the criteria.  Using Set Field, you can do everything
>>>>>with
>>>>> your tables using layouts with no fields.
>>>>>
>>>>> In my systems, I create at least one blank layout for each table.  I
>>>>> basically use these "utility" layouts for everything in my scripts.
>>>>>So
>>>>> I end up with User Interface (UI) layouts and these "get the work
>>>>>done"
>>>>> utility layouts.  I use the utility layouts for creating, finding,
>>>>> updating, and deleting records.
>>>>>
>>>>> And to your other question, "... because it allows the script to run
>>>>> that much faster?", the answer is "yes".   Using this technique
>>>>>rivals
>>>>> PSOS for speed when you need to process a bunch of records.
>>>>>
>>>>> The reason I might have more than one for a given table is
>>>>> relationships.  If I am using the layout to answer a question about 1
>>>>> or more records, then I might also want to know about related
>>>>>records.
>>>>> So I need to make sure the layout is based on the Table Object that
>>>>>has
>>>>> those relationships.
>>>>>
>>>>> Hope this helps with your system.
>>>>> --
>>>>> Richard
>>>>>
>>>>> -----Original Message-----
>>>>> From: Keith M. Rettig [mailto:[hidden email]]
>>>>> Sent: Wednesday, June 21, 2017 6:00 PM
>>>>> To: Richard DeShong <[hidden email]>
>>>>> Subject: Re: relationship slowness
>>>>>
>>>>> You confused me a bit thereŠ
>>>>>
>>>>> In your previous post, the script says to go to utility layout and do
>>>>> a find.  But now you say there should not be any fields on the
>>>>>utility
>>>>> layout.  Will a scripted find work on a layout with no fields
>>>>>showing?
>>>>> Do you use this strategy because it allows the script to run that
>>>>>much
>>>>> faster?  When I first read your post I was assuming that you sent it
>>>>>to
>>>>> a utility layout as a place ³to go run a script that follows a bunch
>>>>>of
>>>>> logic but doesn¹t need to interact with any data until the end² so
>>>>>that
>>>>> the portion not needing data would run faster.  But if I can do a
>>>>> scripted find on a layout without fields showing for speed and
>>>>> manipulate that data as well as a result of the script then I have a
>>>>> lot of rework to do!
>>>>>
>>>>> Keith.
>>>>>
>>>>>
>>>>>> On Jun 20, 2017, at 4:28 PM, Richard DeShong
>>>>>><[hidden email]>
>>>>>> wrote:
>>>>>>
>>>>>> My previous post about using a script with a utility (no fields)
>>>>>> layout has been my preferred method over creating a relationship to
>>>>>> answer a question.
>>>>>>
>>>>>> Another (newer) method would be to use the SQL functionality to get
>>>>>> the details of the record.  You could then build the question into a
>>>>>> Custom Function to return your answer.
>>>>>>
>>>>>> Both methods do not need a special relationship which, if not needed
>>>>>> for another purpose, can then be removed from your schema (keeping
>>>>>>it
>>>>>> simple).
>>>>>>
>>>>>>
>>>>>> On 6/20/2017 4:12 PM, Richard DeShong wrote:
>>>>>>> What happens if you switch to a script?
>>>>>>>
>>>>>>> Script: IsValid
>>>>>>>
>>>>>>> $Code = <the promo code>
>>>>>>> $When = <the redeem timestamp>
>>>>>>> freeze window
>>>>>>> goto layout <promo code util>
>>>>>>> enter find mode
>>>>>>> set field promocode to $Code
>>>>>>> perform find
>>>>>>> $IsValid = Case(
>>>>>>> get( foundcount ) <> 1 ; False ;
>>>>>>> $When < code_publish ; False ;
>>>>>>> $When > code_expire ; False ;
>>>>>>> True
>>>>>>> )
>>>>>>> goto layout <original>
>>>>>>> Exit Script [$IsValid]
>>>>>>>
>>>>>>> Notes:
>>>>>>> 1) The "promo code util" layout should have NO fields;
>>>>>>> 2) The IsValid Case statement could use the positive tests first
>>>>>>> like:
>>>>>>> get(foundcount) = 1  and  $When >= publish  and $When <= expire
>>>>>>>
>>>>>>>
>>>>>>> On 6/20/2017 2:00 PM, Keith M. Rettig wrote:
>>>>>>>> I have to monitor usage of promotional codes to ensure that they
>>>>>>>> are not used more than the prescribed number of times (though some
>>>>>>>> are unlimited) within two given timestamps (code_publish and
>>>>>>>> code_expire).   It works perfectly but the relationship from the
>>>>>>>> promotional_codes table to the promotional_codes_redeemed table
>>>>>>>> seems slow; I am wondering if there is a better way to do it.
>>>>>>>> ³Slow² is defined as when I check on a code I have to wait for
>>>>>>>> multiple seconds for the record to display while a modal window
>>>>>>>> tells me ³Find in progress, Performing query².
>>>>>>>>
>>>>>>>> I have the following relationship criteria;
>>>>>>>> promotional_codes::promocode  =  promotional_codes_redeemed
>>>>>>>> ::promocode
>>>>>>>> promotional_codes::code_publish  <=
>>>>>>>> promotional_codes_redeemed::timestamp_redeemed
>>>>>>>> promotional_codes::code_expire  >=
>>>>>>>> promotional_codes_redeemed::timestamp_redeemed
>>>>>>>>
>>>>>>>> All five relationship fields are indexed.
>>>>>>>> promotional_codes_redeemed has 7 fields (also all indexed).
>>>>>>>>300,000
>>>>>>>> records in the promotional_codes table and 80,000 in the
>>>>>>>> promotional_codes_redeemed table.
>>>>>>>>
>>>>>>>> Is there a smarter way to do this?
>>>>>>>> Is this simply a 300,000 times 80,000 problem?  [24 billionŠ] If
>>>>>>>> so, I suppose I need to cull the promotional codes that have been
>>>>>>>> redeemed.
>>>>>>>>
>>>>>>>> Thanks for any other ideas.
>>>>>>>>
>>>>>>>> Keith.
>>>>>>>> _______________________________________________
>>>>>>>> FMPexperts mailing list
>>>>>>>> [hidden email]
>>>>>>>>
>>>>>>>>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.a
>>>>>>>>u
>>>>>> --
>>>>>> Richard DeShong
>>>>>> Logic Tools
>>>>>> 510-642-5123 office
>>>>>> 925-285-1088 cell
>>>>>>
>>>>>> _______________________________________________
>>>>>> FMPexperts mailing list
>>>>>> [hidden email]
>>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>>>
>>>>> _______________________________________________
>>>>> FMPexperts mailing list
>>>>> [hidden email]
>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> FMPexperts mailing list
>>>>> [hidden email]
>>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>> --
>>>> Jonathan Fletcher
>>>> [hidden email]
>>>>
>>>> Kentuckiana FileMaker Developers Group
>>>> Next Meeting: 6/27/17
>>>>
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
>--
>Richard DeShong
>Logic Tools
>510-642-5123 office
>925-285-1088 cell
>
>_______________________________________________
>FMPexperts mailing list
>[hidden email]
>http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au


_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
12