Recalc Table very slow and error message

[John Fringer]

I have a table with about 100,000 records. The table has several calculated field rules.

Perhaps this is a design problem, but the table takes 45 - 60 minutes to recalc.

Also, when I attempt to pack the table I receive "Could not pack or rebuild indexes" error. However, when click "ok" on the error message it appears to complete and returns "table packed".

My questions to the experts are: 1. Does the time to recal the table seem "reasonable", 2. are there things I should or could do to speed up the calculations and 3. what does the error message mean?

Thanks...John

[Steve Workings]

Re: Recalc Table very slow and error message

I think it would help a whole lot if you told/showed us what calculations you're using.

[Selwyn Rabins]

Re: Recalc Table very slow and error message

I have a table with about 100,000 records. The table has several calculated field rules.

Perhaps this is a design problem, but the table takes 45 - 60 minutes to recalc.

Also, when I attempt to pack the table I receive "Could not pack or rebuild indexes" error. However, when click "ok" on the error message it appears to complete and returns "table packed".

My questions to the experts are: 1. Does the time to recal the table seem "reasonable", 2. are there things I should or could do to speed up the calculations and 3. what does the error message mean?

Thanks...John

it would depend entirely on what calculations are being done. do you have lookups?

if your table just used simple calculations (e.g. no lookups), then on a resonable machine, 100,000 records should not even take a minute.

[John Fringer]

Re: Recalc Table very slow and error message

Thanks for the quick reply!

Here is a good sample of the calcs.

Field (Rate_Id) == TRIM(RATE_PREFIX)+CYEAR(TO)+TRIM(FROM_MO_FIX)

Field (Contract_term) == CONVERT_TYPE(IF(MONTH(TO)=1,YEAR(TO)-1,YEAR(TO)),"c")

(1) Field (Cap_Cost) == ((LOOKUPN("F",A,"Scaling","pjm","Pjm_Id")*(CPLC/1000))*LOOKUPN("F",A,"capacity","pjm","Pjm_Id")*DAYS)/TOTAL_USE

(2) Fiedl (Trans Cost) == ((LOOKUPN("F",A,"Scaling","pjm","Pjm_Id")*(NPLC/1000))*LOOKUPN("F",A,"Transmission","pjm","Pjm_Id")*DAYS)/TOTAL_USE

There are 23 fields with essentially the same lookup formula as examples (1) and (2) above.

Thanks...John

[Selwyn Rabins]

Re: Recalc Table very slow and error message

Thanks for the quick reply!

Here is a good sample of the calcs.

Field (Rate_Id) == TRIM(RATE_PREFIX)+CYEAR(TO)+TRIM(FROM_MO_FIX)

Field (Contract_term) == CONVERT_TYPE(IF(MONTH(TO)=1,YEAR(TO)-1,YEAR(TO)),"c")

(1) Field (Cap_Cost) == ((LOOKUPN("F",A,"Scaling","pjm","Pjm_Id")*(CPLC/1000))*LOOKUPN("F",A,"capacity","pjm","Pjm_Id")*DAYS)/TOTAL_USE

(2) Fiedl (Trans Cost) == ((LOOKUPN("F",A,"Scaling","pjm","Pjm_Id")*(NPLC/1000))*LOOKUPN("F",A,"Transmission","pjm","Pjm_Id")*DAYS)/TOTAL_USE

There are 23 fields with essentially the same lookup formula as examples (1) and (2) above.

Thanks...John

of course it would be slow.

you are doing 23 lookups per record * 100,000 records. that's 2.3 million lookups. each lookup involves opening and closing a file.

[John Fringer]

Re: Recalc Table very slow and error message

Thanks Selwyn ... is there a better way to do this? I'm an Alpha hack that loves the solfware and has really helped me do some cool stuff. Just now learning how to be more efficient.

Is the packing error something to be concerned with?

[Selwyn Rabins]

Re: Recalc Table very slow and error message

Thanks Selwyn ... is there a better way to do this? I'm an Alpha hack that loves the solfware and has really helped me do some cool stuff. Just now learning how to be more efficient.

Is the packing error something to be concerned with?

i can't give an off the cuff answer to this. There may be a more efficient way but it would require a thorough understanding of what you are trying to achieve. If this is something that is very important to optimize, it might be something to refer to Alpha Consulting Services.

with regard to the pack error:

does your table have indexes?
if you remove the indexes then pack, do you still get the error?

if the message happens all of the time, then you could send us the table and we will investigate.

[G Gabriel]

Re: Recalc Table very slow and error message

Thanks Selwyn ... is there a better way to do this?

Selwyn beat me to it.. your lookups is what is slowing you down. I admire you though.. did you really sit in front of your computer for 45-60 minutes waiting for it to calculate? I wish I had that kind of patience..

But to your question: is there a better way?
Perhaps.
Do not put these expressions in the field rules. If these are calculated fields, get rid of them. There was a thread very recently about the same issue. You are much better off putting these calculations in calc fields in a layout, then they will recalc only when you fetch to the desired record(s).