WizardWu 編程網

一位台灣的工程師,接觸 .NET 逾十年,近年研究 SQL Server、Performance Tuning、手機應用

博客园 首页 新随笔 联系 订阅 管理
  103 Posts :: 0 Stories :: 1097 Comments :: 35 Trackbacks

随着 .NET 平台上,LINQ、ORM 框架、Dynamic Data、... 各种数据访问技术不断推陈出新,程序员也一直追着新技术跑,但对底层和代码细节却越来越难以掌控。当项目性能需要调优时,通常也只能对数据库加入更多索引,而多数人已难以对数据访问的代码优化,且手写 SQL 语句的功力似乎也持续退化中。

大家在拼命追求新技术时,似乎已忘记微软的 MCTS 证照,还有一门 ADO.NET 的科目 [7] 。且若有练好 ADO.NET 的基本功,当项目遇到特殊需求时,也才能手写得出来。例如下图 1 的「阶层式下拉菜单 (Hierarchical DropDownList)」,功能很简单,在实务上也常遇到,但单纯靠 DataSource 控件难以实现,必须手写 DataReader 来自定义细节。

------------------------------------------------------------------
本帖的示例下载点:
http://files.cnblogs.com/WizardWu/100216.zip

(执行第一個示例,需要 VS 2008 或 IIS,以及 SQL Server 的 Northwind 数据库)
(执行第二個示例,需要 VS 2008 或 .NET 3.5,以及 SQL Server 的 AdventureWorks 数据库)
(执行第二個示例,需要 VS 2008 或 .NET 3.5,以及 SQL Server 的 AdventureWorks 数据库)
------------------------------------------------------------------

 

 (一) DataReader 的变化应用、用 Get 开头的方法提升 DataReader 的性能 

 


图 1 以 DataReader 实现的 Hierarchical DropDownList

 
上图 1 为本帖下载的第一个示例。在此 ASP.NET 示例中,我们不用 DataTable 以免速度较慢又浪费内存 (此却为 DataSource 控件的默认选择),改用一个 DataReader 对象,去两个表中,各执行一句 SQL SELECT 语句,最后将两个 result set 加载、合并到同一个 DropDownList 中。我在以前写的 C# 代码生成器 [10] ,也用过类似的小技巧。

此示例若您将前台的 DropDownList 控件,换成微软去年九月新推出的 ComboBox 这个可选也可输入的 AJAX 下拉菜单控件 [4] ,此示例的 Code-behind 一行代码都不用改,直接就可套用至 ComboBox 上。
 

