Test or simply set?

[LaRetta]

I haven't ran tests; I probably should. But I'm working on a looping script which is quite complex in setting fields in different relationships, or (sometimes) creating a new record if it doesn't exist. I wonder which is more efficient (using this simple piece of the script):

If [ [color:red]relatedTable::Type :notequal: "CHS" ]

Set Field [ relatedTable::Type ; "CHS" ]

vs.

Set Field [ relatedTable::Type ; "CHS" ]

I mean ... if the field already says CHS, it can be skipped so setting the field would have been unnecessary but the test itself takes time. And if the record doesn't exist, the relationship (with Allow Creation) will create it when the field is set; otherwise it'll just set the field. I confess to laziness and have sometimes NOT tested and just set the field(s) again. I have not tested speed on evaluating a condition vs. setting a related field. Does anyone have perceptions on the issue? When should I test before setting and when should I just set the darned fields? It FEELS like I should only test if it might make a difference in the Set Field[] but even that isn't necessary, ie, the test could be within the Set Field itself[].

I should explain that this is handled in an intermediate table where SQL connects. It is a synchronizing process.

UPDATE: Added the part in red.

LaRetta

Edited January 1, 2008 by Guest
Added update

[LaRetta]

Oh, I realize that, if there are many Set Fields[] which depends upon ONE test, such as if the record meets certain criteria, then test is necessary. I hope everyone realizes the difference ... it is when I ALWAYS want to change/create or DO something that I question why I should test first. :smirk:

[LaRetta]

Okay, I got off my bum and tested it. My volume of records might be too small for significant test. Can I get input on my testing approach?

Tables:

Parent has 10,000 records

Child has 9,500 records

These tables are supposed to be mirrors 1:1 on the ParentID. The field values are pulled in through SQL. 500 child records will need to be created. There are 2,000 child records which already contain the correct value text1 = "UPD", the others will need to be changed. Results? 12 seconds evaluating each record first; 11 seconds with no evaluation but setting fields which might already have the correct result. Attached is the test file. Ideas on how better to approach this test process would be appreciated because, as it stands, I'm going to skip testing in most instances; it doesn't seem efficient.

SpeedTest.zip

Edited January 1, 2008 by Guest
Removed fields from parent table - it was confusing

[LaRetta]

Okay, I changed the number of child records with UPD to exactly half the records and re-ran the test. I wanted an equal test (as best I could figure). Now they BOTH say 10 seconds.

So it's back to the whiteboard, I think.

UPON RETHINK ... this is ridiculous ... the If test must run no matter WHAT the value in the field. The number of child records with UPD should have made NO difference whatsoever.

Edited January 1, 2008 by Guest

[comment]

I think you need to level the ground before each test. I am curious what results you get with the attached.

SpeedTest3.fp7.zip

[comment]

In view of your last edit, you should probably test with different amounts of records to set (in Reset script: Omit Multiple), say 5, 2000, 5000, 8000 and 9995.

[LaRetta]

Hi Michael,

Your method is certainly faster:

5 records = 8 seconds

2,000 = 6 seconds

5,000 = 6 seconds

8,000 = 5 seconds

9,995 = 5 seconds

Your method offers the best of both worlds, 1) allows use of GTRR and Replace Field Contents[] for the majority of records which needs to be changed but 2) still allows creation of new records through the relationship. In re-thinking this process, I'll be rewriting much of it anyway. But I will use this idea when I do. Thank you! :wink2:

[comment]

I'm not saying it's necessarily the fastest method possible. I was just curious if the expense of find would pay for itself. On second thought, you should also consider indexing, or lack thereof - it might affect the results.

[LaRetta]

In my current method, I stay put. I start with an import of only keys into Router. These are all records which have changed or are new. I loop through them, updating or creating through the relationship. There would be no way of finding because I wouldn't know what children to find - GTRR is only method (or stay where I am and use relationship). And because they are imported, they aren't indexed.

My biggest concern with this method is that, in my particular case, after import I'll have duplicate SQL keys. A self-join tells me and they are deleted during my loop (before I write). If I GTRR to children and then GTRR back then I'll have more parents than I started with.

I still am pleased you showed me this, Michael. I tend to be too rigid (plant my butt and never move or move too much etc) and this shows that balance is important. As I've heard before - proportion is everything in life but it must be taken in proportion. :thankyou: