Does calculated fields cause database going slow?

[Leo_Polla_Psemata]

Hi
I am building a new data base. I have created a table with more than 8 calculated fields. Some of these calculations include four or five IIF's and combination of three of four fields.The table will increase to 10000 records every year and there are around 30 fields.

The table works just perfect (fast) with 50 records that I have inserted as example.

The question is, does such calculated fields add too much burden in database? Will make the whole database go very slow after I get more records?

What would it be better and faster?
A simple table that just stores data and twenty queries around it or a table with many complicated calculated fields and just three or four queries?

 

[MajP]

Yes it would. Most people shy away from these. Do your calculations as needed in queries or on forms and reports.

 

[MajP]

Another thing is if you are looking at a form or report in single form view it is faster to do the calculation in the control and not the query. That is because it only calculates the one value when you view it, where the query will calculate all.

 

[The_Doc_Man]

You almost NEVER want to have a calculated field in a table. Offhand I don't know of any VALID reason to have one. EVERY TIME you open the table, if you have a computation, you have to develop the computational result even if the reason you are opening that table happens to not need those particular computations.

Opening a query that contains the computation gives you the option to have a second query that doesn't contain the computation if you were not doing anything with those fields for that query. I.e. a way to avoid the overhead. And queries are cheap. The only thing they really store (other than query properties) is an SQL string, which is limited to a few thousand bytes for the worst query you could imagine.

MajP's comment about forms & reports is also true and relevant. Think of it this way. What good is a table? No good at all - unless you have a way to use it. So put the workload on USING the table and save the table space for other purposes. Keeping the table shorter makes that table faster to use because more can fit into a single disk buffer at one time.

 

[Pat Hartman]

I'm assuming that you are using Data Macros and I guess my understanding of how they work must be different. I thought that the calculated values actually were stored which allowed the creation of indexes - which I think is the only valid reason for storing a calculated value. Given that understanding, the actual calculations would only be performed when a record was saved. Data macros are extremely limited in what they can do so I have not found a use for them in any current applications.

I haven't seen anything that explicitly says what happens behind the scenes. I would create a table of sample data containing at least 100,000 rows to determine how the table performs with a moderate number of rows.

 

[Leo_Polla_Psemata]

Your comments are highly appreciated.


One calculated field is like this
IIf([TL] Like "sz1",IIf(([aatn]-[aatr])<=5,0,IIf(([aatn]-[aatr])<=15,((([aatn]-[aatr])-5)*105),IIf(([aatn]-[aatr])<=20,(((([aatn]-[aatr])-15)*137)+1050),(([aatn]-[aatr])-20)*250)+6000)),IIf(([aatn]-[aatr])<=5,0,IIf(([aatn]-[aatr])<=15,((([aatn]-[aatr])-5)*686),IIf(([aatn]-[aatr])<=20,(((([aatn]-[aatr])-15)*1028)+6860),(([aatn]-[aatr])-20)*1368)+12000)))


It works, it brings the result I need. There more similar calculated fields like this one.

Understand that it is much better to perform calculations outside table.



Anyway, do you have any idea where can someone find a sample database with 100000 records for testings?

 

[MajP]

can someone find a sample database with 100000 records for testings?

Works for me

http://www.databasetestdata.com/

 

[MajP]

For maintainability and reuse (and understandability) I would do anything like this in a UDF.

IIf([TL] Like "sz1",IIf(([aatn]-[aatr])<=5,0,IIf(([aatn]-[aatr])<=15,((([aatn]-[aatr])-5)*105),IIf(([aatn]-[aatr])<=20,(((([aatn]-[aatr])-15)*137)+1050),(([aatn]-[aatr])-20)*250)+6000)),IIf(([aatn]-[aatr])<=5,0,IIf(([aatn]-[aatr])<=15,((([aatn]-[aatr])-5)*686),IIf(([aatn]-[aatr])<=20,(((([aatn]-[aatr])-15)*1028)+6860),(([aatn]-[aatr])-20)*1368)+12000)))

That to me is not maintainable
Make the UDF