示例一的 Code-behind 代码
using (SqlConnection conn = new SqlConnection(strConnString))
{
    conn.Open();
    
using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection 
= conn;

        
//利用分号「;」串连多句 SQL 语句,之后再一次丢进数据库去执行,达成「批次(Batch)」读取或更新数据库的目的。
        
//此技巧亦适用于 INSERT、UPDATE、DELETE 的「批次」执行。       
        cmd.CommandText = "SELECT TOP 5 CompanyName, CustomerID FROM Customers ; SELECT LastName, EmployeeID FROM Employees";

        
using (SqlDataReader dr = cmd.ExecuteReader())
        {
            DropDownList1.Items.Add(
"客户列表");
            
int i = 1;    //目前的数据行

            
while (dr.Read())
            {
                
//.NET typed accessor
                DropDownList1.Items.Add("  " + dr.GetString(0));    //数据库跑循环取回的字段1 (Name),当作选项的 Text
                
//.NET typed accessor
                DropDownList1.Items[i].Value = dr.GetString(1);          //数据库跑循环取回的字段2 (ID),当作选项的 Value

                i
++;
            }

            dr.NextResult();       
//到下一個 ResultSet

            DropDownList1.Items.Add(
"员工列表");
            i
++;

            
while (dr.Read())
            {
                
//.NET typed accessor
                DropDownList1.Items.Add("  " + dr.GetString(0));    //数据库跑循环取回的字段1 (Name) ,当作选项的 Text
                
//Index-based accessor
                DropDownList1.Items[i].Value = dr[1].ToString();         //数据库跑循环取回的字段2 (ID),当作选项的 Value

                
//亦可用 GetSql 开斗的方法 (Provider-specific typed accessor)
                
//DropDownList1.Items[i].Value = dr.GetSqlInt32(1).ToString();   //数据库跑循环取回的字段2 (ID),当作选项的 Value

                i
++;
            }
        }
    }
}

 
在 DataReader 中,使用基于「序列号」的查找 (column ordinal),比基于「命名」的查找 (column field name) 更有效率。例如上面的示例一,我们用 dr.GetString(1) 或 dr[1].ToString(),而不是用 dr["字段名称"]。可分类如下:

  • 写法 (1) 使用 DataReader 索引 + 基于「序列号」的查找,如:dr[1].ToString(),英文称为:Index-based accessor
  • 写法 (2) 使用 DataReader 索引 + 基于「命名」的查找,如:dr["LastName"].ToString(),这是性能最不好的写法
  • 写法 (3) 使用 Get 开头的方法 + 基于「序列号」的查找,如:dr.GetString(1),英文称为:.NET typed accessor
  • 写法 (4) 使用 GetSql 开头的方法 + 基于「序列号」的查找,如:dr.GetSqlString(1),英文称为:Provider-specific typed accessor
  • 写法 (5) 基于「序列号」+ GetOrdinal() 方法的查找。

其中的性能优劣,由好到坏依序为:(4) --> (3) --> (1) --> (2)。一般人會為了方便維護,而採用 (2) 的基于「命名」查找的寫法,殊不知其為性能最差的寫法。待會本帖后續的第 (二) 點會有實際的示例和測試數據。而 (5) 的 GetOrdinal() 寫法,即是為了補足前述各種寫法的優缺點而誕生,在本帖后续的第 (三) 点会介绍到它。

此外,性能最好的 (4),以 GetSql 开头的方法,仅适用于 SQL Server 7 以上版本的数据库。其底层采用 SQL Server 专属的 TDS (表格式資料串流) 格式来交换数据,因此执行性能会比 (3) - 以 Get 开头的方法,执行速度更快。而 OracleClient 命名空间,也提供了 GetOracle 开头的方法可以使用,但 OleDb、ODBC 则没有提供专属的方法。

------------------------------------------------------------------

 

 (二) 用 Typed Accessors 提升 DataReader 的性能

 

 本示例,我们用 SqlDataReader 访问数据库,各以前述的 (3)、(1)、(2) 的写法,执行 100 次循环的查询,测试其性能差距有多大。此 Console Mode 的示例二,代码如下:

示例二 - DataReaderTypedAccessors
using System.Data.SqlClient;

