Compare a null bit column

0

I know the result of the following queries are the same, but I would like to know if there is a difference in execution by SqlServer in performance issues and etc.

SELECT * FROM CLIENTES C WHERE C.FL_EXCLUIDO IS NULL OR C.FL_EXCLUIDO = 0

or

SELECT * FROM CLIENTES C WHERE ISNULL(C.FL_EXCLUIDO, 0) = 0
    
asked by anonymous 13.02.2017 / 20:41

2 answers

0

I've already seen some things about this, there's even a very interesting post in the English OS: link

But in the link example, you are comparing a text field, and in your case it is a bit field.

So I decided to do a test with a table here. Enabling Exec Plan, I ran a query similar to yours, using a bit % field, and in two tests, one with a table with a few hundred records and another with a few thousand records, the result was the same : null required the creation of an index for the tables. The IS NULL function has executed without requesting an index.

As for the runtime, in the table with about 50 thousand records there was no relevant difference, as in a table with a few million records, which was as follows:

ISNULL : 01:21 (1m2s) < IS NULL : 01:19 (1m19s)

Here is the result of Exec Plan to analyze:

This may lead to the conclusion that ISNULL() would theoretically perform faster, as it suggested setting up an index, but this may not be true. To conclude, I decided to create the index and retry the runtimes, which were:

IS NULL : 00:08 (8s)
IS NULL : 00:03 (3s)

With this we can see that in the case of a ISNULL() nullable field, bit had the same performance without index, and with index was much higher, it took 37.5% of the time of ISNULL , that is , less than half, much faster.
Of course, other scenarios and a IS NULL clause may have different results, and it is always recommend to examine the Exec Plain to determine the best query.

    
14.02.2017 / 11:55
0

Rafael, there are several factors that determine the performance of a SELECT construct. Immediately, some comments on the two constructions that published in this topic, which, to facilitate the comments, I referenced as

(a) SELECT * FROM CLIENTS C WHERE C.FL_EXCLUDED IS NULL OR C.FL_EXCLUDED = 0;

(b) SELECT * FROM CUSTOMERS C WHERE ISNULL (C.FL_EXCLUDED, 0) = 0;

(1) In construction (a), the WHERE clause contains the logical OR operator. Usually, restrictions of type

WHERE expressão_lógica1 OR expressão_lógica2 OR ...   

force a sequential scan, either through index or directly into table . There are exceptions.

(2) In construction (b) the WHERE clause contains function call having table column as parameter. This usually prevents SQL Server from finding the appropriate index (if it exists) because the restriction becomes non- sargable , ( index scan ) or directly in the table ( table scan ). There are exceptions here too.

(3) Another factor that can influence performance is what contains the column_list of the SELECT clause. Constructs of type

SELECT *

may make the choice of nonclustered index unfeasible, especially if it is not coverage. This involves including the presence or not of the Key Lookup ( Key Lookup ) in the execution plan.

(4) Assuming the purpose of the query is to get only the customer ID, then we could have

(c)
SELECT idCliente FROM CLIENTES C WHERE C.FL_EXCLUIDO IS NULL OR C.FL_EXCLUIDO = 'false';

  • But the construction

    (d)
    SELECT idCliente FROM CLIENTES C WHERE C.FL_EXCLUIDO IS NULL     
    union all      
    SELECT idCliente FROM CLIENTES C WHERE C.FL_EXCLUIDO = 'false';
    

    can be more efficient than construction (c) if there are filtered indexes:

    CREATE nonclustered INDEX I3_CLIENTES on CLIENTES(FL_EXCLUIDO) where FL_EXCLUIDO = 'false';     
    CREATE nonclustered INDEX I4_CLIENTES on CLIENTES(FL_EXCLUIDO) where FL_EXCLUIDO is null;
    

    Of course it also depends on the volume of data.

    (5) I could list a number of other factors here. However, in constructions (a) and (b) it seems to me that sequential scanning will always occur ( scan ).

    (6) The valid values for the FL_EXCLUDED column are 'true' and 'false', plus no information (NULL). That is, construction (a) could be rewritten as

    (e)
    SELECT * from CLIENTES
    except
    SELECT * from CLIENTES where FL_EXCLUIDO = 'true';
    

    The efficiency (or otherwise) of this solution depends on the characteristics of the FL_EXCLUDED column and the existing indexes.

        
  • 15.02.2017 / 23:02