Irregular Macro Performance

I have a macro that is cycling through a collection that then utilizes selected members of that collection to flag selected excel rows for deletion. The coding seems to be relatively simple but the issue is this:

A.) There are approximately 2000 lines that it has to delete.
B.) On 2 of the office computers it takes approximately 20-30 seconds.
C.) On 2 other office computers, it takes approximately 90 minutes

***The behavior is completely replicatable, and happens 100% of the time. The fast PC's are always fast, and the slow PC's are always slow.

The code is below, but the following steps have already been taken:

1.) Screen updating is turned off
2.) Calculation is turned off
3.) All other files are closed when this file runs

The PC's are almost all identical (Dell Laptops, same models, approx 1 year old, 2 gig of RAM), utilizing Windows XP and Excel 2003 SP4(?). There is no difference in addins or reference libraries between the PC's, and no significant difference in installed software. There are also no noticeable drains in the task manager or processes hitting the CPU, besides Excel. The CPU is topping out at approx 55% on the slow running PC's. Not sure on the fast running ones.

Here is the code:
________________________________________________________________

Please Login or Register  to view this content.

_________________________________________________________________

Basically on the fast preforming PC's the problem line is instantaneous, on the slow performing PC's the problem line takes approx 5 seconds to execute. Multiply that 5 second execution time by 2000 executions and I've got a problem.

The only other piece of information is that I was able to speed execution up significantly, by hardcoding the formulas prior to line deletion. Theoretically this should make no difference as the calculation was turned off, but it did. Still not as fast as the 2 faster performing PC's, but performance went from 90 minutes to 3-4 minutes. Its almost as if its still calcing on the 2 slow ones and thats whats holding me up. I say almost, because the formulas in the cells are mostly DBRW formulas linking to Cognos TM1, and the data is not changing, so it appears that I'm not actually getting any new data, or calculations in those cells, while the process is running.

IMO, its almost as if there is a setting somewhere that I'm missing. If anyone has any idea what might be causing the slow execution of the exact same code, in these 2 PC's, I would greatly appeciate it.

Can you upload an example workbook?
I can see nothing wrong as such with the macro after a quick glance, but I would avoid using 'Cell' as as the name of a range as it is a valid WorksheetFormula, which could cause some confusion when coding, and also because it is too easy to mistype 'Cell' as 'Cells' which of course has a completely different meaning.

What I would find interesting would be for you to switch calculation to manual on those slow PC's, and then select a whole bunch of rows manually and delete them and see if it is still slow.

One thing I have noticed that you don't say is identical, is the actual data the 4 PC's are working on. Are all four processing the same macro on the same data, or does each PC have it's own dataset it is working on? (If the latter have you tried swapping the datasets around?)

hi,

Edit1: Welcome to the Forum

Does it make any difference if you also turn off events?

Please Login or Register  to view this content.

Or if you ensure the calculation is completed by including the one of the below?

Please Login or Register  to view this content.

The below link shows an alternative (more common?) approach of using a "built up" range rather than collection for deleting rows which works from bottom to top: http://www.vbaexpress.com/kb/getarticle.php?kb_id=260
Edit2: Does the macro in the link work any better than the one you currently have?

hth
Rob

Just another thought...

You could put this into the ThisWorkbook section of the VBA Editor:

Please Login or Register  to view this content.

At least then you will KNOW if the sheet is performing a calculation when you are not expecting it to?

A few additional questions:
1. Do you have the same AV software on all machines and is the Office plug-in (if there is one) turned on or off?
2. Do you have page breaks displayed?
3. Are all machines on the same network segment?
4. Do all machines use the same printer?

Additionally. I would suggest you would get better performance using an autofilter and deleting the visible rows, rather than looping.

hi all,

Here's a link to a thread chatting about the merits of a variety of approaches for deleting rows - hopefully the link works...
(there are also a few threads in this forum that show different approaches as well)
http://www.microsoft.com/communities...6-007444472176

If the link doesn't work you may be able to find it by going to the below page:
http://www.microsoft.com/communities...el.programming
& then using the Search function to find posts that include

specialcells "peter t"

.
The thread is called "Bulk Row Deletion - Fastest method" which is from 2004.
Note: this discusses the limitations of using Specialcells(xlvisible) for large data sets (I recognise these limits are greater than the size of the current data set but it is best to be aware of potential issues).

hth
Rob

Did you try my suggestion in post #4 so that you can tell whether calculation has actually been set to manual or not?

Of the methods suggested at Phil's link (nice link, Phil), I think Bullen's idea is best (no surprise); in an unused column, put a 1 in cells to be deleted, a 0 in cells to be retained, sort the data by that column, and delete the rows with a 1 in one fell swoop. As he points out, Excel's stable sorting means it does not disturb the original data order.

1. AV software = Anti Virus software. Programs like Norton often come with an Office plug in for scanning Office documents. This can decrease performance.
2. If page breaks are being displayed, then deleting a row will force a reevaluation of where the page breaks should now be. This will slow things down. It also requires communication with the printer (I believe), hence my question about printers. If you can set all machines to the same printer and test, you could at least rule that out.

As for the autofilter, you appear to be scanning for cells with a value of 1. If you simply autofilter for the value 1, you can then just delete the visible rows in one go rather than looping through each row.

lostit,

The difference is, you're deleting the rows one at a time. The suggestion is that you delete them all at once. Stomper's suggestion is similar (using AutoFilter). I wonder if deleting non-contiguous rows is more burdensome than contiguous rows ...

Originally Posted by Stomper

