Linq to sql 简单性能差异指引 2 (转)

Linq to sql 简单性能差异指引 2 (转)

 测试环境：
        cpu: tk55
        ram: 2g
        hd:120g
        os:vista+sp1
        sql:sql2005开发版
      以下测试只跑一两次，取最后一次（一般第一次会很慢）。

      1.禁用 ObjectTrackingEnabled
         当只是查询数据而不跟踪对象状态时禁用，将极大的提供查询性能。性能差异在36倍左右。测试代码：

            AdventureWorksDataContext adventureWorks1  =   new  AdventureWorksDataContext();
            DateTime d1  =  DateTime.Now;
            adventureWorks1.ObjectTrackingEnabled  =   true ;
            var q1  =  (from p  in  adventureWorks1.Products select p).ToList();
            DateTime d2  =  DateTime.Now;
            Console.WriteLine(d2.Ticks  -  d1.Ticks);

            AdventureWorksDataContext adventureWorks2  =   new  AdventureWorksDataContext();
            DateTime d3  =  DateTime.Now;
            adventureWorks2.ObjectTrackingEnabled  =   false ;
            var q2  =  (from p  in  adventureWorks2.Products select p).ToList();
            DateTime d4  =  DateTime.Now;
            Console.WriteLine(d4.Ticks  -  d3.Ticks);

        输出：
         11232000
        312000

     2.不记录输出
        输入记录一般只用在开发阶段，在部署环境中一般不使用。性能差异在17-18倍左右。测试代码：

            AdventureWorksDataContext adventureWorks1  =   new  AdventureWorksDataContext();
            DateTime d1  =  DateTime.Now;
            adventureWorks1.Log  =  Console.Out;
            var q1  =  (from p  in  adventureWorks1.Products select p).ToList();
            DateTime d2  =  DateTime.Now;
            Console.WriteLine(d2.Ticks  -  d1.Ticks);

            AdventureWorksDataContext adventureWorks2  =   new  AdventureWorksDataContext();
            DateTime d3  =  DateTime.Now;
            var q2  =  (from p  in  adventureWorks2.Products select p).ToList();
            DateTime d4  =  DateTime.Now;
            Console.WriteLine(d4.Ticks  -  d3.Ticks);

输出
SELECT [t0].[ProductID], [t0].[Name], [t0].[ProductNumber], [t0].[MakeFlag], [t0
].[FinishedGoodsFlag], [t0].[Color], [t0].[SafetyStockLevel], [t0].[ReorderPoint
], [t0].[StandardCost], [t0].[ListPrice], [t0].[Size], [t0].[SizeUnitMeasureCode
], [t0].[WeightUnitMeasureCode], [t0].[Weight], [t0].[DaysToManufacture], [t0].[
ProductLine], [t0].[Class], [t0].[Style], [t0].[ProductSubcategoryID], [t0].[Pro
ductModelID], [t0].[SellStartDate], [t0].[SellEndDate], [t0].[DiscontinuedDate],
 [t0].[rowguid], [t0].[ModifiedDate]
