Excel 2007 resource issue

Question

I created an Excel 2007 spreadsheet with a couple of tabs, each of which is linked to a a view on SQL Server 2005 which, in turn, is based on a single table (the same one for both).   I have added extra columns to do calculations and added a third tab with a summary table and a pie chart.

The file size is 56MB whereas the base table is only 36MB and the views only grab about 50% of that data I would guess.  

Saving the spreadsheet was taking over an hour each time, refreshing data was slow too whereas getting the data returned to the views in SQL Server takes just a few seconds.

Addituonally Excel would crash during development so I had to save often (and wait an hour!!).  It took me many days to develop what should have been done in a half the time but I eventually got there. 

Feeling proud of myself I closed the spreadsheet, composed an email to the customer and then thought I need to just check something on the file.... tried to open it again and it wouldn't because of lack of resources and a corrupted file!!  I had made a copy of the development version (and I was trying to open the copy).  I went back to the original version (which I knew was working previously) and got the same error.  I haver tried three different client machines.

Any advice on how to recover the file and/ or how to develop something that performs well?  I seem to have a lot of perfiormance issues with Excel 2007 - overall I am unimpressed :-(

Thanks,

Ian

Answer 1

You don't give enough information for anyone to hazard a guess at what the problem is, so I would suggest you break the problem down to see what is taking so long and what is using the workbook space and what is causing the crashes.

- is it getting the views into Excel?

- is it the calculations on your summary tab?

- or what?

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 2

Thanks Charles,

I was hoping that someone else had experienced general problems with Excel 2007 connecting to SQL server and might have a couple of generic fixes.

In answer to your questions:

1. Yes the data was getting returned to Excel without a problem
2. There are simple SUMIF calculations and a total row on the summary tab and the table is only 8 x 8 (or thereabouts - I cannot open the spreadsheet to check)

Additionally:

• The first view that gets returned to the first tab is 88,793 rows in 45 columns
• the second view on the second tab is one column and 49,493 rows

• Most of the calculations are done in extra columns on the second tab and they refer to cells on the first and second tab. 
• These calculations are generally IF and SUMIF statements including a few AND functions. 
• The biggest nested IF statement is about 6 levels

Please let me know if you need any nmore information.

Ian

Answer 3

So how long does it take just to get the 2 views into a workbook without any calculations and save it?

Or maybe its the SUMIFS - calculate how many cells you are making Excel read or compare = number of sumifs * number of cells in the criteria * number of cells to sum.

Presumably you are NOT using array formulas?

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 4

Thanks Charles.

New workbook with just the data takes a few seconds for each view and maybe 20 seconds to save - Not sure how many cells were being summed but I will try to recreate the formulae now and save a copy at each step to see where the thing was falling down

No array formulae

Regards,

Ian

Answer 5

Hi Ian,

PMFJI...

Sounds like you could be better off pushing the views into a pivot table directly and doing some of the necessary calculations in the views themselves.

---

Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

Answer 6

Please feel free to jump in :-)

I have just found that the first thing I was doing in Excel slowed the file save considerably - concatenating two columns - I am now doing this in the view - let's see what occurs

Answer 7

Concatenation done in views data retrieved quickly, file saved in 5 seconds

The next step was to do the first calculation in just one column on the second sheet:

=COUNTIF(Sheet1!$C:$C,Table_sukghtssql01_CROSSOVER_PROD_VW_DistinctDentalPractices[[#This Row],[my_practice]])

This increased the save time to 5 minutes :-(, I deleted the data connections but there was no difference. 

Going back to Charles' post I have just calculated the number of calculations that need to be made for each column I add -

=49454*88794 or 4,391,218,476

I think this is why I have a problem!! :-)  Considering that I probably have about 20 calculated coplumns to add I expect I am being very optimistic :-)

Is there a more efficient way of doing this calculation in Excel or do I need to do everything I possibly can in SQL Server?  I am struggling with this as wel:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/f76287ce-7ca1-4f05-8afc-7f1981fafee5

...which is why I thought I may be able to do it in Excel

Thanks,

Ian

Answer 8

Hi Ian,

Since the calculation is only needed when you refresh data from SQL Server it makes more sense to have SQL server do the calcs to begin with.

---

Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

Answer 9

You can switch off calculation at save time, use Manual calc etc, but you still have to do the calculation at some point.

Sounds like you are getting about 15 million excel operations per second, which is probably about right for Countif.

Have you looked at using a Pivot table?

Alternatively you could probably sort the data when retrieveing the views , add 2 calculated column of formulae that give the start row and count of each different group (compare this row with last row, if different this is a start row)  and then work with subset groups of adjacent rows using an OFFSET formula

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 10

Thanks both, I am working on grouping and pivotting the data in SQL Server.  So far this seems to be working :-)