Performance

[rogermax]

Has anyone done testing to check performance difference of running a script that would loop through all records and use the Set Field command as opposed to import records?

I was thinking about this while doing an update for a client and waiting a long time for the imports to complete. May be a silly question on the surface, but I figured I would take the chance...

[DanBrill]

Hi Roger,

I haven't done a head to head comparison, but having worked with each, I'd put my money on the import technique. Just to see how it would perform, I put FM to the test. I had a database of the Bible, where every verse was a record. This is 31,000 some records. I imported it from file A to file B 7 times in about 14 seconds. There's no way you could loop through and use set field nearly a quarter-million times in a quarter minute.

[-Queue-]

Hmm, very interesting. Thank you, Dan.

By the way, would you consider what you did 'testing the Word'?

[rogermax]

Basically what I thought. It sure is a drag updating a solution and having to import all the records. I keep looking for a better way. I can tell you it was a lot longer than 14 seconds.

[kenneth2k1]

I would hypothesize that the both the import or the loop time would depend on the number of fields being set. For example, assuming one field and a match field are being imported, it might be faster than looping and setting fields. However, I have seen imports take longer than my looping scripts. But my looping scripts perform a find to narrow the amount of records being looped.

[rogermax]

I often use loops and sets instead of imports for limited found sets. I was curious in relation to a whole solution upgrade. The file count is 16 and the most complex file has 250 fields, although not all of them import. Some minor files have 100k+ records but very few fields and they run the import pretty fast. Then one that has 45k records takes a looooong time. making a loop script for 250 fields could be ugly

on another note--

In my spare time I am doing a complete rewrite to make it into a single presentation file and data/processing files. It has been quite a labor and the further I get, the more I wonder if I am crazy. I am finding some issues in structuring the relationships for filtered value lists and so on.

[DanBrill]

The import time will also depend on how many unstored calc fields have to calculate as a result of the new data.

[BobWeaver]

Dan, I think you meant to say the *stored* calc fields that will slow down the import. The unstored ones will only calculate when displayed.

[AudioFreak]

BobWeaver said:

Dan, I think you meant to say the *stored* calc fields that will slow down the import. The unstored ones will only calculate when displayed.

Would that be dependent on the layout you were on when you performed the import?

Thanks, Michael

[BobWeaver]

No, because stored calculation fields must have the values calculated and stored in them when the record is imported and saved. Unstored calculation fields and summary fields could cause a delay after the import is complete if these fields are on the current layout. So it's a good idea to go to a blank layout before starting the import.

[DanBrill]

Um, yeah. Forgive me. It's Suday. My brain is taking a day of rest.

[rogermax]

Bob, That makes sense. As I look over the solution there are a lot of stored calcs. In my rewrite I am looking at whether or not I can remove many of them. Thanks all.

Which begs another question. Where would the performance hit be using unstored calcs?

[BobWeaver]

With unstored calcs, the perfomance hit comes when you display them, because that's when they calculate. If you include them in a list view layout, that can slow things down epecially scrolling the list etc. Also things like sorting searching. And, of course, you can't use unstored calc fields on the left side of a relationship.