12-06-2018, 05:38 AM
|
#1
|
Newly Registered User
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
|
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?
|
|
|
12-06-2018, 05:41 AM
|
#2
|
Newly Registered User
Join Date: May 2018
Location: USA baby
Posts: 1,177
Thanks: 22
Thanked 311 Times in 302 Posts
|
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.
|
|
|
12-06-2018, 05:43 AM
|
#3
|
Newly Registered User
Join Date: May 2018
Location: USA baby
Posts: 1,177
Thanks: 22
Thanked 311 Times in 302 Posts
|
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.
|
|
|
12-06-2018, 06:17 AM
|
#4
|
Happy Retired Curmudgeon
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,896
Thanks: 65
Thanked 1,303 Times in 1,200 Posts
|
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.
|
|
|
12-06-2018, 03:39 PM
|
#5
|
Super Moderator
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,496
Thanks: 13
Thanked 1,440 Times in 1,372 Posts
|
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
|
|
|
12-07-2018, 03:16 AM
|
#6
|
Newly Registered User
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
|
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?
|
|
|
12-07-2018, 03:56 AM
|
#7
|
Newly Registered User
Join Date: May 2018
Location: USA baby
Posts: 1,177
Thanks: 22
Thanked 311 Times in 302 Posts
|
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/
|
|
|
12-07-2018, 04:26 AM
|
#8
|
Newly Registered User
Join Date: May 2018
Location: USA baby
Posts: 1,177
Thanks: 22
Thanked 311 Times in 302 Posts
|
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)
|
|
|
12-07-2018, 04:59 AM
|
#9
|
Super Moderator
Join Date: Feb 2013
Location: UK
Posts: 10,600
Thanks: 40
Thanked 3,429 Times in 3,320 Posts
|
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
|
|
|
The Following User Says Thank You to CJ_London For This Useful Post:
|
|
12-07-2018, 05:04 AM
|
#10
|
Newly Registered User
Join Date: May 2018
Location: USA baby
Posts: 1,177
Thanks: 22
Thanked 311 Times in 302 Posts
|
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.
|
|
|
12-07-2018, 06:45 AM
|
#11
|
Happy Retired Curmudgeon
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,896
Thanks: 65
Thanked 1,303 Times in 1,200 Posts
|
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.
|
|
|
12-09-2018, 05:31 AM
|
#12
|
Newly Registered User
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
|
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
|
|
|
12-09-2018, 06:02 AM
|
#13
|
Part time moderator
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,298
Thanks: 99
Thanked 2,124 Times in 1,973 Posts
|
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
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.
|
|
|
12-09-2018, 06:42 AM
|
#14
|
Happy Retired Curmudgeon
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,896
Thanks: 65
Thanked 1,303 Times in 1,200 Posts
|
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.
|
|
|
12-14-2018, 03:25 AM
|
#15
|
Newly Registered User
Join Date: Mar 2014
Posts: 170
Thanks: 36
Thanked 0 Times in 0 Posts
|
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?
|
|
|
Thread Tools |
|
Display Modes |
Rate This Thread |
Linear Mode
|
|
All times are GMT -8. The time now is 09:38 PM.
|
|