Saturday, December 15, 2007 - 文章

StringBuilder串接字串的迷思

大部分的.NET開發者都知道，要做大量的字串相加，StringBuilder比string相加快上N倍。這個效能差異源於String物件的特性，每次"動態相加"時必須捨棄原字串佔用的記憶體空間，重新配置記憶體儲存相加後的新字串內容。只是背後的原理實在曲折，於是我們腦海只會留下"串接字串千萬要用StringBuilder，用string相加會被人笑"的簡化結論。

前些時候協助做Code Review，看到一段SQL查詢程式出現有趣的寫法。

一般為了方便閱讀，將長長的SQL依SELECT, FROM, WHERE拆成多行是很好的寫作習慣；不過在這個例子中，StringBuilder被拿來串接靜態變數，直覺上並不能發揮提高效能的效果。依我的認知，靜態字串的相加會在編譯階段時轉化成單一字串，理論上會比動用StringBuilder物件來得快。但沒測過，我也不敢確定答案與速度差距。所以我準備了三種字串組合方式，在力求SQL指令要拆行以利閱讀的前題下，第一種用StringBuilder，第二種用加號相加，第三種則用@"..."讓字串內容得以直接換行。

static string GetSqlString_1()

{

    StringBuilder sqlCmd;

    sqlCmd = new StringBuilder("");

    sqlCmd.Append(
"select customer.customername || ' ' as cstname,vendor.vbename || ' ' as vbename,");

    sqlCmd.Append(" tradepara.cparavalue || ' ' as Bank,");

    sqlCmd.Append(" vendor.brkcap ||' '|| vendor.brkcapvalue as CCASS,");

    sqlCmd.Append(" contract.tradername ||'      '|| contract.traderphone as Person,");

    sqlCmd.Append(" contract.bankno || ' ' as BIC,contract.traderemail || ' ' as email");

    sqlCmd.Append(" from sbtrade");

    sqlCmd.Append(" left join customer on sbtrade.corpid = customer.corpid");

    sqlCmd.Append(" and sbtrade.customerid = customer.customerid");

    sqlCmd.Append(" left join vendor on sbtrade.corpid = vendor.corpid");

    sqlCmd.Append(" and sbtrade.secbrkid = vendor.secbrkid");

    sqlCmd.Append(" left join tradepara on sbtrade.corpid = tradepara.corpid");

    sqlCmd.Append(" and tradepara.tradeid=:tradeid");

    sqlCmd.Append(" and sbtrade.customerid = tradepara.customerid");

    sqlCmd.Append(" and tradepara.cparaid='CUSTBANKNAME'");

    sqlCmd.Append(" left join contract on sbtrade.corpid = contract.corpid");

    sqlCmd.Append(" and sbtrade.mktcodeid = contract.mktcodeid");

    sqlCmd.Append(" and sbtrade.secbrkid = contract.secbrkid");

    sqlCmd.Append(" and contract.sbktype='CSI'");

    sqlCmd.Append(" and sbtrade.secaccount = contract.secaccount");

    sqlCmd.Append(" where sbtrade.corpid=:gCorpId and sbtrade.tradetype='BS'");

    sqlCmd.Append(" and to_char(sbtrade.tradedate,'yyyy/MM/dd') = :tradedate");

    sqlCmd.Append(" and sbtrade.mktcodeid=:mktcodeid");

    sqlCmd.Append(" and sbtrade.secbrkid = :secbrkid");

    sqlCmd.Append(" and sbtrade.secaccount = :secaccount");

    sqlCmd.Append(" and sbtrade.customerid = :customerid");

    return sqlCmd.ToString();

}

static string GetSqlString_2()

