mrderekw24
New Member
- Joined
- Jan 22, 2012
- Messages
- 7
Hello Everyone,
A team I support uses a workbook that has been stripped down to the bare bones for troubleshooting purposes and still has terrible calculation times. Here are a few examples of the calculation process times, which pegs the CPU at 100%. Now it's down to only two sheets at the moment. 400,000 rows by 13 rows on 1st sheet. 119,000 rows by 2 columns on 2nd sheet (table array). It takes 5 minutes to delete 5000 rows while trying to strip it down by the way. We have tested on about 10 different machines to this point.
Filtered on three columns.
Formula1 example =+I378531+H378531
Vlookup1 exmaple =VLOOKUP(C378530,Sheet1!A$2:B$119070,2,FALSE)
Calculation times (on a filter tick on vlookup column);
1 min. 4%
3 min. 13%
5 min. 23%
7 min. 38%
10 min. 47%
I know diagnosing with this info will not happen but can anyone help me with suggestions of speeding up this process (besides turning off auto-calc)? We are ordering beefy hardware and software that will cut this time in half as testing has proven. That's a good start. But 7 minutes for a simple filter tick or save is still not good. Multiple this by 100 filters per day and this team is just sitting around most of the time. They often just go to lunch and sometimes return and it's still calculating. They would love to get to 1 million rows (like Microsoft advertised) but can't even effectively work with 200,000 rows. I believe this team would pay for consulting if they could triple the rows and cut down the calculations by 4x. Turning off auto calculation is a band-aid and they still have to manually calculate 50 times per day. Why they need to recalculate on Vlookups that much per day is beyond me and goes way over my head. So answering questions to their methodology or workflow will be tough for me. Access and DB's will not work according to them. They need the flexibility to filter and manipulate on the fly all day long.
Thank you,
Derek
A team I support uses a workbook that has been stripped down to the bare bones for troubleshooting purposes and still has terrible calculation times. Here are a few examples of the calculation process times, which pegs the CPU at 100%. Now it's down to only two sheets at the moment. 400,000 rows by 13 rows on 1st sheet. 119,000 rows by 2 columns on 2nd sheet (table array). It takes 5 minutes to delete 5000 rows while trying to strip it down by the way. We have tested on about 10 different machines to this point.
Filtered on three columns.
Formula1 example =+I378531+H378531
Vlookup1 exmaple =VLOOKUP(C378530,Sheet1!A$2:B$119070,2,FALSE)
Calculation times (on a filter tick on vlookup column);
1 min. 4%
3 min. 13%
5 min. 23%
7 min. 38%
10 min. 47%
I know diagnosing with this info will not happen but can anyone help me with suggestions of speeding up this process (besides turning off auto-calc)? We are ordering beefy hardware and software that will cut this time in half as testing has proven. That's a good start. But 7 minutes for a simple filter tick or save is still not good. Multiple this by 100 filters per day and this team is just sitting around most of the time. They often just go to lunch and sometimes return and it's still calculating. They would love to get to 1 million rows (like Microsoft advertised) but can't even effectively work with 200,000 rows. I believe this team would pay for consulting if they could triple the rows and cut down the calculations by 4x. Turning off auto calculation is a band-aid and they still have to manually calculate 50 times per day. Why they need to recalculate on Vlookups that much per day is beyond me and goes way over my head. So answering questions to their methodology or workflow will be tough for me. Access and DB's will not work according to them. They need the flexibility to filter and manipulate on the fly all day long.
Thank you,
Derek