Public Function SomeFunc(TL as variant, aatn as variant, aatr as variant) as variant
  'I do not know your real datatypes so made them variants 
   dim diff as double
   if not (isnull(tl) or isnull(aatn) or isnull(aatr) then 
     diff = aatn - aatr
     if diff <=5 then 
       someFunc = 0
       exit function
     end if
     if TL = "Sz1" then
        select case diff
          case is <=15
              somefunc = (diff-5) * 105
          case is <=20
              someFunc = (diff-15) * 137 + 1050
          case else
              SomeFunc = (diff-20) *250 + 6000
        end select
     else
        select case diff
          case is <=15
              someFunc = (diff-5) * 686
          case is <=20
               someFunc = (diff-15) * 1028 + 6860
          case else
               SomeFunc = (diff-20) * 1368 + 12000
        end select    
     end if
   end if

Now you can use it anywhere
in a query

Select SomeFunc([TL],[aatn],[aatr]) as SomeName...

or in a calculated control

=SomeFunc([TL],[aatn],[aatr])

And the best thing about the UDF is that you can test it anywhere by providing literals
debug.print someFunc("sz",20,10)

 

[CJ_London]

. I thought that the calculated values actually were stored which allowed the creation of indexes

Nope - you can't index calculated fields

with regards your example calculation, unlike Majp, I would store the calculation elements in a table, then just join to the table. Perhaps something like

tblRanges
aaSize...aaFrom..aaTo..aaDeduct..aaMultiplier..aaAddition
sz1...…..0...……….5...….0...…………..0...……………..0
sz1...…..6...……...15.....5...………...105...…………..0
sz1...…..16...…….20.....15...………..137...………….1050
sz2.......0...……...5...….0...…………..0...……………..0
sz2.......6...……...15....5...…………..686...………...0
etc

have your query join tblRanges on TL=aaSize plus criteria something like

WHERE [aatn]-[aatr] between tblRanges. aaFrom and tblRanges.aaTo

and your calculation something like

ValueRequired:(([aatn]-[aatr]-aadeduct)*aaMultiplier)+aaAddition

 

[MajP]

unlike MajP, I would store the calculation elements in a table

Good catch. Yes you would always want to do it your way first. Do the heavy lifting in SQL.
I was so busy trying to decipher the huge iif did not even see the pattern.

However, the concept still holds. If you can only do it in code then consider a UDF for reuse ability, maintainability, readability.

 

[The_Doc_Man]

As expected, Allen Browne has something to say on the subject, but he explicitly touches on the calculated field option that was introduced in Ac2010.

http://allenbrowne.com/casu-14.html

 

[Leo_Polla_Psemata]

Hi
I made a test with 100K records, with one calculated field, it worked flawlessly, when I added a second calculated field, i took this



file sharing lock count exceeded. Increase MaxLocsPerFile registry entry

 

[isladogs]

That really doesn't surprise me!

There are 2 methods for increasing the MaxLocksPerFile.
See https://support.microsoft.com/en-gb/help/815281/file-sharing-lock-count-exceeded-error-message-during-large-transactio

Suggest you do so temporarily using method 2
Increase in small steps e.g. from 9500 to 20000 but go higher if necessary.
If you make it too high e.g. 1,000,000 it may possibly 'unbalance' the system settings and cause other issues

 

[The_Doc_Man]

Found this thread that is relevant to Colin's post. Go ahead and play with the MaxLocksPerFile value, but as he suggests, do the changes stepwise, not one huge jump.

https://www.access-programmers.co.uk/forums/showthread.php?t=296647&highlight=maxlocksperfile

 

[Leo_Polla_Psemata]

I have been convinced that calculated fields on tables is a heavy database sin.

It could make your life easier and then make your life hell.



Suppose I have a table T and fields A,B,C,D etc

I don't want to have A as calculated field but A takes date from B,C,D.



I could create an update query and, manually, update details to A.


Could I create a class module that updates automatically fields in a table,based on a query?

 

[isladogs]

I have been convinced that calculated fields on tables is a heavy database sin.

I would agree that calculated fields are almost always the wrong solution.
So don't do this

Just calculate A based on B,C,D as and when required in a SELECT query.
Do NOT store the values in a table.

 

[The_Doc_Man]

You can, if you feel it is necessary, build an UPDATE query that would store the results of the computation. A class module might represent overkill, too much effort when an easier method is available.

Understand that theoretically, you can put anything in the table that you want. It IS, after all, YOUR database. BUT what you just described is a technical design violation. Let me explain.

When you have normalized data tables, then one (out of many) properties that should apply is that any field in a well-structured table depends ONLY on the primary key of the table. For your table where field [A] depends on , [C], and [D], the ONLY way that would be within "proper" design guidelines is if , [C], and [D] were part of a compound key that was the prime key of that table. Otherwise, [A] depends on something that is NOT the PK of the table, and this usually leads to difficulties.

Know this: NOTHING in Access absolutely requires the presence of a table per se, though MANY things demand a recordsource. A table is a valid recordsource. But so is a query based on a table, even if it contains computed values. So if you have this field [A] that needs to be present in a report, compute it on the fly either in a QUERY that underlies the report, or in the appropriate control on the report itself.

As to storing a computed value, here is the question that will forever bug you every time you need to consider a "computed field" solution: Can this field [A] ever be updated to override the computation? AND if so, how would you know that an override occurred?

This comes down to two scenarios: (a) you can never override the computation. In which case you can hard-code that in the report or form and not even bother to store it. OR (b) you CAN override the computation, in which case the fact that the field is computed might get in the way, because its definition no longer would match its value.

Let's toss one more stink-bomb. So you have these fields , [C], and [D] that are mish-mashed together some way to yield field [A]. What is supposed to happen if any one of those fields changes in value? Obviously, if they are part of a compound primary key, that is unlikely, since one of the questions regarding selection as part of a PK is mutability. But if they are NOT part of the PK, then if they can EVER change, what happens to [A]? If you have the computation in a query then opening the query automatically updates the computations.

I'm explaining this to you in this way so that perhaps you can see the viewpoint that is commonly taken in terms of data design. These are the kinds of things you must think about for each data element in your DB. Sometimes a field is just an input field and the question is trivial. Sometimes, though, it takes a bit more forethought.

 

[CJ_London]

This comes down to two scenarios

@ Doc, I would make that three. The third scenario is where the calculated value must never change. for example an invoice line total. You might store the invoice quantity and price and calculate the value - but if you change the calculation down the line then that value would change which means when you print it out for the benefit of the taxman 2 years on they might see a different value to the one that went to the customer. For invoices I always recommend storing all elements of the calculation plus the results |(e.g. price, quantity, discount percentage, discount amount, NetValue, tax percentage, tax amount, TotalValue). I would also store the totals in the invoice header for the same reason - just in case a line record goes missing

It's a bit like your a) but you can't hard code the value - and with the possible exception of quantity, all the other values will have come from other tables rather than the same table