{

    string sql = 
"select customer.customername || ' ' as cstname,vendor.vbename || ' ' as vbename," +

    " tradepara.cparavalue || ' ' as Bank," +

    " vendor.brkcap ||' '|| vendor.brkcapvalue as CCASS," +

    " contract.tradername ||'      '|| contract.traderphone as Person," +

    " contract.bankno || ' ' as BIC,contract.traderemail || ' ' as email" +

    " from sbtrade" +

    " left join customer on sbtrade.corpid = customer.corpid" +

    " and sbtrade.customerid = customer.customerid" +

    " left join vendor on sbtrade.corpid = vendor.corpid" +

    " and sbtrade.secbrkid = vendor.secbrkid" +

    " left join tradepara on sbtrade.corpid = tradepara.corpid" +

    " and tradepara.tradeid=:tradeid" +

    " and sbtrade.customerid = tradepara.customerid" +

    " and tradepara.cparaid='CUSTBANKNAME'" +

    " left join contract on sbtrade.corpid = contract.corpid" +

    " and sbtrade.mktcodeid = contract.mktcodeid" +

    " and sbtrade.secbrkid = contract.secbrkid" +

    " and contract.sbktype='CSI'" +

    " and sbtrade.secaccount = contract.secaccount" +

    " where sbtrade.corpid=:gCorpId and sbtrade.tradetype='BS'" +

    " and to_char(sbtrade.tradedate,'yyyy/MM/dd') = :tradedate" +

    " and sbtrade.mktcodeid=:mktcodeid" +

    " and sbtrade.secbrkid = :secbrkid" +

    " and sbtrade.secaccount = :secaccount" +

    " and sbtrade.customerid = :customerid";

    return sql;

}

static string GetSqlString_3()

{

    string sql = @"
select customer.cstname || ' ' as customername,vendor.vbename || ' ' as vbename, 

tradepara.cparavalue || ' ' as Bank, 

vendor.brkcap ||' '|| vendor.brkcapvalue as CCASS, 

contract.tradername ||'      '|| contract.traderphone as Person, 

contract.bankno || ' ' as BIC,contract.traderemail || ' ' as email 

from sbtrade 

left join customer on sbtrade.corpid = customer.corpid 

and sbtrade.customerid = customer.customerid 

left join vendor on sbtrade.corpid = vendor.corpid 

and sbtrade.secbrkid = vendor.secbrkid 

left join tradepara on sbtrade.corpid = tradepara.corpid 

and tradepara.tradeid=:tradeid 

and sbtrade.customerid = tradepara.customerid 

and tradepara.cparaid='CUSTBANKNAME' 

left join contract on sbtrade.corpid = contract.corpid 

and sbtrade.mktcodeid = contract.mktcodeid 

and sbtrade.secbrkid = contract.secbrkid 

and contract.sbktype='CSI' 

and sbtrade.secaccount = contract.secaccount 

where sbtrade.corpid=:gCorpId and sbtrade.tradetype='BS' 

and to_char(sbtrade.tradedate,'yyyy/MM/dd') = :tradedate 

and sbtrade.mktcodeid=:mktcodeid 

and sbtrade.secbrkid = :secbrkid 

and sbtrade.secaccount = :secaccount 

and sbtrade.customerid = :customerid ";

    return sql;

}

接著我跑以下的程式分別執行GetSqlString_1(),GetSqlString_2()及GetSqlString_3()各100萬次，事前預測是1最慢，2,3一樣快，沒想到，慢的程度比想像中大多了。

Stopwatch sw = new Stopwatch();

int times = 1000000;

sw.Start();

for (int i = 0; i < times; i++)

    GetSqlString_1();

sw.Stop();

Console.WriteLine(string.Format("Test1={0}ms", sw.ElapsedMilliseconds));

sw.Reset();

sw.Start();

for (int i = 0; i < times; i++)

    GetSqlString_2();

sw.Stop();

Console.WriteLine(string.Format("Test2={0}ms", sw.ElapsedMilliseconds));

sw.Reset();

sw.Start();

for (int i = 0; i < times; i++)

    GetSqlString_3();

sw.Stop();

Console.WriteLine(string.Format("Test3={0}ms", sw.ElapsedMilliseconds));

return;

測試結果:

Test1=4152ms
Test2=6ms
Test3=7ms

【結論】

以StringBuilder提升字串相加效率主要應用於大量的反覆字串動態(in runtime)串接，靜態字串的串接在編譯時就會自動變成單一字串，牽扯到StringBuilder物件的建立與呼叫反而變慢許多。因此StringBuilder請應用在連續大量的Runtime動態字串相接才不會未得其利，反受其害。

多行式靜熊字串的表示，在C#中，我強烈推薦使用GetSqlString_3()的Literal String寫法! 用來表示多行文字的SQL語法、Javascript，極為簡潔方便。

【2009-09-07】 StringBuilder 平反文

Posted 15 December 2007 06:38 AM 由 Jeffrey | 15 comment(s) Filed under: .NET, KB

搜尋