SQLite `ifnull()` vs `coalesce()` performance

Question

The ifnull() function is equivalent to coalesce() with two arguments.

Then, if I have two arguments, which one is faster?

If they are equivalent, then they should have the same performance. In other words, the only difference would be in the compile phase. — Gordon Linoff, Jul 24 '13 at 01:41

score 4 · Accepted Answer · answered Jul 24 '13 at 02:52

I have confirmed Gordon Linoff's statement with some experiments. Setup: SQLiteSpy on Windows 8, i5 and the whole table in RAM.

Either query took about 1.64 secs with very small fluctuations (less than .1 sec). Most of the time they were right on 1.64 secs.

Queries:

    create table t (
      a int, b int);

    insert into t values (null,null);
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;
    insert into t select case when random() < .5 then 1 end, case when random() < .5 then 1 end from t;

    select sum(ifnull(a, b)) from t;
    select sum(coalesce(a, b)) from t;
    select count(*) from t;

SQLite `ifnull()` vs `coalesce()` performance

1 Answers1