Which function is faster Abs or IIf?

Question

I ran the following test to determine the difference in efficiency between IIf and Abs:

Public Sub TestSpeed()
    Dim i As Long
    Dim res As Integer

    Debug.Print "*** IIF ***"
    Debug.Print Format(Now, "HH:mm:ss")
    For i = 1 To 999999999
        res = IIf(-1 = True, 1, 0)
    Next
    Debug.Print Format(Now, "HH:mm:ss")

    Debug.Print "*** ABS **"
    Debug.Print Format(Now, "HH:mm:ss")
    For i = 1 To 999999999
        res = Abs(-1)
    Next
    Debug.Print Format(Now, "HH:mm:ss")

End Sub

The results show that Abs is about 12 times faster:

TestSpeed
*** IIF ***
15:59:08
16:01:26
*** ABS **
16:01:26
16:01:37

Can anyone support this or prove that the contrary is true?

EDIT:

One situation in which one may need to decide between the two functions is for doing multiple counts based on criteria in an SQL query such as:

SELECT Sum(Abs(Colour = 'Yellow')) AS CountOfYellowItems, Sum(Abs(Votes>3) AS CountOfMoreThanThreeVotes FROM tblItems

versus

SELECT Sum(IIf(Colour = 'Yellow' ,1 ,0)) AS CountOfYellowItems, Sum(IIf(Votes > 3 ,1 ,0) AS CountOfMoreThanThreeVotes FROM tblItems

http://stackoverflow.com/q/13050731/11683, but I would just use the more readable option. — GSerg, May 25 '14 at 13:18
@GSerg That question has to do with `IIf` versus `If`. The question here is regarding `Abs` — E Mett, May 25 '14 at 13:21
`IIf` and `If` are comparable, `IIf` and `Abs` are not. If your logic is "calculate absolute value of a number," then use `Abs`, otherwise use `IIf` or `If`. In any case the link explains what `IIf` actually does, which gives a hint on the performance. — GSerg, May 25 '14 at 13:41
The only really important question here is "is it fast enough for you/your application/your customer". If it is then leave it. If it's not, then you'll hopefully have found a more specific question to ask. — ClickRick, May 25 '14 at 13:45
For those who might be interested, this question is a follow-up to my answer to an earlier question [here](http://stackoverflow.com/a/23854722/2144390). — Gord Thompson, May 25 '14 at 13:49
@GSerg There are cases when either can be used, e.g. doing a mutiple counts in a single SQL query where each count uses different criteria. — E Mett, May 25 '14 at 13:49
@ClickRick In order to develop scalable solutions it is important to use most efficient method in the first place. Your customer may not feel the difference when he has a couple of thousand of records, but a number of years later when he has a couple of hundred thousand records the slowdown might be causing him to be pulling his hair out and cursing out the stupid programmer! — E Mett, May 25 '14 at 15:42
@EMett In that case I'd be asking questions about the SQL engine or the use of VBA. But yes, in general, your test as stated will reproducibly yield the same results as you had. Don't forget to factor out the cost of the loop itself with a third "nul" test, which simply assigns 1 each time - that might actually increase the apparent improvement of `Abs` over `IIf`. — ClickRick, May 25 '14 at 15:48

score 1 · Answer 1 · edited May 23 '17 at 12:11

I ran a similar test that supports your findings:

Option Compare Database
Option Explicit

Public Sub SpeedTest()
    Const LoopLimit = 99999999
    Dim Tasked(LoopLimit) As Boolean, i As Long
    Dim Total As Long, t0 As Single, Elapsed As Single

    For i = 0 To LoopLimit
        Tasked(i) = False
    Next

    Debug.Print "*** IIF ***"
    Total = 0
    t0 = Timer
    For i = 0 To LoopLimit
        Total = Total + IIf(Tasked(i) = True, 1, 0)
    Next
    Elapsed = Timer - t0
    Debug.Print "Elapsed time: " & Format(Elapsed, "0.0") & " seconds."
    Debug.Print "Average time: " & Format(Elapsed / (LoopLimit + 1) * 1000000000, "0") & " nanoseconds."

    Debug.Print "*** ABS ***"
    Total = 0
    t0 = Timer
    For i = 0 To LoopLimit
        Total = Total + Abs(Tasked(i))
    Next
    Elapsed = Timer - t0
    Debug.Print "Elapsed time: " & Format(Elapsed, "0.0") & " seconds."
    Debug.Print "Average time: " & Format(Elapsed / (LoopLimit + 1) * 1000000000, "0") & " nanoseconds."
End Sub

resulting in

*** IIF ***
Elapsed time: 19.0 seconds.
Average time: 190 nanoseconds.
*** ABS ***
Elapsed time: 2.4 seconds.
Average time: 24 nanoseconds.

In terms of raw execution speed, Abs(BooleanValue) appears to be an order of magnitude faster than IIf(BooleanValue = True, 1, 0).

Whether or not that difference has a significant impact on the overall performance of the code in which the functions may be used depends very much on the context, as illustrated here.

score 0 · Answer 2 · answered Nov 18 '22 at 13:00

I have had a look at this in SQL for comparison.

DECLARE @i As BIGINT = 1
DECLARE @res As INT
declare @v BIGINT = -1

Print '*** ABS **'
DECLARE @s2 datetime = GETDATE()
Print Format(@s2, 'HH:mm:ss')

SET @i = 1
WHILE @i < 9999999
begin
    SET @res = Abs(@v)
    SET @i = @i + 1
end

DECLARE @e2 datetime = GETDATE()
Print Format(@e2, 'HH:mm:ss')
Print DATEDIFF(MILLISECOND,  @s2,@e2)

DECLARE @i As BIGINT = 1
DECLARE @res As INT
declare @v INT = -1



Print '*** IIF **'
DECLARE @s1 datetime = GETDATE()
Print Format(@s1, 'HH:mm:ss')
SET @i = 1

WHILE @i < 9999999
begin
    SET @res = IIf(@v < 0 , @v*-1, @v)
    SET @i = @i + 1
END

DECLARE @e1 datetime = GETDATE()
Print Format(@e1, 'HH:mm:ss')
Print DATEDIFF(MILLISECOND,  @s1,@e1)

You can't run both statements together to get a fair comparison. I think there is almost no difference. One case where IIF is better is where @v BIGINT = -2147483648 (or greater), as ABS would just fail with an overflow.

Which function is faster Abs or IIf?

2 Answers2

Linked