namespace DataReaderTypedAccessors
{
class Program
{
static void Main(string[] args)
{
int loops = 100; //本示例循环的次数



int contactID;
string firstName;
string middleName = null;
string lastName;


int startTick = 0;
int elapsedTick;


string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";


string sqlSelect = "SELECT ContactID, FirstName, MiddleName, LastName FROM Person.Contact";



Console.WriteLine("---DataReader column value access timing test, {0} iterations---\n", loops);


SqlConnection connection
= new SqlConnection(sqlConnectString);
SqlCommand command
= new SqlCommand(sqlSelect, connection);
connection.Open();



elapsedTick = 0;
for (int i = 0; i < loops; i++)
{
// Create the DataReader and retrieve all fields for each
// record using a typed accessor with a column ordinal
using (SqlDataReader dr = command.ExecuteReader())
{
startTick
= Environment.TickCount;
while (dr.Read())
{
contactID
= dr.GetInt32(0);
firstName
= dr.GetString(1);
middleName
= dr.IsDBNull(2) ? null : dr.GetString(2);
lastName
= dr.GetString(3);
}

elapsedTick
+= Environment.TickCount - startTick;
}
}
Console.WriteLine(
"Typed accessor (写法 3): Ticks = {0}", elapsedTick);



elapsedTick = 0;
for (int i = 0; i < loops; i++)
{
// Create the DataReader and retrieve all fields for each
// record using a column ordinal
using (SqlDataReader dr = command.ExecuteReader())
{
startTick
= Environment.TickCount;
while (dr.Read())
{
contactID
= Convert.ToInt32(dr[0]);
firstName
= Convert.ToString(dr[1]);
middleName
= Convert.ToString(dr[2]);
lastName
= Convert.ToString(dr[3]);
}

elapsedTick
+= Environment.TickCount - startTick;
}
}
Console.WriteLine(
"Column ordinal (写法 1): Ticks = {0}", elapsedTick);



elapsedTick = 0;
for (int i = 0; i < loops; i++)
{
// Create the DataReader and retrieve all fields for each
// record using a column field name
using (SqlDataReader dr = command.ExecuteReader())
{
startTick
= Environment.TickCount;
while (dr.Read())
{
contactID
= Convert.ToInt32(dr["ContactID"]);
firstName
= Convert.ToString(dr["FirstName"]);
middleName
= Convert.ToString(dr["MiddleName"]);
lastName
= Convert.ToString(dr["LastName"]);
}


elapsedTick
+= Environment.TickCount - startTick;
}
}
Console.WriteLine(
"Column name (写法 2): Ticks = {0}", elapsedTick);



Console.WriteLine(
"\nPress any key to continue.");
Console.ReadKey();
}
}
}

 

 执行结果如下图 2,写法 (1) 的 Column ordinal,比写法 (2) 的 Column name,速度快上约 20 - 25 % 。且当循环加大到 1000 次时,比例仍差不多。

而写法 (3) 的 Typed accessor,比写法 (1) 的 Column ordinal 速度快上 27 %,比写法 (2) 的 Column name,速度快上约 37 % 。当循环加大到 1000 次时,比例会有所变化。
 

由于 Typed accessor 可避免 Column ordinal、Column name 里重复的 boxing、unboxing 转型动作,因此必然对性能有所助益。加上可搭配 IsDBNull() 方法,来处理数据库的栏位为 NULL 的问题 (参考上方代码),因此是程序员手动撰码提取数据的首选写法。

图 2 示例二的执行结果,三种写法各自耗费的时间

 
在这个示例中,您还会发现,按 Ctrl + F5 执行但不调拭,会比挟 F5 执行并调拭,执行速度要快很多。

 ------------------------------------------------------------------ 

 

(三) 用 Column Ordinals 提升 DataReader 的性能 

 

我们已经知道,基于「序列号」的查找 (column ordinal),比基于「命名」的查找 (column field name) 更有效率。不过在实务上维护项目时,表的字段序号 (或称索引) 有可能会变动或增减,或在改写公司前人的 SQL 语句时,亦有可能导致欲查询的字段与真正表中的字段不符。此时我们可透过 GetOrdinal() 方法来处理此种问题,先以字段名称,来查找所对应到此字段的正确序列号,避免硬编码序列号所产生的字段提取不正确。

以下是 MSDN 的示例 [2] ,GetOrdinal 先执行区分大小写的查找;如果失败,则进行另一次不区分大小写的搜索。我们先调用单一次 GetOrdinal 方法,取得特定字段的序列号,再将结果分配给一或多个变量,以便在后续的 DataReader 循环、实际提取值时来引用这些序列号。
 

