I'm not sure whether this is an OS issue, hardware or a combination of both but.... I am running a macro to calculate and analyse stock data, involving a large number of large files and a complete run can take anywhere between 12-36 hours ( I understand there may be better solutions but I just stick with what I know....). I started with just one PC, an intel i7, however to speed things up, I started running on a second computer, an older Core 2Duo. (Same macro, different stocks) Now I assumed, that my newer i7 would get through the macro a lot quicker, however after running them side by side, I have found that the Core 2Duo is getting through the macro at least twice as fast as the i7( Itested using the same files on both to check). Is someone able to offer a reason for this huge gap in performance? Below are the basic specs for both pcs. Is older CPU better designed to handle this sort of work? Or something to do with the difference in OS? Intel Core 2Duo E8500 @ 3.16 , 4gb memory , 256gb SSD, OS - XP, 2010 office Intel i7 960 @3.2 ,6 gb memory, 256gb SSD, OS - Win 7, 2010 office Thanks in advance!
maninjapan - just as a side issue for using excel given your test takes a long time and a helpful hint of you dont have already use it..... Depending on how your macro works, if it involves a lot of dumping/writing of data to the sheets by cut and paste or similar - which usually takes up a lot of time - A handy hint I discovered of writing data to a matrix and then resizing it is much faster.
Thanks for the tip Siuya, I do indeed do a lot of opening / closing files, copying and pasting. I'm not familiar with matrix's or resizing. Do you have any links or something that might point me in the right direction? Thanks again!
there isn't enough information to clearly understand the workload so that one can give you an opinion... but based on what you stated about the Core2 Duo being faster, here is what I would do on the i7-960 in your bios... - disable threading - disable VT and VT-x - enable Execute Disable Bit - disable SpeedStep Technology - disable TurboBoost Technology I would also say to migrate to Excel 2013 ... you will see a considerable decrease on your processing time with 2013... I hope the above helps you... there are other settings that can be done, but those are mostly based on the server/workstation model..
ofthomas, thank you for the advice, I'm not familiar with these settings but will definitely look at them. Regarding the workload. I am optimizing an intraday-trading strategy, testing a range of parameters. The macro opens a file for a stock containing the strategy( 1 file per stock per day), updates the values and recalculates the formulas that make up the strategy, copies and pastes any trades to a results file, closes the file then goes through the rest of the stocks in the list for the period being tested. Rinse and repeat for each combination of the parameters.
It took me a little mucking around and experimentation for me to 'get' it, but ultimately managed to write a 3d array (using an array within an array) very quickly using this method. It was a year or so ago, and I have it somewhere but not easily at hand as its within a lot of code. However....this is a great source for this to start.(Depending on your vba writing levels) http://www.cpearson.com/excel/ArraysAndRanges.aspx http://www.avdf.com/apr98/art_ot003.html
ofthomas, how do the above 2 improve things for me. I only just looked them up but don't these work to improve performance when I need it? Cheers