none
Performance issue with ISNULL function

    Question

  • Hi all,

    I have a case that cause performance issue as example below:

    Table A:
    Col1(PK)---Col2---IsActive

    1------------C1-----1

    2------------C2-----0
    Table B:
    Col1B(PK)---Col2B---Col3B(FK from table A)

    1B-------------C1-----1

    2B-------------C2-----NULL

    When I use LEFT JOIN:

    select B.* from table B left join table A on B.Col3B = A.Col1 where isnull(A.IsActive, 1) = 1

    That means I need to get data in table B where A.IsActive = 1 or NULL.The above query have a problem with ISNULL function because with this function, we can not use the INDEX and when we have a BIG data, the performance is very BAD.

    Is there any solution for this?

    Thanks all

    Wednesday, June 26, 2013 6:59 AM

All replies

  • Try the below:

    select B.* from table B left join table A on B.Col3B = A.Col1 where A.IsActive = 1 Or A.ISActive is null

    However, please provide indexes on the tables and the execution plan for the query to suggest more on the same.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, June 26, 2013 7:01 AM
  • Is there any solution for this?

    You already wrote the reason and solution; avoiding a function on a column in a WHERE clause:

    select B.* 
    from table B left join table A on B.Col3B = A.Col1 
    where (A.IsActive = 1 OR A.IsActive IS NULL)


    Olaf Helper

    Blog Xing

    Wednesday, June 26, 2013 7:01 AM
  • >>>>That means I need to get data in table B where A.IsActive = 1 or NULL.

    You do not have isactive is null but isactive =0

    this one does not return what you need?

    select B.* from table B left join table A on B.Col3B = A.Col1 

    ----

    create table #t (id int,isact int)
    create table #t1 (id int)

    insert into #t values (1,1)
    insert into #t values (2,0)

    insert into #t1 values (1)
    insert into #t1 values (null)


    select #t1.* from  #t1
     left join  #t on #t1.id = #t.id 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, June 26, 2013 7:08 AM
    Answerer
  • >>>>That means I need to get data in table B where A.IsActive = 1 or NULL.

    You do not have isactive is null but isactive =0

    this one does not return what you need?

    select B.* from table B left join table A on B.Col3B = A.Col1 

    ----

    create table #t (id int,isact int)
    create table #t1 (id int)

    insert into #t values (1,1)
    insert into #t values (2,0)

    insert into #t1 values (1)
    insert into #t1 values (null)


    select #t1.* from  #t1
     left join  #t on #t1.id = #t.id 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Hi Uri, it looks like OP is looking for 1 or NULL value from #t table. If thats the case, we need to specify the conditions right? Please correct me if am wrong?

    create table #t (id int,isact int)
    create table #t1 (id int)
    
    insert into #t values (1,1)
    insert into #t values (2,0)
    insert into #t values (1,NULL)
    insert into #t values (1,0)
    
    insert into #t1 values (1)
    
    select #t.* from  #t1
     left join  #t on #t1.id = #t.id 
     --where #t.isact = 1 Or #t.isact is null
     
    Drop table #t,#t1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, June 26, 2013 7:14 AM
  • Flag columns are usually performance killers since there is not much selectivity to prevent full table scan.

    Is this a reporting query? Run it in the reporting database?  Schedule it at night?   Put the non-active rows(records) into a separate table?

    Can you run a GROUP BY COUNT on IsActive and post results?

    Covering index maybe an option:

    http://www.sqlusa.com/bestpractices/coveringindex/

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Wednesday, June 26, 2013 7:55 AM
    Moderator
  • A condition on a where clause (or a join) that checks if a column IS NULL is likely to trigger a full table scan (if your column is not indexed and depending on which version of SQL server are you running). It does not mater is you use the ISNULL() function, the COALESCE() function, or [column name] IS NULL.

    My suggestion is to avoid the use of NULLs in flag columns. If you cannot set your IsActive flag to False (preferaly 0) for NULL values, you might want to consider that logic does not necessarily have to be binary (true or false, 1 or 0), you can have ternary logic (1 = true, 0 = False, 9 = something else like NULL).

    Kind regards,

    M


    • Edited by M Vega Wednesday, June 26, 2013 8:11 AM Minor edit
    Wednesday, June 26, 2013 8:10 AM
  • Why not force a default value on the column? Since this is a boolean flag.

    CREATE TABLE {TABLENAME} (
    {COLUMNNAME} {TYPE} {NOT NULL} 
    CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE})


    Wednesday, June 26, 2013 10:00 AM
  • Try This?

    select  distinct b.* 
    from
    #test1 a left outer join #test2 b
    on (a.col1=b.col3b or b.col3b is NULL)

    both a.col1 and b.col3b(join columns) should have same data type.

    Thanks..

    Wednesday, June 26, 2013 10:32 AM
  • The problem is I use LEFT JOIN, so I can't use default value in this case.
    Both 'ISNULL' and 'OR' in filter conditions are slow.
    Thanks all
    Wednesday, June 26, 2013 10:35 AM
  • Unfortunately your problem is due to a crappy schema. 

    There might be schema changes you can make that will improve things - but if you are not allowed to do that there is no point in discussing them.  It would also help to know what "big data" and "very BAD" actually mean in terms of real quantities.  Without further useful information (ddl, the actual query, version of sql server, etc.), it looks like there is no solution.

    Wednesday, June 26, 2013 1:38 PM
  • I am using MS SQL server 2008R2 - enterprise edition and the main table have around 100 million records. If I use 'ISNULL' or 'OR', it take me around 6 hours. but if I remove this logic, it just take me 30 minutes.
    Thursday, June 27, 2013 3:15 AM
  • How much data does the select return?  Perhaps creating an indexed view may speedup the query

    http://technet.microsoft.com/en-us/library/cc917715.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, June 27, 2013 6:25 AM
    Answerer
  • > Perhaps creating an indexed view may speedup the query

    .... and slow down INSERTs.

    Possibilities: covering index or partitioning the table.

    You need to decide what are the business critical queries.

    Post code & DDL for further assistance.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Thursday, June 27, 2013 8:57 AM
    Moderator
  • .>>>>>.. and slow down INSERTs.

    The OP did say nothing about how the table is populated... Perhaps it is located on read only file group....


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, June 27, 2013 10:52 AM
    Answerer
  • You may enjoy this process of asking a thousand questions and answering bits of each one at a time, but it will not move you closer to a solution in a timely fashion.  Do your own statistical analysis - how many different values are found in the IsActive column and what is the distribution for each?  Do you need the isnull / OR logic?  How many rows are you selecting?  What is consuming the dataset? 

    The simple solution is to remove the nullability of the column, replacing it with a default. 

    Thursday, June 27, 2013 12:58 PM
  • Any progress?

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Wednesday, July 03, 2013 4:13 PM
    Moderator