ms-access Calculated field performance

1
votes

My table contains various "Date" informations like "OrderDate", "ProductionDate", "ControlDate", "SellDate". In the query, I want to have a field "Status" which says something like "Orderd", "Produced", "Controlled", "Sold". I can derive the status information from the various dates (e.g. if there's a ProductionDate but no ControlDate set, Status is "Produced"). The number of date fields and stati are guaranteed to never change. The date changes are very infrequent, whereas the queries occur all the time. The query contains not just one such derived field but about five.

How big is the performance hit for calculating Status with a VBA routine in the query, compared to creating an extra Status field in the table which is set every time a date changes?

Or is this design basically flawed?

1
databasems-accesscalculated-field
Good question. A lot depends on how the data is updated, and how many records would be queried at any given time. I have done this both ways, and it really does depend on many factors. A function derived status would be esoterically better as it's guaranteed to be correct based on the live data. But if you have 100000's records this would start to get slow to work. If however you can restrict your dataset to ignore Sold items for instance so you are only looking at 'active' records then it would be the better way. - Minty
You should refrain from using calculated fields. Queries are for this. - Gustav
Thanks for your comments, I'm happy that I'm not totally off, but seem to have a real problem. I guess I'll start looking into how to profile an Access database. - Batox

1 Answers

2
votes

As far as performance hit, you're gonna see a lot smaller hit if you update a single record in a table than if you try to create that field "on the fly" in a query. The question is; how much data are we talking about and how long is it taking to spit out the report? I'd go 50/50 on this; I don't think either method is wrong or worse but if you're looking to save as much time as possible then I'd add a field to the table and update that field whenever one of the dates is input/updated. More coding on the back end, but it'll take milliseconds to run the updates versus deciseconds (or possibly seconds) to create the field in a query.