Using Calculated Fields feature in Access2010

[jonathanchye]

I wonder what are everyone's thoughts about using the "Calculated Fields" feature available in Access2010? This will greatly simplify things but I wonder how would it impact performance and perhaps future upgrades to SQL-based solution? (MS SQL Server)

 

[The_Doc_Man]

It is perhaps possible but that doesn't mean it is a great idea. You have put your finger on a potential problem - the ability to split the database to an active back-end server.

You can do your calculation in a query very well, and I see no reason to get sloppy with calculations done in some "feature" that won't up-convert very well. Besides that, one of the guidelines for stabilizing a database with a front-end, back-end split configuration is that you should use queries for everything anyway.

If there is a complex table-connect string associated with a table that is bound to a form, you can use the link manager and queries to hide that completely. If you did a direct connection to a table, particularly if there was a password on the back-end, you would be asked every time you opened the back-end database. Build single-table queries to every table, then use THEM underneath your forms and reports. Access will silently manage the connection for you if you set up the connect string correctly.

 

[Banana]

IMNSHO, there's no point in using a calculated field for Access 2010 database if it's not going to be used in web. This feature was given as addressing issues associated with deploying a web database.

Frankly, the whole thing is questionable because for starter, you can't index it. At least in SQL Server, you can choose to index the calculated values and thus make a case for improving performance by allowing you to filter based on the calculated values. Even worse, this calculated field in Access is persisted. This means you're just eating up extra bytes of storage when it could have been calculated on the fly. Traditionally, disk I/O has been the biggest bottleneck so when your CPU is twiddling its thumb for the spinning platter to collect all those bytes, you would be well off to have your CPU go and calculate the values and thus require less bytes to be read off the disk and thus see better performance.

Now, this completely changes when you do use a web database. For one thing, it'd be deployed on SharePoint, which is most likely to be installed on multiple servers and thus have access to fancy disk subsystem - RAID, fibre channel cards, SANs, whatever the latest new technology those boys come up with. And then there's crapload of caching going on, much more than I care to know about. In this context, storing values become advantageous in comparison.

So, for a traditional database, I argue you don't want to use calculated fields. Until one decides to allow indexing on the calculated fields or at least the option to *not* persist the calculated fields, I'd just stick to the true'n'tried method of doing all calculations in the queries.