[C#][EF] CompiledQuery

2010-09-05

昨天收到點部落格所送的Entity Framework與LINQ開發實戰(點部落真是佛心來的)，

第一篇就來整理一下CompiledQuery。

DAL採用ORM技術可以減少前端程式與後端資料庫緊密耦合，例如在開發階段客戶使用Sybase DB，

但客戶告知系統正式上線後，DB將抽換成MS SQL或 Oracle，這意味者，你不能寫特定資料庫的SQL語法，

而這時就可以感受到ORM資料存取技術的好處(當後端資料庫更換時，前端AP將不用整個翻修改寫相對應SQL)，

但相對的就得犧牲部分效能(雖然EF4中針對查詢效能已有大幅度改善)。

提高EF查詢效能方法:

一、使用已編譯的 LINQ 查詢

當我們要使用不同參數執行查詢相同實體多次時，CompiledQuery類別會對查詢進行編譯及快取(LINQ to Entities )，

以達到重複使用並提高查詢效能。

二、考慮使用 NoTracking

如果沒有更新或刪除物件的計畫，則執行查詢時，

請考慮使用 NoTracking 合併選項，以減少追蹤物件所花費成本。

(關於提高EF查詢效能MSDN有更詳細的說明)

Using CompiledQuery and NoTracking:

BL專案新增TestControl.cs。

DAL專案新增Demo實體資料模型，實體資料模型中包含一個nopt_qtpls實體資料表，

以及TABLENopt_Qtpls.cs。

MyEF新增BL和DAL參考。

TestControl.cs

 public enum QryMethod

            Select,

            CompiledQuery

        public TestControl()

        public string TestInit( QryMethod QryType, string Bill_No )

            TableNopt_Qtpls tabqtpls = new TableNopt_Qtpls();

            string QryResult = string.Empty;

            string FinallResult = string.Empty;

            Stopwatch sw = new Stopwatch();

            sw.Reset();

            sw.Start();

            switch( QryType )

                case QryMethod.CompiledQuery:

                    QryResult = tabqtpls.ExecCompiledQuery( Bill_No );

                    break;

                case QryMethod.Select:

                    QryResult = tabqtpls.ExecSelect( Bill_No );

                    break;

                default:

                    QryResult = "未執行任何查詢!";

                    break;

            sw.Stop();

            FinallResult = string.Format( "查詢結果:{0}", QryResult )+Environment.NewLine

                + string.Format( "花費時間(ms):{0}", sw.ElapsedMilliseconds.ToString() + Environment.NewLine );

            return FinallResult;

TestControl類別中，提供TestInit方法。

TableNopt_Qtpls.cs

   private static readonly Func<demoEntities, string, IQueryable<nopt_qtpls>> _CompiledQuery =

       CompiledQuery.Compile<demoEntities, string, IQueryable<nopt_qtpls>>(

      ( db, Bill_No ) => from qtpls in db.nopt_qtpls

                         where qtpls.BILL_NO == Bill_No

                         select qtpls );

        public string ExecCompiledQuery( string Bill_No )

            using( demoEntities db = new demoEntities() )

try

                    db.nopt_qtpls.MergeOption = MergeOption.NoTracking;

                    var qry = _CompiledQuery.Invoke( db, Bill_No );

                    StringBuilder sb = new StringBuilder();

                    foreach( var s in qry )

                        sb.AppendLine( string.Format( "單號:{0},類型{1}", s.BILL_NO, s.BILL_NO_TYPE ) );

                    return sb.ToString();

                catch( Exception ex )

                    return ex.Message;

  public string ExecSelect( string Bill_No )

            string ConnectionString = ConfigurationManager.ConnectionStrings[ "SqlConStr" ].ConnectionString;

            using( SqlConnection conn = new SqlConnection( ConnectionString ) )

try

                    string select = "SELECT BILL_NO,BILL_NO_TYPE FROM dbo.nopt_qtpls where BILL_NO=@Bill_No";

                    string Result = string.Empty;

                    SqlDataReader dr = null;

                    using( SqlCommand cmd = new SqlCommand( select, conn ) )

                        conn.Open();

                        cmd.Parameters.Clear();

                        cmd.Parameters.Add( new SqlParameter( "@Bill_No", Bill_No ) );

                        dr = cmd.ExecuteReader();

                        StringBuilder sb = new StringBuilder();

                        while( dr.Read() )

                            sb.AppendLine( string.Format( "單號:{0},類型{1}", dr[ "BILL_NO" ].ToString(), dr[ "BILL_NO_TYPE" ].ToString() ) );

                        return sb.ToString();

                catch( SqlException sqlex )

                    return sqlex.Message;

                catch( Exception ex )

                    return ex.Message;

TableNopt_Qtpls類別中，提供兩種存取資料方法。

Default.aspx.cs

  protected void Button1_Click( object sender, EventArgs e )

            TestControl testcontrol = new TestControl();

            //查詢5次不同的Bill_No參數，Using EF(CompiledQuery,NoTracking)

            StringBuilder sb = new StringBuilder();

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

                sb.AppendLine( testcontrol.TestInit( TestControl.QryMethod.CompiledQuery, Bill_No[ i ] ) );

            TextBox1.Text = sb.ToString();

            sb.Clear();

            //查詢5次不同的Bill_No參數，Using Ado.Net

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

                sb.AppendLine( testcontrol.TestInit( TestControl.QryMethod.Select, Bill_No[ i ] ) );

            TextBox2.Text = sb.ToString();

結果(這裡順便和ADO.NET比較一下):

第一次

可以看到只有第一次花費較多時間，後續相關查詢時間幾乎都差距不大。

第二次

第三次

可以看到CompiledQuery對EF效能果然影響很大，CompiledQuery讓後續查詢花費時間可以很接近使用ADO.NET查詢花費時間。

參考

RiCo技術農場