Remotely Run Betting Assistant

[greenfingers]

I'm taking the plunge! But I've blundered at the first hurdle ........I haven't completed the order yet!

Windows 2008 R2 Standard (64bit)
Windows 2008 Standard Edition (32bit)
Windows 2008 Standard Edition (64bit)
Windows 2003 Standard (32bit)
Windows 2003 Standard Edition (64bit)

Which is best for BA

[jokerjoe]

Prob won't make too much difference, maybe try 2008 R2 since it's the most recent.

[GaryRussell]

Although BA works on 64 bit windows it's not specifically designed to take advantage of a 64 bit OS. In my opinion Windows 2003 Standard (32 bit) is sufficient. We also haven't done any benchmarks so I don't have a definitive answer. The main advantage you will be getting though is faster response times which far outways any other factor.

[greenfingers]

Cool, thanks Joe.

[greenfingers]

Ah, thanks Gary. Think I'll have to go with the developer on this one, sorry Joe! Thanks both

[osknows]

This reminds me of 2 similar things I've heard of which may be related

1. There's a massive discussion on some boards about the benefits of 64bit flash, many people believe flash performance will improve once support for 64bit is eventually released; many developers/experts say it's nonsense and there won't be much noticable benefit..

2. City trading companies spent millions upgrading their servers/systems for speed then ending up moving their servers nearer source for the race for quicker connections

[greenfingers]

I've got the VPS up and running, however I'm pretty dismayed to see the CPU maxing-out at 100% most of the time when I have a market refreshing at 0.2 seconds. On my little ol' laptop I can run my system at about 65% CPU on average.

I'm using Excel 2007 which I've read is very inefficient compared to 2003 but even so surely the VPS should outperform a laptop?????!!!

Any technical gurus out there know what's goin' on? I've emailed support at the VPS company but not sure who to ask about this. Any help much appreciated.........

[jokerjoe]

Most VPSs are designed for web hosting and at the lower end of the scale don't have many resources compared to what you might have at home. What specs have you got on it (CPU/RAM)?

Also is your code as efficient as it could be? Do you need to refresh at 0.2 seconds? What's the lowest you can refresh without maxing-out the CPU?

Regarding 64-bit, granted there aren't any performance benefits as yet unless you need the extra memory. However I think we're at the point where it's beginning to make sense to start switching over as future Windows releases are going exclusively that way and increasingly programs are being recompiled for 64-bit.

[greenfingers]

I see. They don't say the spec of the CPU, just that its a host machine with eight Xeon cores. The RAM is 512mb and there's 206mb available.

The CPU usage drops 30% when the refresh rate is 0.3 seconds instead of 0.2. I can feel a request for a 0.25 second refresh rate coming on!

I was surprised by the performance and thought there may be something not configured correctly, or a magic patch or something! But if you think it sounds about right then I'll look into the other areas like improving the code and maybe I'll buy Excel 2003. Cheers.

[Lewis]

Just to make sure I am not being a complete idiot, how are you measuring the performance of Available RAM / CPU usage. I was looking at Administrative tools -> Performance. Is there anything else I should be looking at?

[GaryRussell]

I've got the VPS up and running, however I'm pretty dismayed to see the CPU maxing-out at 100% most of the time when I have a market refreshing at 0.2 seconds. On my little ol' laptop I can run my system at about 65% CPU on average.

I'm using Excel 2007 which I've read is very inefficient compared to 2003 but even so surely the VPS should outperform a laptop?????!!!

Any technical gurus out there know what's goin' on? I've emailed support at the VPS company but not sure who to ask about this. Any help much appreciated.........

Is BA struggling on it's own when not linked to the spreadsheet? What's the CPU when not linked to Excel and how many markets are are you displaying at a time?

[greenfingers]

Is BA struggling on it's own when not linked to the spreadsheet? What's the CPU when not linked to Excel and how many markets are are you displaying at a time?

No, BA runs between 10% and 25% CPU when refreshing at 0.2 secs not linked to Excel. This is with 4 tabs on the same market, no other tabs, as is the case when it is linked to the spreadsheet.

[GaryRussell]

Is BA struggling on it's own when not linked to the spreadsheet? What's the CPU when not linked to Excel and how many markets are are you displaying at a time?

No, BA runs between 10% and 25% CPU when refreshing at 0.2 secs not linked to Excel. This is with 4 tabs on the same market, no other tabs, as is the case when it is linked to the spreadsheet.

It uses about the same CPU on our server. Excel uses 10-14% so total is 39%. If your spreadsheet does not contain intensive VBA or formulas then it sounds like your Excel is misbehaving.

[jokerjoe]

Four tabs on the same market? Could you structure your code to just use one tab?

I'm not sure if there is a massive difference between Excel 2003 and 2007 (or even 2010 now). I'd suggest going over your code and seeing if there are places that could be slowing it down. Typically reading and writing to the worksheets is a big bottleneck, best to do once by reading a range into an array and vice versa. Also maybe you can change any For loops to Do loops to cut back on unnecessary calculations. Searching for "optimize vba" brings up quite a few little things you can do.

[greenfingers]

It uses about the same CPU on our server. Excel uses 10-14% so total is 39%. If your spreadsheet does not contain intensive VBA or formulas then it sounds like your Excel is misbehaving.

Four tabs on the same market? Could you structure your code to just use one tab?

I'm not sure if there is a massive difference between Excel 2003 and 2007 (or even 2010 now). I'd suggest going over your code and seeing if there are places that could be slowing it down. Typically reading and writing to the worksheets is a big bottleneck, best to do once by reading a range into an array and vice versa. Also maybe you can change any For loops to Do loops to cut back on unnecessary calculations. Searching for "optimize vba" brings up quite a few little things you can do.

Thanks guys. I have reinstalled Excel and not done installed any updates as Gary suspects that one of the updates is causing the other problem I had where one of the tabs wasn't moving on to the next race. Indeed, this problem didn't occur again last night.

The reinstall seems to have helped with the CPU usage a lot too. I can't be sure how much though because it's inconsistent and I have removed some code that was calling an Application.WorksheetFunction which I've read is quite inefficient.

Right now I'm testing it running at 0.2 refresh with an unmatched bet in the market and the CPU is only 80%. However last night I left it running for the last hour's racing and screen-recorded it (from my computer so not using any VPS CPU for the video) and whilst the the VPS CPU started off much lower at 65% (6 runner race), on the next race it hovered at 90% (9 runner race) and on the last (12 runners) did still hit 100% occasionally.

So long as it's not flatlining at 100% all the time I'm okay for the minute and I'll work on improving the code.

Following a tip I read, I have the around my code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculation.Manual
Application.EnableEvents = False

.....code.....

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Joe, does this get around the bottleneck caused by reading and writing to the sheets from VBA or would it be even better to rewrite my code to use an array as you suggest?

To answer Gary's question, the VBA code is really minimal- 20 lines excluding the mandatory stuff. The sheets have 13 columns of calculations. 2 use the VLOOKUP function which I'm guessing could be CPU intensive, I have MATCH in 2 others, the rest are arithmetic except the trigger cells which have a 7-level nested IF statement (I believe that's the limit!).

Would it run more efficiently if I re-wrote this in VBA (assuming I do so efficiently - I'm reading a book now(!) and there's always great help on the forum..........!)?????

Thanks for your help guys.