If page breaks are being displayed, then deleting a row will force a reevaluation of where the page breaks should now be.

Clever boy ...

How about clearing instead of deleting?

Originally Posted by shg

How about clearing instead of deleting?

This actually was an idea that I had been playing with, but it had a few issues for me. If I cleared contents and then deleted the line immediately, I had no improvement at all. If I cleared contents and then tried to cycle through the collection a second time, it somehow cleared out the collection as I had no members left. Not sure what I was doing wrong there, but I couldn't make it work.

What I did was combine a clearcontents command into the thought process used in the "build up" method mentioned in one of the other posts. So I built up my "DeletionRows" object with all of the combined addresses, but then cleared the contents of the flagged line at the same time. Once I had finished cycling through all of my detail, and clearing contents, I deleted the flagged, but empty rows, through a single deletion command associated with the "DeletionRows" object and got the performance I was looking for on all machines.

My performance for the entire routine was as follows:
Cycled through 10,000+ lines of detail (up from the 2,000+ in original example) and deleted down to about 200 remaining lines (the original base report). The original 2,000 line deletion under the old code took approximately 90 minutes. The new code with 10,000 lines takes exactly 19 seconds.

Here is the code so that you can see the changes. Thanks again to everyone for their feedback.

Please Login or Register  to view this content.

Please edit your post to add code tags.

Originally Posted by shg

Please edit your post to add code tags.

got it, thanks

Forum rules

lostit,

I am glad you managed to find a solution to your problem.
Due to the tendancy of of 'Union' to be slow, if you get a chance you might like to try the following code and see if it gives you a significant speed increase. It might be worth trying it both with, and then without the 'ClearContents' line, as I would be interested to see what effect that would have.

Once you determine whether the 'ClearContents' line is still needed or not you might then also like to try that macro with the RED lines commented, and the BLUE lines uncommented to see what effect it has.

I'm hoping that one of these should give you significant speed improvement.

If you do try it let me know how you get on please

Please Login or Register  to view this content.

Ah well, thanks for that. That has surprised me quite a bit, as usually the Union function runs pretty slow, but perhaps the TM1 data here is having more of an influence.
Thanks for trying

hi all,

I've been watching this thread but haven't tried testing anything myself b/c of the Cognos links seeming to have such an impact. Here are some thoughts/questions which may or may not be helpful...

0)
Lostit, when cross posting it is considerate to everyone if you provide a link to the cross posted thread, in this case: http://forums.olapforums.com/viewtopic.php?f=3&t=1276

1) I'm having trouble understanding the need for using a Collection of address Strings, rather than directly using the range found (be it row by row or using Union - potential speed issues aside). How did you get guided to this Collection approach?

2)

Originally Posted by lostit

Originally Posted by shg

Of the methods suggested at Phil's link (nice link, Phil), I think Bullen's idea is best (no surprise); in an unused column, put a 1 in cells to be deleted, a 0 in cells to be retained, sort the data by that column, and delete the rows with a 1 in one fell swoop. As he points out, Excel's stable sorting means it does not disturb the original data order.

Thats exactly what it already has. The code is actually using the first loop to cycle through that column and find the "1's", loading those cell objects into a collection, and then using the second loop to cycle through that collection to delet those rows that have been flagged. The first loop is almost instantaneous on all machines, while the second loop is the one that is experiencing the "irregular performance." When I step it through, its the actual "delete" line thats having the issue.

Lostit, when Shg (& Bullen) say "sort the data by that column", they mean using Excel's inbuilt ability via the Data - Sort menu option. Is this what you have tried?

3)

Originally Posted by lostit

... the formulas in the cells are mostly DBRW formulas linking to Cognos TM1, and the data is not changing, so it appears that I'm not actually getting any new data, or calculations in those cells, while the process is running. ...

Originally Posted by lostit

... If I were to break the link to the database, which represents the majority of cells in questions (approx 90%) then the speed will pick up to the 2-3 minute range but defeat the purpose of the model. ...

Is your spreadsheet designed nicely in that the formulae are able to be copied down or across (or both)?
If so, & the data isn't meant to change when the macro is running. Are you open to the idea of saving a row (or column - based on layout) of formulae above the headers (or in a column on the side)?
This would allow you to convert all your data to values for the duration of the Deletion macro & then recopy down (&/or across) the formulae to recreate the links for the necessary number of cells (thereby recreating model functionality).

4) I haven't used Cognos for about 5 years & I'm not sure if it was Cognos "TM1" (+ I realise I may look like a clown here! ) but... would it be possible to remove the rows on the Cognos side using parameters/query restrictions before drawing the data into Excel?

5) As Romper suggested, have you set up the file with:
(I couldn't see any confirmation & apologise if I have overlooked it within the thread)
-> View - Zoom, set at 100%?
-> View - Normal, selected?

6) A long shot...
Does ticking Tools - Options - General - "Ignore other applications" help?
(I'm not sure of the potential wider impacts - try on a copy of your file & remember to change it back)

7) This shows off my current ignorance of Cognos, so I'm clutching straws straight from the manual!

In most circumstances, any edits you apply to TM1 cubes are immediately written to the RAM of the TM1 server containing the cube. Each time a value in RAM is updated, a lock is placed on the TM1 server and any views stored in memory affected by the updated value are deleted, which is detrimental to TM1 performance.

Sourced from: http://download.boulder.ibm.com/ibmd...s_guide_en.pdf
Are the links in your spreadsheet just pulling from Cognos, or are some trying to "push" back to the TM1 server (ie being "written to the RAM of the TM1 server")?


Rob