private static void ReadGetOrdinal(string connectionString)
{
string queryString = "SELECT DISTINCT CustomerID FROM dbo.Orders;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command
= new SqlCommand(queryString, connection);
connection.Open();

SqlDataReader reader
= command.ExecuteReader();

// Call GetOrdinal and assign value to variable.
 
int customerID = reader.GetOrdinal("CustomerID");


// Use variable with GetString inside of loop.
while (reader.Read())
{
Console.WriteLine(
"CustomerID={0}", reader.GetString(customerID));
}

reader.Close();
}
}

 
以下是本帖提供下载的的第三个示例,原理相同,但我们将 GetOrdinal 方法所提取到的 ContactID、FirstName、LastName 三个字段的序列号,分别搭配本帖前述的写法 (1)、写法 (3)、写法 (4),提取出内容相同的三个 result set,而写法 (3)、写法 (4) 还能搭配 IsDBNull() 方法来使用。
 

示例三 - DataReaderColumnOrdinals
using System.Data.SqlClient;


namespace DataReaderColumnOrdinals
{
class Program
{
static void Main(string[] args)
{
int coContactID, coFirstName, coLastName;


string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";


string sqlSelect = "SELECT TOP 5 * FROM Person.Contact";



SqlConnection connection = new SqlConnection(sqlConnectString);
SqlCommand command
= new SqlCommand(sqlSelect, connection);
connection.Open();



//创建 DataReader 并取得多个字段的「列序号 (ordinals)」。
//应避免在循环中调用 GetOrdinal 方法,以免影响性能
using (SqlDataReader drSchema = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
//SqlDataReader.GetOrdinal 方法: 在给定「列 (字段)」名称的情况下,获取各自对应的列序号
coContactID = drSchema.GetOrdinal("ContactID");
coFirstName
= drSchema.GetOrdinal("FirstName");
coLastName
= drSchema.GetOrdinal("LastName");
}



//输出字段序列号 (column ordinals)
Console.WriteLine("--- 字段在表中的序列号 Column ordinals ---");
Console.WriteLine(
"ContactID = {0}, FirstName = {1}, LastName = {2}",
coContactID, coFirstName, coLastName);




Console.WriteLine("\n--- Index-based accessor (写法 1) ---");


using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
// Output fields using the column ordinals as accessors
Console.WriteLine("{0}\t{1}, {2}",
dr[coContactID], dr[coLastName], dr[coFirstName]);
}
}




Console.WriteLine("\n--- .NET typed accessor (写法 3) ---");


using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
// Output fields using the column ordinals as accessors
Console.WriteLine("{0}\t{1}, {2}",
dr.IsDBNull(coContactID)
?
"NULL" : dr.GetInt32(coContactID).ToString(),
dr.IsDBNull(coLastName)
?
"NULL" : dr.GetString(coLastName),
dr.IsDBNull(coFirstName)
?
"NULL" : dr.GetString(coFirstName));
}
}




Console.WriteLine("\n--- Provider-specific typed accessor (写法 4) ---");


using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
// Output fields using the column ordinals as accessors
//GetSqlString() 方法后面,不用再加上 ToString() 转型
Console.WriteLine("{0}\t{1}, {2}",
dr.IsDBNull(coContactID)
?
"NULL" : dr.GetSqlInt32(coContactID).ToString(),
dr.IsDBNull(coLastName)
?
"NULL" : dr.GetSqlString(coLastName),
dr.IsDBNull(coFirstName)
?
"NULL" : dr.GetSqlString(coFirstName));
}
}



connection.Close();



Console.WriteLine("\nPress any key to continue.");
Console.ReadKey();
}
}
}

 

图 3 性能优劣,由好到坏依序为:写法 (4) --> 写法 (3) --> 写法 (1)

 -----------------------------------------------------------------

 

 (四) 用 GetValues 方法,一次提取記錄中所有字段的值 

 

由于 Get 开头的方法,一次只能提取记录中某一个字段的值,为了提升执行性能,从 ADO.NET 2.0 开始,新增了一个 SqlDataRecord.GetValues 方法 [3] 。它会返回一个 Object 类型的数组,里面包含目前这一条记录中,所有字段的值。

