# 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.

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
• >>>>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```

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
• 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 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
• > 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
• .>>>>>.. 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
• 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
• Helped me like anything... Thanks
Wednesday, June 29, 2016 12:56 PM
• Did you try WHERE A.IsActive <> 0?

A Fan of SSIS, SSRS and SSAS

Wednesday, June 29, 2016 1:18 PM

https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/

You are still writing assembly language and SQL. Perhaps this why you failed to post DDL? Do you know about the coalesce () operator is now standard SQL and has been for a decade? You are doing a bad thing and you are doing it badly.

Instead of the assembly language flag, we use a something_status which has a time dimension to it. A status is a state of being, so we have to note when the state started, and when the state ends. You can use constraints to assure that there is only one active state in the schema; your flags are a little bit harder to assure data integrity. But since we did not have any DDL, perhaps you can have more than one active state in the referencing table.

We can give you a quick kludge (get rid of the null option in your flag and use zero and one), or we can do it right. Which do you want?

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Thursday, June 30, 2016 12:54 AM