DataReader 程序性能優化

作者: WizardWu 來源: 博客園發布時間: 2010-02-22 10:19 閱讀: 2094 次推薦: 0 原文鏈接 [收藏]

　　隨著 .NET 平臺上，LINQ、ORM 框架、Dynamic Data、... 各種數據訪問技術不斷推陳出新，程序員也一直追著新技術跑，但對底層和代碼細節卻越來越難以掌控。當項目性能需要調優時，通常也只能對數據庫加入更多索引，而多數人已難以對數據訪問的代碼優化，且手寫 SQL 語句的功力似乎也持續退化中。

　　大家在拼命追求新技術時，似乎已忘記微軟的 MCTS 證照，還有一門 ADO.NET 的科目。且若有練好 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# 代碼生成器，也用過類似的小技巧。

　　此示例若您將前臺的 DropDownList 控件，換成微軟去年九月新推出的 ComboBox 這個可選也可輸入的 AJAX 下拉菜單控件，此示例的 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