OrderBy a Calculated Field. Wise or Foolish?

[MatthewGrace]

My goal is to determine under what conditions, if any, is it wise to OrderBy a calculated field. Below is a task I'm faced with that lead to me pondering this.



I have an "Orders" Table which has an OrderStatus field (acceptable values are: 'Pending', 'In Production', 'Ready', 'Shipped'). Part of me wants to make that field a "Short Text" field, with a Field Size of a dozen or so characters to store the values, and move on with life. But then I got around to thinking: This table also has a series of timestamp fields (date/time) for each stage of manual labor involved in Order creation - so I could just make OrderStatus a Calculated field like so...

IIf(Not Is Null([DateShipped]), "Shipped", IIf(Not IsNull([DateFinished]), "Finished", IIf(.........))))))))

As you can see, all this is doing is detecting the presence of a value in timestamp fields within the record, and deciding the appropriate string via IIf() nesting.

But what about in a table with a few thousand records? If I wanted to OrderBy that calculated field, wouldn't Access then have to work MUCH harder to precalculate all those values for each record before it could even begin the sort, thus making it crazy slow?!

Thank you for any suggestions.

[CJ_London]

since calculated fields cannot be indexed - any sort or use as criteria will be slow because the system will need to do a sequential search through the table. Whether it actually stores the value so it doesn't have to calculate each time the record is accessed, I'm not sure - I seem to recall reading somewhere it is calculated at the time the component values are changed (e.g. in an update query) - so my guess it is stored, just not indexed. However I would think the effect on a few thousand records would be minimal - probably not really noticeable until you reach 100,000+ records.

[MatthewGrace]

Hm. I'd like closure if anyone knows whether Calculated Fields are stored or not. I'm guessing not - that they're calculated on the fly every time you view the Table. As a test I made a new table with a Calculated Field, entered 10 or so bogus records, closed the table, and finally went back into Design View for that Table and changed out the Calculation entirely! If the data is indeed stored, viewing the Table contents should yield outdated stored information... unless changing the Calculation triggers Access to recalculate/store the data all over again. This is not the impression Allen Browne gave me in last couple sentences of this page: http://allenbrowne.com/casu-14.html

Final thought: If Calculated Fields are indeed stored, how come there is no Field Size property dictating how much space they're allowed to consume?

[CJ_London]

how come there is no Field Size property dictating how much space they're allowed to consume?

you specify the result type as single/double etc - even text per your requirement - otherwise it defaults to what it thinks best based on the calculation.

I've just created a test table, added a calculated field and saved. Then entered a few records. Next I wrote a query to return the calculated value - it returned correctly. I then went into table design view and changed the calculation and saved the table (without opening the table to view the data). I reopened the query and the calculated field showed the revised calculation. So I suspect Allen Browne is wrong on this point. Perhaps it was a problem but now resolved through updates - and he does say 'may' not be updated correctly. However I agree with his other comments - no indexing and can be too limiting.

I have yet to see a good argument for using them in a db scenario, but can see them appealing to those who (quite legitimately) work directly with tables and perhaps limited use of queries from an 'excel' perspective - BA's and the like - who probably don't use forms/reports or any code/macros other than to execute a chain of queries. The same goes for lookup fields

[MatthewGrace]

Thank you for the response.
1) I questioned earlier "If Calculated Fields are indeed stored, how come there is no Field Size property dictating how much space they're allowed to consume?". Your answer is correct in that you can specify the Result Type (one of which is "Short Text")... but even still there is no Field Size property determining how many characters that Short Text can contain, which makes me believe it's not stored. The need for "Result Type" may be for future use as a number in a math expression, or as a string for parsing, etc. I'm more and more convinced that Calculated Fields truly are designed to be processed on-the-fly, so their results are perpetually current, thus skirting the very obvious problem Allen Browne described.

