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/...rge-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...axlocksperfile

[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?