I have a complex formula that I would like to use in a view column. I can put the formula in the view column itself, but I’ve always thought that it’s a better idea to compute the value in the form when the document is saved, and then display that field’s value in the column.
Is that correct? Or is the difference minimal.
A little more information:
The database is for project tracking. Some times there are several phases active. In a column I would like to use several @If and @Trim statements to concatonate all of the pertinant information about the tasks and their due dates.
If I add a new field to the form to do this computation, I will need to refresh all of the documents in the database to compute the value (there are many). Once that was done, however, I can simply display the value of the field in the column.
Conversely, I could put the code in the view’s column formula. My original gut feeling was that this would be inefficient since the values would have to be generated each time the view index was updated, but now I’m wondering if view indexing may be more efficient than I think. For instance, view indexing may only happen when a document is changed. Therefore, in both cases the computation would only happen when the document changed.
Your thoughts?
TIA.
Subject: Better to compute in form or view column formula?
It really depends on the number of projects that you will have in your view. It also sounds like you only really need to complete the calculations for open projects - completed ones can be farmed off to a different view.
If you are going to just do the calculations for open projects, and you will have less than 100 open projects, it really doesn’t matter. The speed difference will be slight. If you’re looking at 10,000 open projects, then you definitely want to do the calculation in the form.
Subject: Great responses. Thank you.
.
Subject: Better to compute in form or view column formula?
Computing the values in the documents will allways be more efficient in terms of performance, because no calaculation has te be made when you reindex the view.
Once the index has been made, the difference in speed will be small if there are not to many document changes, because - as you mentioned - only changed documents have to be ‘replaced’ in the index.
If there are few documents in the view (say < 1000) it won’t matter a lot. If there are many documents (>10,000) then things could slow down when you’re computing everything in the column formula’s and there are many document changes. You must also keep in mind that in case of view corruption, you’ll have to rebuild the view’s index from scratch. If this is the case and you have for example 200,000 documents in the view with very complex column formulas, it could take quite some time before your database is accessable again.
I would go for the computed fields in the documents.
One important notice : if you’re using time dependant functions (like @now), you must use calculated fields that are set for example by an overnight agent, or otherwise your server will get on it’s knees!
Regards,
Joris