Peter Kopp · May 4, 2016

Advantages and Dis-advantages of SqlComputeCode.

Good afternoon, I have working prototypes of each of these approaches. I do not have an expansive cache background.

I have a couple of projects where I am ingesting files from disk.

The name of the file contains a lot of the information I will need to reference the file in the future.

What are the advantages and dis-advantages of these two approaches:

Store the whole filename and use SqlComputeCode to populate properties in a class?

Parse the data out of the filename on ingestion into properties in a class?

For each approach what are the implications to indexing?

Thank you.


0 832
Discussion (2)1
Log in or sign up to continue

I would store the original filename in a property anyway. SQLComputed properties (as long as they are not Calculated or Transient) are calculated when the record is inserted or updated (you can further restrict re-calculation with the SQLComputeOnChange keyword) and stored with the record as any other properties.

That makes also possible to maintain indices based on SQLComputed properties, with two conditions:

  • The SQLComputeCode must be deterministic - this is quite obvious, I think.
  • If you change the SQLComputeCode, that won't affect the records stored in your database, nor the corresponding indices - so you'll have to trigger a re-calculation to apply the new compute code.

See also our online documentation on the topic.

My answer was not complete in this form: the same effect can be achieved with SQL Triggers, too. That version is more SQL compliant maybe. And the "computed" properties can be set in one of our callback methods (%OnBeforeSave - for example), if you prefer the object interface - this is not called when you update your records from SQL though.  

Which implementation is the best? Besides the SQL/Object preferences, I think that's rather a matter of taste. You can index your properties in all three cases, and you'll face mostly the same (or similiar) problems I mentioned above.

My personal vote would go to SQLComputeCode.