slow database

[pmusaev]

I am in the process of creating a database that contains approximately 45 calculation fields. When I define the calculation and hit "Ok" my computer takes about 15 seconds to process the command. What is this a sign of and should this concern my database performance down the road when I have 200 calculated fields.

cheers!

[Vaughan]

It's more to do with the number of records than number of calculation fields. I must ask, why do you need 200 calculation fields?

Even so, it doesn't matter how long it takes because users shouldn't be defining fields.

[pmusaev]

it's painful for me right now as i set it up... i have 200 calculation fields because i am tracking the content of half a dozen fields for certain codes (approx. 200 codes)... is this common practice? thanks for the insight!

ps. there are no records in the dBase yet

[pmusaev]

stored vs. unstored?

[Vaughan]

I can think of a couple of ways of doing it that don;t involve 200 hard-coded calculation fields. (The problem with hard-coding stuff is that it's a bugger to change later, and it should be avoided.)

One is a single calculation field with a 200-odd line Case() function. This will still have hard coding in it but it's 199 fields better.

Another method is to set up file that holds the codes (each record is one code) and relate the main file to this code file.

[pmusaev]

Vaughan,

Can you take a few moments and help me put this together... I am interested in your suggesstions. What I have is a time card database and each field I have is looking for a certain "Code" and then if it is found, it tracks that code and then another summary field totals up the amount of hours allocated to that specific "Code". Here is an example of one of the 200 (actually more) fields:

Case( costCode_a = "03-02-090" , a1_ot + a2_ot + a3_ot + a4_ot + a5_ot + a6_ot + a7_ot + a8_ot + a9_ot + a10_ot + a11_ot + a12_ot , 0 ) +

Case( costCode_b = "03-02-090" , b1_ot + b2_ot + b3_ot + b4_ot + b5_ot + b6_ot + b7_ot + b8_ot + b9_ot + b10_ot + b11_ot + b12_ot ) +

Case( costCode_c = "03-02-090" , c1_ot + c2_ot + c3_ot + c4_ot + c5_ot + c6_ot + c7_ot + c8_ot + c9_ot + c10_ot + c11_ot + c12_ot ) +

Case( costCode_d = "03-02-090" , d1_ot + d2_ot + d3_ot + d4_ot + d5_ot + d6_ot + d7_ot + d8_ot + d9_ot + d10_ot + d11_ot + d12_ot ) +

Case( costCode_e = "03-02-090" , e1_ot + e2_ot + e3_ot + e4_ot + e5_ot + e6_ot + e7_ot + e8_ot + e9_ot + e10_ot + e11_ot + e12_ot ) +

Case( costCode_f = "03-02-090" , f1_ot + f2_ot + f3_ot + f4_ot + f5_ot + f6_ot + f7_ot + f8_ot + f9_ot + f10_ot + f11_ot + f12_ot ) +

Case( costCode_g = "03-02-090" , g1_ot + g2_ot + g3_ot + g4_ot + g5_ot + g6_ot + g7_ot + g8_ot + g9_ot + g10_ot + g11_ot + g12_ot )

even better... I will attach my file with this post... please provide any suggestions you may have asap, as I write this... i am also [slowly] working on the dBase. Cheers!

timesheet.zip

[bruceR]

pmusaev said:

I am in the process of creating a database that contains approximately 45 calculation fields. When I define the calculation and hit "Ok" my computer takes about 15 seconds to process the command. What is this a sign of and should this concern my database performance down the road when I have 200 calculated fields.

The fact that it takes as few as 15 seconds indicates that you are working with a very small data set.

As others have mentioned, it is probably time to do some redesign. But if your recalc is only taking 15 seconds, that is nothing to worry about. Get a large data set and it can take half an hour.