WotLK DPS spreadsheet - Page 202

• VRoscioli · 07/09/09, 8:39 PM

Not that it should matter, since there isn't much use for Elixir of Demonslaying, but there is an error in the formula in Buffs!J113

Shandara · 07/10/09, 4:13 AM

Just as a heads up, I've decided to rework the gear planner to work like the Ranged weapons button for each slot (i.e. it actually tries the item). Trying to work the excel formulas to give accurate results with +hit and +haste in particular is giving me a headache really. Excel's formula based language isn't really suited to building in a lot of exceptions and nested IF statements.

The Gear Planner is really an optimization problem, which is far easier to do in scripts.

To make it better than the current "Ranged Weapons" button, it will try to do the following:
a) Re-gem +hit if you end up below the hit-cap
b) Re-gem for agility/AP/Crit (your choice on Settings tab) if you end up above.

For both cases it will try both 'pure' gems (i.e. +16 hit or +16 agility) and mixed gems (i.e. +8 agility/+8 hit gem).

The benefit is more accuracy, the downside is obviously that it means a big hit in performance. I will include options to calculate only for certain slots which are selectable. And it will only calculate for item locations actually selected.

Cranch · 07/10/09, 4:41 AM

I'd like to be able to do (a) and (b) above but without having to run at the speed of the Ranged weapons button. In other words, the current gear planner modified to do (a) and (b). I presume that'd run a lot faster. Hopefully that will be an option.

Shandara · 07/10/09, 7:12 AM

Originally Posted by Cranch

I'd like to be able to do (a) and (b) above but without having to run at the speed of the Ranged weapons button. In other words, the current gear planner modified to do (a) and (b). I presume that'd run a lot faster. Hopefully that will be an option.

I'm adding a button on the Gear tab to do exactly that. It doesn't have to recalculate the whole sheet while re-gemming so it's pretty fast.

When comparing items, after each item is equipped it does have to recalculate the dps, which is where the performance hit comes from.

• VRoscioli · 07/10/09, 10:51 AM

Originally Posted by Shandara

Just as a heads up, I've decided to rework the gear planner to work like the Ranged weapons button for each slot (i.e. it actually tries the item). Trying to work the excel formulas to give accurate results with +hit and +haste in particular is giving me a headache really. Excel's formula based language isn't really suited to building in a lot of exceptions and nested IF statements.

The Gear Planner is really an optimization problem, which is far easier to do in scripts.

To make it better than the current "Ranged Weapons" button, it will try to do the following:
a) Re-gem +hit if you end up below the hit-cap
b) Re-gem for agility/AP/Crit (your choice on Settings tab) if you end up above.

For both cases it will try both 'pure' gems (i.e. +16 hit or +16 agility) and mixed gems (i.e. +8 agility/+8 hit gem).

The benefit is more accuracy, the downside is obviously that it means a big hit in performance. I will include options to calculate only for certain slots which are selectable. And it will only calculate for item locations actually selected.

Will this be re-running the rotation test for each and every piece of gear now (assuming that option is turned on)? If so, won't this take roughly forever? Perhaps there should be a cutoff as to what gear it tries. (For example, the Ranged Weapon button appears to try *every* ranged weapon in the spreadsheet, including ones that don't even have a chance of being anywhere near what is currently equipped.) Maybe it could not even bother trying anything that is currently a 10% or more DPS loss or something.

Shandara · 07/10/09, 11:27 AM

Originally Posted by VRoscioli

Will this be re-running the rotation test for each and every piece of gear now (assuming that option is turned on)? If so, won't this take roughly forever? Perhaps there should be a cutoff as to what gear it tries. (For example, the Ranged Weapon button appears to try *every* ranged weapon in the spreadsheet, including ones that don't even have a chance of being anywhere near what is currently equipped.) Maybe it could not even bother trying anything that is currently a 10% or more DPS loss or something.

No, it doesn't run the test for each piece of item anymore, since the effect of a single piece of gear on the rotation is small (apart from haste affecting Steady Shot, no other stat does).

I'm taking this opportunity to try and profile the code a bit and see where it can be optimised.

As a comparison, on my computer (E8500 dual-core with 4gb RAM) the current 'Calculate Attributes' button takes roughly 30 seconds to compute stat values. After that the Gear Planner is pretty much instant, naturally.

The new code that tries an item and see the DPS effects takes about 500 ms per item to compute the DPS increase. The biggest part of this time, 360-370ms, is spent re-calculating the entire sheet (to get the DPS figure of the new item).

The more I can optimise that part spent calculation all the sheet, the faster it will be to compute an item's DPS value.

EDIT:
I've made some improvements to the speed that will be very noticeable. I hope to get the whole system done today and a test-version out for people to try.

Tsook · 07/10/09, 11:55 AM

One thing that could potentially speed up the optimizer part is if it only ran using items it knows you have (or have easy access to). Now that Rawr has finally mostly caught up to the features the Hunter spreadsheet has had for ages, one of the things it does better than the sheet is allow you to mark items that you have and then optimize the best way to use all of them together. Of course, that would require an entire new UI or unhiding the gear sheets to do the marking, so if you can get it fast enough it's probably better just to run on all items.

Maybe an ilevel/quality filter? I'm pretty damn sure no blue or anything with an ilevel of <=200 is going to be an upgrade for me (trinkets aside), so no reason for the sheet to check those.

Shandara · 07/10/09, 12:25 PM

