Excel - large files slow when sorting/calculating

[Bofinn]

On a clean install (plenty of free space) I'm doing some testing. It's a 17MB spreadsheet with 50K rows of data.

When you sort a column using Excel 2003 it takes 20 minutes to calculate, and it is using 50% of the processor.

So I tested Excel 2007 - it only takes 10 minutes to sort, but it uses almost 100% of the processor.



I am aware of turning the auto calculate off, but that is not an option that will help my department. Everything else I read recommends going to a database.

This is a monthly report and they refuse to split it up weekly, because it's used to pay insurance agent's commissions.

Any suggestions? Also, MS Access is not supported at work, I think we use DB2.

[rjs735]

I can't answer your Excel question other than if you are cross footing as well as column calcs they take some time.

Access or DB2 they are both relational databases. Access was designed to run on a pc from the ground up. DB2 is IBM's relational Datbase it was originally designed to run on IBM mainframes. I was a specialist on the product many years ago.

Then IBM ported DB2 to run on pcs competing against MS Access. I haven't looked at it but I bet DB2 on a pc has a GUI interface to host mainframe db2 database. So in the end you would have a host mainframe do the calcs and then store them in the DB and have user on a PC with the DB2 gui access the data.

My guess would be to see if the spread sheet ran better on a quad processor and then use a dedicated system to do the clacs and store them in retrieveable form from shared file access. If the shared were req.

I jumped in with my knowledge of DB2 and was able to create tables and queries and reports with access within a day. All the functional elements of a relationship can be set up. Although some of the more comples relationships may require additional cols or tables to get the results you want.