Which is best processor for this type of EXCEL activity

[peck]

We have an EXCEL-based app (VBA) that allows a user to open an empty workbook, then pull data from different data sources. For each data object read, a new sheet is automatically created and the data loaded into it. Generally, for this type of exercise the user ends up with a few hundred sheets. However, for some applications tens of thousands are required (it is a product configuration environment).

Our problem is the speed at which new (completely blank) sheets are created. From the behaviour we observe, it is clear that sheet creation slows down as number of sheets grows.

We use 64-Bit Excel, on core i5 processors, with 20GB RAM. To test performance, we use the following VBA macro (which only generates blank sheets). It takes us over 2mins to run this the first time, then around 6mins to run it a second time. We suspect - from this behaviour - that the slowdown is software-related (possibly the 'sheet list' maintained internally is not indexed, bcos it is not expected that this many sheets will be required).

At this point, we are looking at a hardware upgrade to get better performance. Any advice will be appreciated.

Dim StartTime As Double, I As Long, J As Long
Sub NewSheets()
' Turn off UI responses
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveWindow.DisplayWorkbookTabs = False
' Add new sheets
I = Sheets.Count
J = I + 25000
StartTime = Timer
While I < J
Sheets.Add After:=Sheets(I)
I = I + 1
Wend
' Restore Excel settings to original state.
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveWindow.DisplayWorkbookTabs = True
' Msg to indicate duration
MsgBox ("Elapsed Time: " & Format(Timer - StartTime, "###0.00") & " secs")
Sheets(1).Activate
End Sub

[David (PassMark)]

You are completely nuts to use Excel and VBA for a performance critical application. It is like trying to fly to the moon in a shopping trolley.

If you switch to using C/C++ you should get at least 10x speed improvement, use a lot less RAM and get consistent performance from one run to the next.

But here are some past threads on Excel and picking a CPU
http://www.passmark.com/forum/showth...excel-workbook
http://www.passmark.com/forum/showth...-MS-Excel-2013

[peck]

Really! Did you just do that? Call me completely nuts on my 1st thread? Not nice, David. And that while you appear to be missing the point completely.

Firstly, this is not a performance critical application. We're merely pursuing reasonable levels of performance here.

Secondly, not everything is faster via C/C++. And I certainly doubt that there is a way to make the equivalent of Sheets.Add go faster (AND to not let it slowdown as the number of sheets grows). Let alone 10x faster. But if anyone can prove otherwise, then by all means: Call me nuts!

Thanks for the list of past threads on picking a CPU. If anybody has one of these uber machines, a few minutes to execute my (VBA) macro above and a minute to post the result, it would help me chose. And I would really appreciate it.

[David (PassMark)]

Call me completely nuts on my 1st thread?

Yes.

this is not a performance critical application

You wouldn't be asking the question is performance wasn't important. You could just use any old CPU and the task would finish when it finishes.

Secondly, not everything is faster via C/C++.

Yes it is, at least compared to VBA in Excel.
To claim anything else is, well..... nuts.

In case your are interested here is someone who did a similar experiment.
http://stackoverflow.com/questions/1...-arithmetic-op

C++ was way faster.

I certainly doubt that there is a way to make the equivalent of Sheets.Add go faster

You missed the point. I am suggesting you don't use Excel for your performance critical task. Don't load Excel, don't create Excel sheets. Just load the data in a standalone C/C++ app and process it in the app, then output CSV at the end (if need be).
What processing gets done in Excel, besides making empty sheets?

[peck]

It is an interactive process. EXCEL is the 'frontend'. The users starts with a blank workbook. The user then pulls in data objects from different data sources. Where the user pulls data from, the number of data objects pulled in, whether he requires more data objects halfway through AND the processing that is done is determined entirely by the user. The user has all of EXCELs functionality AND some bespoke functionality. It is an approach the works very well for us, for a number of non-technical reasons.

When the number of ojects being pulled in gets into the thousands, we see an unreasonable slowdown. We've tracked this slowdown to the sheet creation step. The more sheets there are in an EXCEL workbook, the slower 'Sheets.add' becomes.

Based on EXCEL's performance in general (and other complex tasks it completes at speed), we believe that it should not be slowing down like this on this particular task. Through our support channels, we are pursuing this with Microsoft.

For now though, we're looking at the kind of CPU & RAM configuration that will mitigate this to the best extent possible.

[David (PassMark)]

Without any details of what the data is, or what processing is done, it is hard to estimate the potential conversion work required to get it out of Excel. I still think using real code (and not a script) is the long term solution however. Microsoft aren't likely to update & optimise Excel just for your specific usage.

I didn't some quick profiling of your script. It is single threaded (as expected). So pick a CPU with high single threaded performance.

There wasn't much disk access while the script was running, so that is good.

There was a huge amount of queries to the Windows registry however. So that would use up some of the time. The registry would get cached into RAM, but it is still a lot of unnecessary overhead on the CPU. But this is out of your control while you are using Excel.

Only around 0.5GB of RAM was used. So more RAM probably isn't the solution. Only very rarely does RAM latency & bandwidth have a significant impact. So slightly faster RAM isn't going to solve the problem either.

On my i7-4770 with 16GB the first run took 101 seconds (Excel 2010). It appears to be CPU bound on 1 core. Most the cores remained 'parked'. So multiple CPUs isn't the solution either. And the i7-4770 is near enough to the fastest (single threaded) CPU available. So a higher end CPU isn't going to dramatically improve things. You might gain 10% - 20% with air overclocking however. Maybe 30% if you went liquid cooled overclocking.

With C/C++ you could thread the code to use all CPUs. But in Excel this is out of your control.

So it all comes back to Excel not being optimised for creating tens of thousands of tabs. i.e. it is the wrong tool for the job, if performance matters.

[peck]

This is great. We'll be looking at both: Higher processor for now. And, going forward, how we could convert. Appreciate the pointers to the different overheads & areas of activity as well.

Thank You, David.