FROM [Production].[Product] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

       11076000
       624000

      3.编译lambda表达式
         性能差异14-15倍。测试代码：

            AdventureWorksDataContext adventureWorks1  =   new  AdventureWorksDataContext();
            DateTime d1  =  DateTime.Now;
            var q1  =  (from p  in  adventureWorks1.Products select p).ToList();
            DateTime d2  =  DateTime.Now;
            Console.WriteLine(d2.Ticks  -  d1.Ticks);

            AdventureWorksDataContext adventureWorks2  =   new  AdventureWorksDataContext();
            DateTime d3  =  DateTime.Now;
            var compiledQuery  =  CompiledQuery.Compile((AdventureWorksDataContext ctx)  =>  from p  in  ctx.Products select p);
            var q2  =  compiledQuery(adventureWorks2).ToList();
            DateTime d4  =  DateTime.Now;
            Console.WriteLine(d4.Ticks  -  d3.Ticks);

         输出：
         11076000
         780000

       4.使用DataLoadOptions.LoadWith
            性能差异5-6倍左右。测试代码：

            AdventureWorksDataContext adventureWorks1 = new AdventureWorksDataContext();
            DateTime d1 = DateTime.Now;
            var q1 = (from o in adventureWorks1.WorkOrders where o.ProductID == 3 select o).ToList();
            DateTime d2 = DateTime.Now;
            Console.WriteLine(d2.Ticks - d1.Ticks);

            AdventureWorksDataContext adventureWorks2 = new AdventureWorksDataContext();
            DateTime d3 = DateTime.Now;
            DataLoadOptions options = new DataLoadOptions();
            options.LoadWith<WorkOrder>(o => o.Product);
            adventureWorks2.LoadOptions = options;
            var q2 = (from o in adventureWorks2.WorkOrders where o.ProductID == 3 select o).ToList();
            DateTime d4 = DateTime.Now;
            Console.WriteLine(d4.Ticks - d3.Ticks);

          输出：
          11700000
          2184000

        5.使用延迟加载(系统默认)
           性能差异42-43倍左右。测试代码          

            AdventureWorksDataContext adventureWorks1  =   new  AdventureWorksDataContext();
            DateTime d1  =  DateTime.Now;
            adventureWorks1.DeferredLoadingEnabled  =   true ;
            var q1  =  (from o  in  adventureWorks1.WorkOrders  where  o.ProductID  ==   3  select o).ToList();
            DateTime d2  =  DateTime.Now;
            Console.WriteLine(d2.Ticks  -  d1.Ticks);

            AdventureWorksDataContext adventureWorks2  =   new  AdventureWorksDataContext();
            DateTime d3  =  DateTime.Now;
            adventureWorks2.DeferredLoadingEnabled  =   false ;
            var q2  =  (from o  in  adventureWorks1.WorkOrders  where  o.ProductID  ==   3  select o).ToList();
            DateTime d4  =  DateTime.Now;
            Console.WriteLine(d4.Ticks  -  d3.Ticks);

           输出：
          26676000
          624000 

        6.使用sql语句
            性能差异 7倍左右

            AdventureWorksDataContext adventureWorks1  =   new  AdventureWorksDataContext();
            DateTime d1  =  DateTime.Now;
            var q1  =  (from o  in  adventureWorks1.WorkOrders  where  o.ProductID  ==   3  select o).ToList();
            DateTime d2  =  DateTime.Now;
            Console.WriteLine(d2.Ticks  -  d1.Ticks);

            AdventureWorksDataContext adventureWorks2  =   new  AdventureWorksDataContext();
            DateTime d3  =  DateTime.Now;
            var q2  =  adventureWorks2.ExecuteQuery < Product > ( @" SELECT [t0].[WorkOrderID], [t0].[ProductID], [t0].[OrderQty], [t0].[StockedQty], [t0].[ScrappedQty], [t0].[StartDate], [t0].[EndDate], [t0].[DueDate], [t0].[ScrapReasonID], [t0].[ModifiedDate]
                                                            FROM [Production].[WorkOrder] AS [t0]
                                                            WHERE [t0].[ProductID] = 3 " ).ToList();
            DateTime d4  =  DateTime.Now;
            Console.WriteLine(d4.Ticks  -  d3.Ticks);

            输出：
            11856000
            1716000

        7.使用储存过程
            性能差异20倍左右。测试代码：

            AdventureWorksDataContext adventureWorks1  =   new  AdventureWorksDataContext();
            DateTime d1  =  DateTime.Now;
            var q1  =  (from o  in  adventureWorks1.WorkOrders  where  o.ProductID  ==   3  select o).ToList();
            DateTime d2  =  DateTime.Now;
            Console.WriteLine(d2.Ticks  -  d1.Ticks);

            AdventureWorksDataContext adventureWorks2  =   new  AdventureWorksDataContext();
            DateTime d3  =  DateTime.Now;
            var q2  =  adventureWorks2.GetWorkOrderByProductID( 3 ).ToList();
            DateTime d4  =  DateTime.Now;
            Console.WriteLine(d4.Ticks  -  d3.Ticks);

--  ================================================
--  Template generated from Template Explorer using:
--  Create Procedure (New Menu).SQL
--
--  Use the Specify Values for Template Parameters 
--  command (Ctrl-Shift-M) to fill in the parameter 
--  values below.
--
--  This block of comments will not be included in
--  the definition of the procedure.
--  ================================================
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
--  =============================================
--  Author:        <Author,,Name>
--  Create date: <Create Date,,>
--  Description:    <Description,,>
--  =============================================
CREATE   PROCEDURE  Production.GetWorkOrderByProductID
     --  Add the parameters for the stored procedure here
     @ProductID   int
AS
BEGIN
     --  SET NOCOUNT ON added to prevent extra result sets from
     --  interfering with SELECT statements.
     SET  NOCOUNT  ON ;

     SELECT   [ t0 ] . [ WorkOrderID ] ,  [ t0 ] . [ ProductID ] ,  [ t0 ] . [ OrderQty ] ,  [ t0 ] . [ StockedQty ] ,  [ t0 ] . [ ScrappedQty ] ,  [ t0 ] . [ StartDate ] ,  [ t0 ] . [ EndDate ] ,  [ t0 ] . [ DueDate ] ,  [ t0 ] . [ ScrapReasonID ] ,  [ t0 ] . [ ModifiedDate ]
                                                             FROM   [ Production ] . [ WorkOrder ]   AS   [ t0 ]
                                                             WHERE   [ t0 ] . [ ProductID ]   =   @ProductID
END
GO

            输出：
            12168000
            624000  

    8、获取数据以用于数据绑定  
        性能差异1.5倍左右。测试代码：  

            AdventureWorksDataContext adventureWorks1  =   new  AdventureWorksDataContext();
            DateTime d1  =  DateTime.Now;
            var q1  =  adventureWorks1.Products.ToList();
            DateTime d2  =  DateTime.Now;
            Console.WriteLine(d2.Ticks  -  d1.Ticks);

            AdventureWorksDataContext adventureWorks2  =   new  AdventureWorksDataContext();
            DateTime d3  =  DateTime.Now;
            var q2  =  adventureWorks2.WorkOrders.GetNewBindingList();
            DateTime d4  =  DateTime.Now;
            Console.WriteLine(d4.Ticks  -  d3.Ticks);

        输出：
         10140000
        6708000