Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-06-2018, 05:38 AM   #1
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
Leo_Polla_Psemata is on a distinguished road
Does calculated fields cause database going slow?

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?

Leo_Polla_Psemata is offline   Reply With Quote
Old 12-06-2018, 05:41 AM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 707
Thanks: 14
Thanked 162 Times in 160 Posts
MajP will become famous soon enough
Re: Does calculated fields cause database going slow?

Yes it would. Most people shy away from these. Do your calculations as needed in queries or on forms and reports.
MajP is offline   Reply With Quote
Old 12-06-2018, 05:43 AM   #3
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 707
Thanks: 14
Thanked 162 Times in 160 Posts
MajP will become famous soon enough
Re: Does calculated fields cause database going slow?

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.

MajP is offline   Reply With Quote
Old 12-06-2018, 06:17 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,591
Thanks: 62
Thanked 1,208 Times in 1,108 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Does calculated fields cause database going slow?

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-06-2018, 03:39 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,449
Thanks: 13
Thanked 1,422 Times in 1,355 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Does calculated fields cause database going slow?

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-07-2018, 03:16 AM   #6
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
Leo_Polla_Psemata is on a distinguished road
Re: Does calculated fields cause database going slow?

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?
Leo_Polla_Psemata is offline   Reply With Quote
Old 12-07-2018, 03:56 AM   #7
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 707
Thanks: 14
Thanked 162 Times in 160 Posts
MajP will become famous soon enough
Re: Does calculated fields cause database going slow?

Quote:
can someone find a sample database with 100000 records for testings?
Works for me
Code:
http://www.databasetestdata.com/

MajP is offline   Reply With Quote
Old 12-07-2018, 04:26 AM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 707
Thanks: 14
Thanked 162 Times in 160 Posts
MajP will become famous soon enough
Re: Does calculated fields cause database going slow?

For maintainability and reuse (and understandability) I would do anything like this in a UDF.
Code:
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
Code:
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
Code:
Select SomeFunc([TL],[aatn],[aatr]) as SomeName...
or in a calculated control
Code:
=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)
MajP is offline   Reply With Quote
Old 12-07-2018, 04:59 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Does calculated fields cause database going slow?

Quote:
. 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

Code:
 
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

Code:
WHERE [aatn]-[aatr] between tblRanges. aaFrom and tblRanges.aaTo
and your calculation something like

Code:
 
ValueRequired:(([aatn]-[aatr]-aadeduct)*aaMultiplier)+aaAddition
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
MajP (12-07-2018)
Old 12-07-2018, 05:04 AM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 707
Thanks: 14
Thanked 162 Times in 160 Posts
MajP will become famous soon enough
Re: Does calculated fields cause database going slow?

Quote:
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.
MajP is offline   Reply With Quote
Old 12-07-2018, 06:45 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,591
Thanks: 62
Thanked 1,208 Times in 1,108 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Does calculated fields cause database going slow?

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
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-09-2018, 05:31 AM   #12
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
Leo_Polla_Psemata is on a distinguished road
Re: Does calculated fields cause database going slow?

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
Leo_Polla_Psemata is offline   Reply With Quote
Old 12-09-2018, 06:02 AM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,439
Thanks: 92
Thanked 1,831 Times in 1,701 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Does calculated fields cause database going slow?

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 12-09-2018, 06:42 AM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,591
Thanks: 62
Thanked 1,208 Times in 1,108 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Does calculated fields cause database going slow?

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
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-14-2018, 03:25 AM   #15
Leo_Polla_Psemata
Newly Registered User
 
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
Leo_Polla_Psemata is on a distinguished road
Re: Does calculated fields cause database going slow?

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?

Leo_Polla_Psemata is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
calculated fields cant see in the database Dhanu Forms 36 12-02-2017 09:18 AM
Calculated text box not working with slow connection to backend tomusn83 Forms 4 12-21-2015 05:17 AM
Calculated boxes slow to draw?? NZArchie Reports 4 10-12-2011 12:46 PM
populate textbox with calculated value taking values from database and form fields. pinky Modules & VBA 7 07-10-2009 06:20 PM
Slow Database? HELP! phn General 1 04-04-2006 05:21 AM




All times are GMT -8. The time now is 11:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World