但 GetValues 方法,只能接收一个 Object 类型的数组,如下方代码,在撰码时虽然很方便,但就有些文档宣称它可提升性能,这点我是较不认同。
 

示例四 - GetValues 方法
using (SqlConnection conn = new SqlConnection(strConnString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection
= conn;
cmd.CommandText
= "SELECT * FROM Employees";

using (SqlDataReader dr = cmd.ExecuteReader())
{
Object[] objArray;
//包含目前数据行中,所有字段的值

while (dr.Read())
{
objArray
= new Object[dr.FieldCount]; //dr.FieldCount 表示字段的数量,以此决定数组的大小
                dr.GetValues(objArray); //将整条记录存储在数组中

               //从数组中,第一、第二个字段的数据取出
                DropDownList1.Items.Add(objArray[0].ToString() + ", " + objArray[1].ToString());
}
}
}
}

 
此外,DataReader 还有许多实务上很方便的功能,例如:GetName() 方法,可用字段的序列号,查找其对应的字段名称。若未曾翻阅过这些 ADO.NET 的 API 或书籍,而只会用鼠标拖放 DataSource 控件、edmx、xsd,在开发上很方便,但仍有不足之处,应该要对 ADO.NET 有一定的认识 (可惜博客园和一些论坛,连这个分类项都没有)。 

------------------------------------------------------------------

 

新的一年,在大家一窝蜂地追求新技术、新框架、新的 IDE 工具时,若您的公司要招聘新的程序员,当心遇到对 .NET 4.0、VS 2010、LINQ、EDM、Dynamic Data、Silverlight、... 都能琅琅上口,在 VS 里拖拉控件他也一把罩,但对基础类库和 API 特性却毫不熟悉的人,或需求稍微变化就不会写的人 [9]


------------------------------------------------------------------

 

 相关文章:

[1] SqlDataReader 成员
http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqldatareader_members%28VS.80%29.aspx

[2] SqlDataReader.GetOrdinal 方法
http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqldatareader.getordinal.aspx

[3] SqlDataRecord.GetValues 方法
http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.server.sqldatarecord.getvalues%28VS.80%29.aspx

[4] AJAX ComboBox Demonstration
http://www.asp.net/AJAX/AjaxControlToolkit/Samples/ComboBox/ComboBox.aspx


参考书籍:

[5] ADO.NET 3.5 Cookbook, chapter 10 (O'Reilly)
http://oreilly.com/catalog/9780596101404/
http://www.amazon.com/ADO-NET-3-5-Cookbook-Cookbooks-OReilly/dp/0596101406/ref=sr_1_1?ie=UTF8&s=books&qid=1266291565&sr=1-1

[6] ADO.NET 3.5 精研講座,作者:许熏尹,出版社:悦知出版社 (台湾书籍)
http://www.delightpress.com.tw/book.aspx?book_id=SKUP00004


其他:

[7] 微软 .NET 3.5 的 MCTS、MCPD 认证介绍
http://www.cnblogs.com/WizardWu/archive/2009/01/24/1380718.html

[8] 网站性能越来越差怎么办?
http://www.cnblogs.com/WizardWu/archive/2009/01/03/1367527.html

[9] 程序员真情忏悔录
http://www.cnblogs.com/WizardWu/archive/2009/01/29/1381275.html

[10] C# 代码生成器 & 网站架构设计
http://www.cnblogs.com/WizardWu/archive/2009/12/16/1625191.html

[11] 华山的剑宗、气宗
http://www.icoa.cn/show.asp?id=246
http://blog.lawask.cn/lawyer-article-418.htm
http://www.exiaoshuo.com/wuxia/64510/3234371.asp
http://art.macd.cn/index/t-251861-a-268887.html

------------------------------------------------------------------

posted on 2010-02-16 19:33  WizardWu  阅读(8652)  评论(32编辑  收藏