Since I can't stand unvalidated performance claims, I looked up the reason behind IsDBNull performance

If you need to figure out a performance issue you can always just ask me via comments or post something as a question in some feedback.  I posted earlier this week that using SqlDataReader indexer methods were much faster if you used the ordinal.  The reasoning behind this is right in the blog posting, as you might expect, so you gain some deep insight as to why a method is faster versus another method, in our case the string overload.

Since the IsDBNull issue is getting so much traffic, I'll comment on exactly why comparing a return result from say dr[string] or dr[ordinal] against DBNull.Value is going to be faster.  The issue is in the underlying work being done.  Both methods are doing identical amounts of work, however one is wrapping things into Sql* objects and the other is wrapping things using .NET objects.  Let's see what happens in both case:

this[string] calls GetValue then PrepareRecord.  There are some buffer reads and finally a call to TdsParser.ProcessRow with a value of false for useSqlTypes.  In this case we now call TdsParser.ReadValue for the values.

IsDBNull calls PrepareSQLRecord (forget about that GetValue stuff ;-).  This method is the same as PrepeareRecord, except that it calls TdsParser.ProcessRow with true for useSqlTypes.  In this case TdsParser.ReadSqlValue is used.  ReadSqlValue contains a bunch of code that builds Sql* types after calling base Read* methods.  This extra level of creating another object and calling the constructor with the value that would have been returned directly had you used the first method makes things a bit slower.

The later process of creating Sql* objects really sucks.  Many of them, like the numeric values, actually call functions with a lot of overhead like IsInfinity and IsNaN (in the case of SqlDouble).  All of the types also store a boolean member field that returns whether the value is null or not.  You see each of these structs can be created using a value overload or a boolean overload indicating whether or not it is Null.

Future thinking would allow you to use IsDBNull in the near future.  You see things have changed a bit in Whidbey with the introduction of generics and some of the SQL libraries have changed to make use of nullable types.  I think with nullable types you won't notice the performance issues seen now.  I also think that much of the boxing currently present in the library will be removed.

References
IsDBNull() alternative
People are still using dr[stringName], when dr[intOrdinal] is soooooo much faster.

Published Tuesday, April 13, 2004 2:09 AM by Justin Rogers

Comments

Tuesday, April 13, 2004 8:10 AM by TrackBack

# re: People are still using dr[stringName], when dr[intOrdinal] is soooooo much faster.

Tuesday, April 13, 2004 8:10 AM by TrackBack

# re: IsDBNull() alternative

Tuesday, April 13, 2004 9:50 AM by Brad More

# re: Since I can't stand unvalidated performance claims, I looked up the reason behind IsDBNull performance

Might I suggest Convert.IsDBNull? Performs similarly to the alternative mentioned in one of your references and was graciously provided by our friends in Redmond just for this purpose.
Tuesday, April 13, 2004 12:23 PM by Drew Marsh

# re: Since I can't stand unvalidated performance claims, I looked up the reason behind IsDBNull performance

Actually, the fastest code I can imagine would be:

reader[ordinal] is DbNull

Because there is only ever one instance of DbNull you don't need to check against Value, you ca just test the type. Plus, when you check against DbNull.Value you end up re-boxing whatever comes out of the reader to pass it to DbNull's Object::Equals override.
Tuesday, April 13, 2004 12:26 PM by Drew Marsh

# re: Since I can't stand unvalidated performance claims, I looked up the reason behind IsDBNull performance

Sorry, small correction... you don't have to box because it comes out as Object already. However, I'd bet dimes to dollars that using "is" is faster than calling Equals.
Tuesday, April 13, 2004 4:28 PM by Justin Rogers

# re: Since I can't stand unvalidated performance claims, I looked up the reason behind IsDBNull performance

Convert.IsDBNull looks really good. Under the covers the only additional operation it performs is checking for an implementation of IConvertible and using that to possibly determine if the value is Empty. I'm inclined to worry about this extra goo.

The is keyword is backed by a not so fast crawling instruction isinst. isinst has to do a lot of work, so I could never imagine it being faster than the equality methods.

Be careful of small instructions that hide large details like walking down an object's derivation table.
Saturday, May 14, 2005 10:26 AM by TrackBack

# Custom CheckDBNull function in Whidbey using Generics.

Custom CheckDBNull function in Whidbey using Generics.
Thursday, June 12, 2008 2:43 AM by Work for stay at home moms.

# Moms work at home.

Work at home moms. Site build it work at home moms wahm. Work from home moms. Moms work at home.

Leave a Comment

(required) 
(required) 
(optional)
(required)