DataRow.IsNull() Performance?

[Guest]

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

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.

 

[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

 

[Miha Markic [MVP C#]]

Hi Cor,

Hi Miha,

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

Infinite

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

(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]]

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

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

 

[Frans Bouma [C# MVP]]

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.

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

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