DataRow.IsNull() Performance?

[Alric]

I have read in numerous places that developers should avoid the IsDBNull function of the DataReader object for performance reasons. Instead one should use an expression like "DataReader[ordinal]==System.DBNull.Value" to test for Null.

Does anyone know of similar performance issues with the DataRow.IsNull function? I hope to do some performance testing this weekend. I will post my results back here.

[Miha Markic [MVP C#]]

Hi Alric,

IsDBNull checks for both null and DBNull.Value and it is a function with a
parameter (which might be optimized by compiler) thus it could be a bit
slower.
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Post by Alric
I have read in numerous places that developers should avoid the IsDBNull

function of the DataReader object for performance reasons. Instead one
should use an expression like "DataReader[ordinal]==System.DBNull.Value" to
test for Null.

Post by Alric
Does anyone know of similar performance issues with the DataRow.IsNull

function? I hope to do some performance testing this weekend. I will post
my results back here.

[Cor Ligthert]

Hi Miha,

With how many readed records this becomes noticable do you think?

I think you mean something as it could be theoratical a bit slower. I do not
think that the time of a processor instruction can affect the real througput
time of reading data on a disk or whatever.

(I hope you do not see this as flame, because it is not however I get
sometimes the idea that people reading things as they want to see it, so
this is a kind of real arguing)

I never use IsDBNull by the way, I find the syntax not nice of that.

:-)

Cor

Post by Miha Markic [MVP C#]
IsDBNull checks for both null and DBNull.Value and it is a function with a
parameter (which might be optimized by compiler) thus it could be a bit
slower.

[Miha Markic [MVP C#]]

Hi Cor,

Post by Cor Ligthert
Hi Miha,
With how many readed records this becomes noticable do you think?

Infinite :-)

Post by Cor Ligthert
I think you mean something as it could be theoratical a bit slower. I do not
think that the time of a processor instruction can affect the real througput
time of reading data on a disk or whatever.

No, I do not consider it a performance hit at all. Afterall the main
performace bottleneck is reading, networking, etc.
Yes, it is just theoretical :-)

Post by Cor Ligthert
(I hope you do not see this as flame, because it is not however I get
sometimes the idea that people reading things as they want to see it, so
this is a kind of real arguing)
I never use IsDBNull by the way, I find the syntax not nice of that.

Me neither.

[Frans Bouma [C# MVP]]

Post by Alric
I have read in numerous places that developers should avoid the IsDBNull
function of the DataReader object for performance reasons. Instead one
should use an expression like "DataReader[ordinal]==System.DBNull.Value" to
test for Null.

correct, this saves you a significant amount of cycles. In a normal read
loop from a datareader, where you check every field in a row for null, you
can spend 10-15% of the time in IsDBNull. So just testing for the value
System.DBNull.Value can help speed up your application a lot. I didn't
believe it myself either until I did some serious profiling of some
datareader loops with IsDBNull.

I don't expect a significant speed increase with the DataRow equivalent,
because the IsDBNull() function is pretty intensive behind the scenes, the
IsNull routine returns a bitarray get result.

FB

[William Ryan eMVP]

Frans:

That's pretty interesting. I'm guessing the same applies for datatable
values? I actually have a good bit of code using IsDbNull that I probably
need to change.

As an aside, if you are in a read only scenario from the db (ie you won't
send back an Update) - is using IsNull() [assuming you are using SQL Server
or a db that supports it] on the server a decent alternative?

After reading everyone's posts on this, the IsDbNull thing definitely makes
sense - I guess I should have been hip to this earlier.

Thansk again,

Bill
--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

Post by Frans Bouma [C# MVP]

Post by Alric
I have read in numerous places that developers should avoid the IsDBNull
function of the DataReader object for performance reasons. Instead one
should use an expression like "DataReader[ordinal]==System.DBNull.Value" to
test for Null.

correct, this saves you a significant amount of cycles. In a normal read
loop from a datareader, where you check every field in a row for null, you
can spend 10-15% of the time in IsDBNull. So just testing for the value
System.DBNull.Value can help speed up your application a lot. I didn't
believe it myself either until I did some serious profiling of some
datareader loops with IsDBNull.
I don't expect a significant speed increase with the DataRow equivalent,
because the IsDBNull() function is pretty intensive behind the scenes, the
IsNull routine returns a bitarray get result.
FB
--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

[Frans Bouma [C# MVP]]

Post by William Ryan eMVP
That's pretty interesting. I'm guessing the same applies for datatable
values? I actually have a good bit of code using IsDbNull that I probably
need to change.

No it's just with datareader's IsDBNull. DataTable's IsNull is in fact the
DataRow's IsNull, which is just an in-memory check of a column value at a
given index. I found the slowness of IsDBNull after profiling my O/R mapper's
performance and notices a severe dip in performance in the datareader loop,
and hte profiler showed 15-20% of the time the loop was spending in
IsDBNull!. So I changed it to a compare with DBNull.Value and the routine
gained a significant amount of performance :)

Post by William Ryan eMVP
As an aside, if you are in a read only scenario from the db (ie you won't
send back an Update) - is using IsNull() [assuming you are using SQL Server
or a db that supports it] on the server a decent alternative?

Definitely, IF you don't have any logic working on NULL values. I mean: if
you read a set of rows and the logic working with the values has to display
something like "Not available" when a field is NULL (not defined), you should
read the NULL values as well.

I use default values for NULL values on the client. This means that the
field's NULL value is noted and availabel via a flag, but at the same time
you can use the field's value as it is a normal int for example, and it is
then for example 0.

FB