This article does not have any technical content, is to test MSSqlHelper in the use of reflection, do not use reflection performance comparison.
After that, don't ask why you don't use ORM or something like that - there will be other articles about your own ORM upgrade history over the years.
Background:
I have my own MSSqlHelper, which is the most basic database operation class.
When Query queries a collection, you can specify a delegate for reader => objects -- if not, MSSqlHelper automatically assigns values through reflection.
static void Main(string[] args)
{
Test0();
Test1();
Test0();
Test1();
Console.ReadKey();
}
public static void Test0()
{
string connString = "Data Source=localhost;Initial Catalog=InkFxBlog;User Id=sa; Pwd=123.com;";
DateTime time0 = DateTime.Now;
List<TS_SDK> list = MSSqlHelper.Query<TS_SDK>(connString, "SELECT * FROM dbo.TS_SDK");
DateTime time1 = DateTime.Now;
Console.WriteLine("Test0 | " + list.Count + " | " + (time1 - time0).TotalSeconds + "second");
}
public static void Test1()
{
string connString = "Data Source=localhost;Initial Catalog=InkFxBlog;User Id=sa; Pwd=123.com;";
DateTime time0 = DateTime.Now;
List<TS_SDK> list = MSSqlHelper.Query<TS_SDK>(connString, "SELECT * FROM dbo.TS_SDK", null, reader =>
{
TS_SDK item = new TS_SDK();
item.FId = (Int64)reader["FId"];
item.FNumber = (string)reader["FNumber"];
item.FEnum = (string)reader["FEnum"];
item.FName = (string)reader["FName"];
item.FSDKName = (string)reader["FSDKName"];
item.FFullName = (string)reader["FFullName"];
item.FType = (string)reader["FType"];
item.FVisit = (string)reader["FVisit"];
item.FInheritFrom = (string)reader["FInheritFrom"];
item.FJoinBaseType = (string)reader["FJoinBaseType"];
item.FJoinChildType = (string)reader["FJoinChildType"];
item.FIsStatic = (Boolean)reader["FIsStatic"];
item.FIsOverride = (Boolean)reader["FIsOverride"];
item.FIsVirtual = (Boolean)reader["FIsVirtual"];
item.FIsAbstract = (Boolean)reader["FIsAbstract"];
item.FIsInherit = (Boolean)reader["FIsInherit"];
item.FIsNetFx = (Boolean)reader["FIsNetFx"];
item.FOutUrl = (string)reader["FOutUrl"];
item.FSummary = (string)reader["FSummary"];
item.FRtSummary = (string)reader["FRtSummary"];
item.FCSCode = (string)reader["FCSCode"];
item.FVBCode = (string)reader["FVBCode"];
item.FCPPCode = (string)reader["FCPPCode"];
item.FFSCode = (string)reader["FFSCode"];
item.FAssembly = (string)reader["FAssembly"];
item.FVersion = (string)reader["FVersion"];
item.FNameSpace = (string)reader["FNameSpace"];
item.FParentId = (Int64)reader["FParentId"];
item.FParentNumber = (string)reader["FParentNumber"];
item.FDemo = (string)reader["FDemo"];
item.FInfo = (string)reader["FInfo"];
return item;
});
DateTime time1 = DateTime.Now;
Console.WriteLine("Test1 | " + list.Count + " | " + (time1 - time0).TotalSeconds + "second");
}
Operation results:
The 7W rows of the whole table were read.
—— Obviously: without specifying the delegation read by reader, the use of built-in reflection code is a full 3 seconds slow.
—— Of course, MSSqlHelper's built-in delegation controls stability, using such code:
item.FId = Tools.ToLong(reader["FId"]);
item.FName = Tools.ToString("FName");
The performance of such conversion functions is certainly not as fast as that of the following code:
item.FId = (Int64)reader["FId"]; item.FName = (string)reader["FName"];
Why do I stick to the type change provided by Tools?
> Because of stability
> Conversion range: compatible with the following metamorphic database data
//This is the browser's time format. The following code will get 2017-03-2002:46:06000
DateTime timeA = Tools.ToDateTime("Mon Mar 20 2017 02:46:06 GMT+0800 (China Standard Time)");
//The following code can get 2017-03-19 01:43:15 000
DateTime timeA = Tools.ToDateTime("2017 March 19, 2001, 0143:15 seconds");
Let's try again with Tools'type-strong function to see how much performance Tools waste in type conversion.
public static void Test2()
{
string connString = "Data Source=localhost;Initial Catalog=InkFxBlog;User Id=sa; Pwd=123.com;";
DateTime time0 = DateTime.Now;
List<TS_SDK> list = MSSqlHelper.Query<TS_SDK>(connString, "SELECT * FROM dbo.TS_SDK", null, reader =>
{
TS_SDK item = new TS_SDK();
//This reader commission,
//Tools.ToLong() Tools.ToLong() functions used for type conversion
//Why not use direct conversions like (string)reader["FName"]? - I'd rather sacrifice performance for stability
item.FId = Tools.ToLong(reader["FId"]);
item.FNumber = Tools.ToString(reader["FNumber"]);
item.FEnum = Tools.ToString(reader["FEnum"]);
item.FName = Tools.ToString(reader["FName"]);
item.FSDKName = Tools.ToString(reader["FSDKName"]);
item.FFullName = Tools.ToString(reader["FFullName"]);
item.FType = Tools.ToString(reader["FType"]);
item.FVisit = Tools.ToString(reader["FVisit"]);
item.FInheritFrom = Tools.ToString(reader["FInheritFrom"]);
item.FJoinBaseType = Tools.ToString(reader["FJoinBaseType"]);
item.FJoinChildType = Tools.ToString(reader["FJoinChildType"]);
item.FIsStatic = Tools.ToBoolean(reader["FIsStatic"]);
item.FIsOverride = Tools.ToBoolean(reader["FIsOverride"]);
item.FIsVirtual = Tools.ToBoolean(reader["FIsVirtual"]);
item.FIsAbstract = Tools.ToBoolean(reader["FIsAbstract"]);
item.FIsInherit = Tools.ToBoolean(reader["FIsInherit"]);
item.FIsNetFx = Tools.ToBoolean(reader["FIsNetFx"]);
item.FOutUrl = Tools.ToString(reader["FOutUrl"]);
item.FSummary = Tools.ToString(reader["FSummary"]);
item.FRtSummary = Tools.ToString(reader["FRtSummary"]);
item.FCSCode = Tools.ToString(reader["FCSCode"]);
item.FVBCode = Tools.ToString(reader["FVBCode"]);
item.FCPPCode = Tools.ToString(reader["FCPPCode"]);
item.FFSCode = Tools.ToString(reader["FFSCode"]);
item.FAssembly = Tools.ToString(reader["FAssembly"]);
item.FVersion = Tools.ToString(reader["FVersion"]);
item.FNameSpace = Tools.ToString(reader["FNameSpace"]);
item.FParentId = Tools.ToLong(reader["FParentId"]);
item.FParentNumber = Tools.ToString(reader["FParentNumber"]);
item.FDemo = Tools.ToString(reader["FDemo"]);
item.FInfo = Tools.ToString(reader["FInfo"]);
return item;
});
DateTime time1 = DateTime.Now;
Console.WriteLine("Test2 | " + list.Count + " | " + (time1 - time0).TotalSeconds + "second");
}
Test again:
—— Recently, I want to optimize my underlying auxiliary classes, so I turn the code out and toss it around.~
----------------------------------------------------------------------------------------------------------------------------------------
Just modified the reflection code to enable Emit High Speed Reflection
As a result, performance has been significantly improved, with only 15 to 20% performance loss - I'm satisfied.
Ps. Current Emit Reflections use generic code (internal and external auxiliary classes) - if Emit code is written specifically, performance can be improved again, but I am too lazy to write.