Originally Posted by Tsook

One thing that could potentially speed up the optimizer part is if it only ran using items it knows you have (or have easy access to). Now that Rawr has finally mostly caught up to the features the Hunter spreadsheet has had for ages, one of the things it does better than the sheet is allow you to mark items that you have and then optimize the best way to use all of them together. Of course, that would require an entire new UI or unhiding the gear sheets to do the marking, so if you can get it fast enough it's probably better just to run on all items.

Maybe an ilevel/quality filter? I'm pretty damn sure no blue or anything with an ilevel of <=200 is going to be an upgrade for me (trinkets aside), so no reason for the sheet to check those.

You can deselect all items that you don't want by removing their drop locations.

Deselecting the following:
* Non-raid Gear
* Pre-WotLK

Should get rid of all the blues and Heroic Dungeon drops.

Bikiniwax · 07/10/09, 12:26 PM

Using version 90b of spreadsheet, a 7/57/5 build with 3/3 Imp. Hunter's Mark.

When I remove points from Imp. Hunter's Mark and recalculate the shot rotation and overall spreadsheet, I expect DPS to go down however it is not changing at all. Is this a bug or am I missing something here?

Shandara · 07/10/09, 12:45 PM

Originally Posted by Bikiniwax

Using version 90b of spreadsheet, a 7/57/5 build with 3/3 Imp. Hunter's Mark.

When I remove points from Imp. Hunter's Mark and recalculate the shot rotation and overall spreadsheet, I expect DPS to go down however it is not changing at all. Is this a bug or am I missing something here?

Do you have buffs enabled that give IHM Mark?

Haldhur · 07/10/09, 2:26 PM

Originally Posted by VRoscioli

Can you be more specific? What isn't working?

when i push the test rotation:

Shandara · 07/10/09, 2:35 PM

I had noticed your bug report, but I have been unable to reproduce it myself. I'm puzzled why it would error on that particular line.

EDIT:
I've uploaded v91beta here. Please try it out and let me know what you think or could be done differently.

For comparisons, on my computer (E8500 dual-core 3.16 ghz, 4gb RAM). The full check takes 160seconds for ~900 items. At 190ms per item. Naturally that includes many items that are obsolete.

If I select just 1 or 2 slots the check is done in 5-6 seconds.

The re-gemming feature on the Gear tab is also used by the Gear Planner when dealing with sockets/+hit on items. Let me know if you think certain gemming decisions it makes are bad.

Tsook · 07/10/09, 4:47 PM

Originally Posted by Shandara

You can deselect all items that you don't want by removing their drop locations.

Deselecting the following:
* Non-raid Gear
* Pre-WotLK

Should get rid of all the blues and Heroic Dungeon drops.

Yeah, you're right, of course. My example was bad. I should have said something along the lines of "I have Lotrafen but I don't have Twisted Visage, so it doesn't need to bother trying Twisted Visage."

But considering it now that I've had a chance to wake up, what I was thinking about is really a different use case than what the Gear Planner is for now, which is generally "planning upgrade paths," not necessarily "finding the optimal subset of gear given a set of gear as input." It's just this latest feature has got me thinking: if you've already coded up the ability to have the spreadsheet swap things around and re-gem appropriately, going that one step further to have it try multiple combinations could largely obviate the need for the "swap in this hit piece, swap out that other hit piece and replace it with a set piece to keep the bonus" kind of work we have to do quite often when evaluating potential upgrades.

Unless one gems for a majority of one's hit, the intelligent regemming you've implemented won't be able to really effectively deal with the kind of situation where you'd have to swap an entire other hit item out to make the item in question an upgrade.

Of course, the number of combinations has exponential growth with the number of items selected for an optimizer, so I wouldn't be surprised if you felt it was not likely to be possible in reasonable runtime using excel scripting.

Shandara · 07/10/09, 5:00 PM

While this would be possible to do, it wouldn't be, as you say, very fast.

I've pretty much reached the limit of how fast I can make Excel recalculate the sheet's DPS. Even with rigorous limiting of what cells/sheets get actually calculated (this gave a HUGE boost in speed in the v91beta), there are still a lot of formulas to go through on each change.

VBA is in itself fairly fast for a real-time interpreted scripting language, but it's the interfacing with Excel cells/formulas that incurs the big penalties.

jmichaelp · 07/11/09, 12:07 AM

Originally Posted by Iru

When you change talents are you changing specs or just moving points around in a tree? Newer versions of the spreadsheet default to rotation tests rather than distribution frequencies so changing specs means you MUST change the shot rotation to see true DPS, the spreadsheet will no longer guess for you.

Also, you don't have 'Randomise Procs on Rotation Test' set to yes by accident do you? While the rotation test approach is generally considered more accurate, the random procs method will result in different DPS numbers each time you run the test, even if you haven't changed anything.

Hi, thanks for the reply. I'm changing specs, so it seems that changing the rotation might be the problem. If the case is that I have to go change the shot rotation every time I change spec, then the spreadsheet becomes really unwieldy & I'll probably stop using it.

Hmmm... I don't see 'Randomise Procs on Rotation Test' anywhere lol so I probably didn't change it from it's original setting.

While I appreciate the work being done on this spreadsheet, it seems that missing out on developments in the past couple of versions may have left me behind the learning curve. The spreadsheet may be becoming too complicated & without guidance from documentation I don't know how I'll be able to use it any more.