Question about SQLDataReader

  • Thread starter Thread starter Marlon
  • Start date Start date
M

Marlon

Would there be any performance gain to calling SqlDataReader.GetSqlXXXX
instead of SqlDataReader.GetXXXX ?

I assuming that SqlDataReader.GetXXXX makes a call
SqlDataReader.GetSqlXXXX (i.e.GetString calls GetSqlString)
 
Hello Marlon,
Would there be any performance gain to calling
SqlDataReader.GetSqlXXXX instead of SqlDataReader.GetXXXX ?

I assuming that SqlDataReader.GetXXXX makes a call
SqlDataReader.GetSqlXXXX (i.e.GetString calls GetSqlString)
Click to expand...

That's right.

..NET Reflector Disassembly example:

public int GetInt32(int i)
{
SqlInt32 num1 = this.GetSqlInt32(i);
return num1.Value;
}

and

public SqlInt32 GetSqlInt32(int i)
{
return (SqlInt32) this.PrepareSQLRecord(i);
}
 
There isnt much difference between GetXXX and GetSQLXXX. GetXXX is just
a wrapper that calls the appropriate GetSqlXXX. It just converts the
value to a CLR type.
 
Quoting from MSDN Documentation --
The System.Data.SqlTypes namespace provides classes for native data types
within SQL Server. These classes provide a safer, faster alternative to
other data types. Using the classes in this namespace helps prevent type
conversion errors caused in situations where loss of precision could occur.
Because other data types are converted to and from SqlTypes behind the
scenes, explicitly creating and using objects within this namespace results
in faster code as well.

Which means ---

a) It's faster.
b) It's safer.
c) It prevents type conversion errors

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
If youve got Lutz Roeder's Reflector, open it up and check out the
GetString method on the System.Data.SqlClient.SqlDataReader class. This
is what is shows in the dissassember:

public string GetString(int i)
{
SqlString text1 = this.GetSqlString(i);
return text1.Value;
}

Now even if you used GetSqlString, it would return a SqlString object.
To get the actual string value in the SqlString youd have to call
SqlString.Value. The inbuilt GetXXX functions do exactly the same. So
when using the SqlDataReader it wont make much difference if you call
GetXXX or GetSqlXXX, unless you use the Sql data types itself to store
values in your code.
 
Okay, looking at reflected code.. lets take the example of --

public byte GetByte(int i)
{
SqlByte num1 = this.GetSqlByte(i);
return num1.Value;
}
..... Compared With ....
public SqlByte GetSqlByte(int i)
{
return (SqlByte) this.PrepareSQLRecord(i);
}


This in fact validates what I just said .. GetByte calls GetSqlByte, so
GetByte is GetSqlByte PLUS Some Extra Work ..
1. Specifically the vtable jump of num1.value,
2. The extra cost of conversion (if SqlByte.Value was nothing but SqlByte,
you'd get compilation errors .. so SqlByte.Value is not SqlByte, it's
actually byte, and there's a conversion going on .., don't know how MS
implemented it, but I'd use to implicit cast routine .. chances are they
did too)
3. Extra creation of .. a public struct SqlByte .. that looks somewhat like
this .. (See you at the end of this) ..

public struct SqlByte : INullable, IComparable
{
private byte m_value;
private bool m_fNotNull;
private static readonly int x_iBitNotByteMax;
public static readonly SqlByte Null;
public static readonly SqlByte Zero;
public static readonly SqlByte MinValue;
public static readonly SqlByte MaxValue;
private SqlByte(bool fNull);
public SqlByte(byte value);
public bool IsNull { get; }
public byte Value { get; }
public static implicit operator SqlByte(byte x);
public static explicit operator byte(SqlByte x);
public override string ToString();
public static SqlByte Parse(string s);
public static SqlByte operator ~(SqlByte x);
public static SqlByte operator +(SqlByte x, SqlByte y);
public static SqlByte operator -(SqlByte x, SqlByte y);
public static SqlByte operator *(SqlByte x, SqlByte y);
public static SqlByte operator /(SqlByte x, SqlByte y);
public static SqlByte operator %(SqlByte x, SqlByte y);
public static SqlByte operator &(SqlByte x, SqlByte y);
public static SqlByte operator |(SqlByte x, SqlByte y);
public static SqlByte operator ^(SqlByte x, SqlByte y);
public static explicit operator SqlByte(SqlBoolean x);
public static explicit operator SqlByte(SqlMoney x);
public static explicit operator SqlByte(SqlInt16 x);
public static explicit operator SqlByte(SqlInt32 x);
public static explicit operator SqlByte(SqlInt64 x);
public static explicit operator SqlByte(SqlSingle x);
public static explicit operator SqlByte(SqlDouble x);
public static explicit operator SqlByte(SqlDecimal x);
public static explicit operator SqlByte(SqlString x);
internal static SqlByte Abs(SqlByte x);
public static SqlBoolean operator ==(SqlByte x, SqlByte y);
public static SqlBoolean operator !=(SqlByte x, SqlByte y);
public static SqlBoolean operator <(SqlByte x, SqlByte y);
public static SqlBoolean operator >(SqlByte x, SqlByte y);
public static SqlBoolean operator <=(SqlByte x, SqlByte y);
public static SqlBoolean operator >=(SqlByte x, SqlByte y);
public static SqlByte OnesComplement(SqlByte x);
public static SqlByte Add(SqlByte x, SqlByte y);
public static SqlByte Subtract(SqlByte x, SqlByte y);
public static SqlByte Multiply(SqlByte x, SqlByte y);
public static SqlByte Divide(SqlByte x, SqlByte y);
public static SqlByte Mod(SqlByte x, SqlByte y);
public static SqlByte BitwiseAnd(SqlByte x, SqlByte y);
public static SqlByte BitwiseOr(SqlByte x, SqlByte y);
public static SqlByte Xor(SqlByte x, SqlByte y);
public static SqlBoolean Equals(SqlByte x, SqlByte y);
public static SqlBoolean NotEquals(SqlByte x, SqlByte y);
public static SqlBoolean LessThan(SqlByte x, SqlByte y);
public static SqlBoolean GreaterThan(SqlByte x, SqlByte y);
public static SqlBoolean LessThanOrEqual(SqlByte x, SqlByte y);
public static SqlBoolean GreaterThanOrEqual(SqlByte x, SqlByte y);
public SqlBoolean ToSqlBoolean();
public SqlDouble ToSqlDouble();
public SqlInt16 ToSqlInt16();
public SqlInt32 ToSqlInt32();
public SqlInt64 ToSqlInt64();
public SqlMoney ToSqlMoney();
public SqlDecimal ToSqlDecimal();
public SqlSingle ToSqlSingle();
public SqlString ToSqlString();
public int CompareTo(object value);
public override bool Equals(object value);
public override int GetHashCode();
static SqlByte();
}


My god .. that is UGLY .. not only because it's big .. but look at all the
members it holds .. AND *ALL* of them are value types .. think of using
those in a loop (typical to database operations).
Not to mention, SqlByte itself is a System.ValueType ..

Now think of this argument and multiply it with 10 columns and 10,000 rows -
that's quite sizable.

Therefore ..
GetSqlXXX is more efficient than GetXXX,
Also, it prevents Type Conversion errors.

The same argument applies to all other such methods.

What am I missing? :) ..

For now, I'll stick by my argument . .GetSqlXXX is better.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
Back
Top