VBA实现Box Muller、Ziggurat、均匀比值算法的性能疑问

Hey there, let's break down your ratio uniforms algorithm performance issue and figure out why your results don't match theoretical expectations. First, let's recap your work to align:

You've implemented three standard normal random number generators in VBA—Box-Muller, Ziggurat, and the ratio uniforms method—all validated via the Shapiro-Wilk test to produce proper normal distributions. But when testing performance generating 10⁷ samples, you saw Box-Muller take 3.7 seconds, Ziggurat 1.28 seconds, and ratio uniforms a surprising 10.77 seconds—contradicting sources that say ratio uniforms should be 3-4x faster than Box-Muller.

Let's dig into your code and fix the bottlenecks.

I spotted a few critical inefficiencies dragging down performance:

• Unnecessary Inner Loop: Your inner Do...Loop Until u1 <> 0 is completely redundant. VBA's Rnd function returns values in the range (0,1]—it never actually returns 0. This loop runs extra iterations for no reason, wasting cycles.
• Expensive Condition Check: The line If u1 ^ 2 <= exp(-1 / 2 * u2 ^ 2 / u1 ^ 2) Then is computationally heavy. Exponentiation and exp calls are slow in VBA, and you're doing multiple redundant calculations here. We can rewrite this condition using logarithms to eliminate the costly exp call and reduce repeated math.
• Recomputed Constant: The value (2 * exp(-1)) ^ (1 / 2) is a fixed mathematical constant, but you're recalculating it every single iteration. Precomputing this once will save millions of unnecessary operations.

Here's a revised version of your code addressing all these issues:

Sub OptimizedRatioUniforms()
    Dim x() As Double
    Dim passing As Long
    Dim amount As Long: amount = 10000000
    Dim u1 As Double, u2 As Double, v2 As Double
    Dim u1Sq As Double, logU1 As Double, leftSide As Double
    Const SQRT_2_OVER_E As Double = 0.857763884960707 ' Precomputed sqrt(2/e)
    
    ' Resize array once to avoid repeated reallocations
    ReDim x(1 To amount)
    
    Do While passing < amount
        u1 = Rnd
        v2 = Rnd
        u2 = (2 * v2 - 1) * SQRT_2_OVER_E
        
        u1Sq = u1 * u1
        logU1 = Log(u1)
        leftSide = -4 * u1Sq * logU1
        
        ' Simplified condition using logarithmic rearrangement
        If u2 * u2 <= leftSide Then
            passing = passing + 1
            x(passing) = u2 / u1
        End If
    Loop
End Sub

What Changed? #

• Removed redundant inner loop: No more checking for u1 = 0 since Rnd can't return 0.
• Precomputed the constant: SQRT_2_OVER_E is calculated once outside the loop instead of 10 million times.
• Rearranged the condition: By taking the natural log of both sides of the original inequality, we eliminated the exp call and reduced repeated calculations. The new condition uses simple multiplications and a single log call per iteration, which is much faster in VBA.
• Reduced redundant math: Calculated u1^2 and Log(u1) once per iteration instead of multiple times.

To squeeze even more speed out of your code:

• Enable compiler optimizations: In the VBA Editor, go to Tools > Options > Editor and uncheck "Compile on demand". Then go to Tools > Options > General and check "Optimize code" (if available). This lets the compiler optimize your code before running it.
• Avoid dynamic array resizing: We resized the array once at the start instead of letting it grow incrementally, which saves overhead.
• Consider a faster PRNG: VBA's built-in Rnd is convenient but not the fastest. If you need even better performance, you could implement a Mersenne Twister PRNG in VBA (though this adds some complexity).

The ratio uniforms algorithm does have a lower theoretical cost per accepted sample than Box-Muller, but VBA's handling of floating-point operations—especially slow functions like exp and repeated exponentiation—can skew real-world results. Your original code's unnecessary loop and expensive condition check were the main reasons it ran so much slower than expected. With the optimized version, you should see a significant speedup, likely bringing it in line with or even faster than your Box-Muller implementation.

If you still don't see the expected performance after these changes, double-check that your Box-Muller implementation isn't already optimized (e.g., using the polar form which avoids rejection for half the samples) — that could be another factor in the gap.

内容的提问来源于stack exchange，提问作者J.schmidt