Slow workbook...

[Waxaholic]

I have a workbook with 8 sheets total. 4 sheets contain about 20 charts each. The remaining 4 sheets contain the data for the chart sheets. Each chart sheet has its own unique data sheet. The data sheets are populated via a macro which pulls in from an Oracle db via custom sql queries. The Charts in the chart sheets are tied to unique dynamic ranges for their updates. Typically it will take 2 minutes or longer from start to finish to execute 1 chart sheet query, load the returned Oracle data into the corresponding data sheet, and update the chart sheet. I have found that to be too slow because if i remove the other 3 chart sheets and corresponding 3 data sheets, leaving 1 chart sheet and 1 corresponding data sheet, the whole routine runs in about 30 seconds or less. Something is slowing down my process here. I have tried the usual "Application.ScreenUpdating....", "Application.Calculation...", etc. I have tried disabling calculation at the worksheet level on the other 3 chart sheets. I have even gone as far as setting the "SeriesCollection.Formula..." to a bogus entry like (0,0,0,1) for each series defined for a particular chart. I would then return the "SeriesCollection.Formula" to its original values later. This provided no speed increase. I think the charts in the other 3 chart sheets are being updated continuously while my Oracle data is being loaded into one of the data sheets. Neither of the 3 chart sheets are linked in any way to the downloaded data nor the data sheet it is being dumped into, yet the process is slow. I am at a loss on how to improve this. Any ideas would be welcome.

 

[nuked]

Do you have any array formulas that use column refs ("A:A"), as opposed to absolute ranges ("A1:A3000") etc?

Perhaps you could post your code too? Are you passing a query string to the DB or calling a stored procedure server side?

 

[Waxaholic]

The ranges are dynamic so they adjust to whatever is loaded into the data sheets. The query string is sent to the db and result set returned to excel. Typically a result set will contain no more than 288 rows 40 columns wide. So, this is not a large result set by any means. The code is too large to paste here and i don't think it is the problem since the code and result set is completed within 30 seconds. With 1 chart sheet and 1 data sheet, this result set is returned and charts refreshed in less than 30 seconds. With more than 1 chart/data sheet, the result/refresh takes 2 minutes or longer. So, it is only when i add additional chart/data sheets is when the slowness presents itself. The other chart sheets are the root of the problem but i am not sure why because they are not called upon nor should they be doing any updating. I am still tinkering with things in hopes of understanding why the additional chart/data sheets are slowing things down when they should be idle.