2) With that out of the way, I don't yet see the bad rap for calculated fields. Sure queries can do the same and more, but can they do a calc field any better or is it just preference? I think the answer lies in this "Indexing" you've both described, but I'm admittedly ignorant to how it works. I've looked up the first few paragraphs on Wikipedia of "Database Indexing", and the only takeaway I can actually grasp at my low level is that it somehow makes things faster at the expense of disk space... I just don't know how to visualize and really "feel" what difference that will make in my projects unfortunately, a serious complaint I have about learning Access / DB anything in general. That is, once you get past a fairly fundamental level of teaching videos/texts there are few resources, and none designed to usher you to the "intermediate" or "advanced" level. At that point, one must painfully grind out experience one-Googled-nugget-of-info-at-a-time at a crippled speed.

For now, I will compromise and make an expression Query that calculates this. I hope it doesn't run slow. If it does, I can easily revise it in the future.

Thank you for your time and efforts, Ajax.

[CJ_London]

there is no Field Size property

agreed, but this is a property of the field - the max num of characters (just as integer, long etc effectively specify a maximum size), access does not reserve this space, used or not for a record, just the amount required for the data it contains.

I don't yet see the bad rap for calculated fields

nor do I - just that 'queries can do the same and more'. As you will find out with any small degree of complexity in your data, you will find a calculated field is insufficient - they can't use UDF's, refer to another record in same or another table.

Indexing - not sure where you have been reading but they are essential for the fast running of a db. Expense of disk space? - disk space is cheap and they do not take up that much space. For small db's - a few '000 records per table and simple queries it is probably not noticeable but more records and more complex queries and you will start to notice it. A simple rule of thumb is to open the table and look at the recordcount at the bottom. If it is not populated instantly, you need indexing because the time it takes is the time it takes to do a sequential search which is the only other way of finding a particular record. Very simplistically, let's say it takes a second - so if you have query which returns 100 records that is 100 sequential searches - at an average speed of 1/2 second (assuming records are evenly spread around the table) for each search that is 50 seconds. With an index it will be instantaneous.

Think of the table as being a library of books all stored in a random order (which is how a table stores it records, they are not stored in the order you enter them). You don't have an index so to find a particular book, you go along the shelves until you find the one you want - sometimes you are lucky and it is on the first shelf, sometimes not and it is on the last shelf. Now the librarian realises this takes too long so they create an index - a piece of paper which contains say book title and the aisle and shelf the book is stored on maintaining in alphabetical order of the book title. Now you can scan down the piece of paper, find the book you want and go straight to the shelf. That is what an index is - a separate 'object' containing the value of a field maintained in order and a pointer to that record in the table.

To understand why indexing is so efficient, you need to understand how computers connect with the datasource - simplistically in the case of a disk it will 'scoop up' an amount of data, not sure what it is these days but probably something like 4mb (when I started it was 512k) look for what is required and if it doesn't find it, scoop up the next amount. If it is scooping up whole records (for a sequential search) it might pick up say 100 records, but if indexed it might pick up 1000 (because indexes are smaller) so will find your record 10 time quicker - and with indexing algorithms it will have a better idea of what to scoop up next - in the analogy above, you look at the first page - books starting with A, but since you are looking for a book starting with Z, you know to go to the last page and work backwards.

If you can create a table with say 100000 records and not indexed, open the table and do a sort - see how long it takes. Then index that field and see how long it takes again. I've experienced situations with 3 million+ records and a complex query taking 12 or 13 hours to run. When indexed, it was down to 20 minutes (I was called in when it was set running before everyone went home and was still running when they got in the following day).

Indexing does have a time overhead when a record is inserted, deleted or the indexed field changed because it needs to be updated to maintain the order. But this more than pays dividends when you want to find that record again. However there is no point in indexing for the sake of it, just those fields you are going to join on or regularly sort and/or filter on. There is also little point in indexing fields which have few distinct values (like Booleans or 'shipped/finished') or contain a lot of nulls because the index itself will not be that efficient. Go back to library analogy - how useful is it to have pages of 'finished' followed by pages of 'shipped'?

With regards facilities for advanced users, they do exist but you need to pay (basically back to college) and the more advanced you get the more specific your requirement/focus. To find stuff on the web, it helps to learn the correct terminology and how to search properly - again you will find plenty of help on the web for how to do this - e.g. precede your search string with 'access' or 'vba' or 'access vba'

[MatthewGrace]

Thank you for this explanation.

Someone +rep this man.