Is it slow to isnull check field?

Question

1.00/5 (1 vote)

See more:

I have an TSQL, Query in SQL Server by using ISNULL to convert null field to Left field value or 0.
Is it make performance to Slow?

Select
    R1=ISNULL(R1,0),
    R2=ISNULL(R2,ISNULL(R1,0)),
    R3=ISNULL(R3,ISNULL(R2,ISNULL(R1,0))),
    R4=ISNULL(R4,ISNULL(R3,ISNULL(R2,ISNULL(R1,0)))),
    R5=ISNULL(R5,ISNULL(R4,ISNULL(R3,ISNULL(R2,ISNULL(R1,0))))),
    R6=ISNULL(R6,ISNULL(R5,ISNULL(R4,ISNULL(R3,ISNULL(R2,ISNULL(R1,0))))))
From R

What I have tried:

any recommend or reference, please.

Posted 9-May-17 10:30am

PR1ST

Updated 10-May-17 3:10am

Add a Solution

2 solutions

Solution 1

What did you find when you ran the SQL Profiler on this and compared the results to other queries where you're not making 21 function calls per row?

Posted 9-May-17 15:34pm

Dave Kreskowiak

Comments

PR1ST 10-May-17 1:50am

This is a small test to compare: I use more than 400 ISNULL functions in each row.
Result in SQL Profile:
CUP Reads Writes Duration
With ISNULL 78 1752 3 77
Other 63 3841 15 73

I wonder, ISNULL(A, ISNULL(B,C)) will do only one time if A is not null or SQL will process all ISNULL functions?

Dave Kreskowiak 10-May-17 8:27am

You have to run it multiple times and over a dataset comparable to what you're going to get in production. But, based on this, yes, it's going to be slower. FOUR HUNDRED calls to ISNULL!?!?! Are you insane?

If you think you need to do something like that, you really need to take a look at your database design and possibly massaging the data when it's written to the database to eliminate the need to do so much processing during a SELECT.

PR1ST 10-May-17 16:28pm

If total in really process should be have around 1,000 ISNULL use every single row.

This is the problem
R1    R2   R3
---------------
100    
      200
            300
---------------
100 | 200 | 300
What we want 
R1    R2   R3
---------------
100 | 100 | 100
      200 | 200
            300
---------------
100 | 300 | 600

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-05-10T03:10:00

You might find something like this a little faster

SQL

Select
    R1=ISNULL(R1,0),
    R2=COALESCE(R2,R1,0),
    R3=COALESCE(R3,R2,R1,0),
    R4=COALESCE(R4,R3,R2,R1,0),
    R5=COALESCE(R5,R4,R3,R2,R1,0),
    R6=COALESCE(R6,R5,R4,R3,R2,R1,0)
From R

See COALESCE (Transact-SQL) | Microsoft Docs[^]
On the whole though, I agree with Dave Kreskowiak - your database design and/or your data entry need some attention