Jump to content
Sign in to follow this  
rudon

performance issues

Recommended Posts

I have a single table but many different layouts. If data is imported into the table using layout A, the value of Field 1 needs to be calculated differently from when layout B is being used, and so on. The layouts handle different input formats for data that need to be added to the table from different source files. The system works well when I use complicated Case, If, Choose clauses handling the whole series of layouts (about 12) in the autoenter calcalution for Field 1. Since this is repeated for many different fields the database however becomes difficult to maintain when there are changes in the formats of the sourcefiles.

An alternative would be to set a table up with text versions of the required calculations as a grid with a record for each layout (A, B ... L) and fields for the required calculations for Fields 1 - N. The autoenter calculations for the fields in the table could then be set as Evaluate() functions.

My question before I do this: is there a large performance reduction using this alternative?

Or any other suggestion for an alternative solution?

Share this post


Link to post
Share on other sites

I am not sure I understood this completely, but it sounds like you should have a temp table for importing raw data, and a global field to indicate the type of import source.

You could have a calculation field in the temp table - using Case() and referencing the global field - to 'normalize' the imported data to some common standard. Then make your script import the data again, from the temp table (sourcing the calculation field) into the real table.

Share this post


Link to post
Share on other sites

The easy answer to create a custom function and build your rules into that. Then you only need to place that custom function in place of your complicated clauses.

Whenever a change needs to be made to the rules, you change it in one place: the custom function.

Share this post


Link to post
Share on other sites

The idea of a temp table is probably a good way simplifying the problem using scripts with parameters to select a different temp table depending on the layout. Yes this looks like a clean solution to me. The speed hit of reading the data twice is probably not important. This is certainly a better solution than my orginal one with complicated case statements in autoenter calculations of all fields of the final table. Thanks!

The advantage of my solution with Evaluate() would be that the format changes in the source data do not require opening up the data base itself but need only user editing of the grid table.

In fact the choice of going through the Evaluate() function could still be valid alternative which makes me repeat my question if anyone has an idea concerning the performance hit of using Evaluate().

Share this post


Link to post
Share on other sites

This would would not really reduce complexity: the custom function would need to have the same complicated clauses since they are field dependent. If the "recoding" of the source data would be similar in many fields your solution would do the trick, but the data are not that friendly...

Share this post


Link to post
Share on other sites

I think you need only one temp table.

The advantage of my solution with Evaluate() would be that the format changes in the source data do not require opening up the data base itself but need only user editing of the grid table.

You could still do this, by having another table of Formats, where the formula of each format would be stored, and users can modify/add formats. Then use a relationship to pick the correct format record for each import.

Evaluate() does slow things up, since it has to go thru the calc engine twice. It's difficult to judge the impact in abstract. IMHO, if you only do this once, when the data is imported, it shouldn't matter much.

Share this post


Link to post
Share on other sites

This would would not really reduce complexity: the custom function would need to have the same complicated clauses since they are field dependent. If the "recoding" of the source data would be similar in many fields your solution would do the trick, but the data are not that friendly...

So each field / layout combo has it's own rules? 12x20 combos or so? That IS a complicated statement.

A user-editable table does seem the best way to go.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.