locked
EF 4.0 Is adding a non-optimal Order By clause in the generated SQL. How can I change it? RRS feed

  • Question

  • I have an entity defined in an EF 4.0 model that is based on a view.  The view lays over a table with about 18 million rows of data.  I have selected the 4 deterministic properties of the entity as a compound Entity key for this entity.  I have exposed access to the model containing this view via an Odata WCF data service.  When I make a basic query against this view (the WCF Data service is set to limit results like so "config.SetEntitySetPageSize("*", 100)"):

                var fcbs = this.iBenchmarkCostContext.FtCostBenchmarks.ToArray();
    

     This is the query that i see in my profiling tool:

    SELECT TOP (100) [Extent1].[MonthBeginDt]                     AS [MonthBeginDt],
                     [Extent1].[dmCostBenchmarkKey]               AS [dmCostBenchmarkKey],
                     [Extent1].[dmProductKey]                     AS [dmProductKey],
                     [Extent1].[IsImputedFlg]                     AS [IsImputedFlg],
                     [Extent1].[ProjectedCopayPerRxAmt]           AS [ProjectedCopayPerRxAmt],
                     [Extent1].[ProjectedPricePerQtyAmt]          AS [ProjectedPricePerQtyAmt],
                     [Extent1].[ProjectedQtyPerRxQty]             AS [ProjectedQtyPerRxQty],
                     [Extent1].[ProjectedRxCnt]                   AS [ProjectedRxCnt],
                     [Extent1].[AvgPriceRxAvgPriceQtyDenominator] AS [AvgPriceRxAvgPriceQtyDenominator],
                     [Extent1].[AvgQtyDenominator]                AS [AvgQtyDenominator],
                     [Extent1].[AvgCopayDenominator]              AS [AvgCopayDenominator],
                     [Extent1].[ProjectedTotalCostAmt]            AS [ProjectedTotalCostAmt],
                     [Extent1].[AllowedRxCnt]                     AS [AllowedRxCnt],
                     [Extent1].[CopayRxCnt]                       AS [CopayRxCnt],
                     [Extent1].[TotalAllowedAmt]                  AS [TotalAllowedAmt],
                     [Extent1].[TotalCopayAmt]                    AS [TotalCopayAmt],
                     [Extent1].[TotalCostPerUnitAmt]              AS [TotalCostPerUnitAmt],
                     [Extent1].[TotalUnitQty]                     AS [TotalUnitQty],
                     [Extent1].[RC]                               AS [RC]
    FROM   (SELECT [ftCostBenchmark].[MonthBeginDt]                     AS [MonthBeginDt],
                   [ftCostBenchmark].[dmCostBenchmarkKey]               AS [dmCostBenchmarkKey],
                   [ftCostBenchmark].[dmProductKey]                     AS [dmProductKey],
                   [ftCostBenchmark].[IsImputedFlg]                     AS [IsImputedFlg],
                   [ftCostBenchmark].[ProjectedCopayPerRxAmt]           AS [ProjectedCopayPerRxAmt],
                   [ftCostBenchmark].[ProjectedPricePerQtyAmt]          AS [ProjectedPricePerQtyAmt],
                   [ftCostBenchmark].[ProjectedQtyPerRxQty]             AS [ProjectedQtyPerRxQty],
                   [ftCostBenchmark].[ProjectedRxCnt]                   AS [ProjectedRxCnt],
                   [ftCostBenchmark].[AvgPriceRxAvgPriceQtyDenominator] AS [AvgPriceRxAvgPriceQtyDenominator],
                   [ftCostBenchmark].[AvgQtyDenominator]                AS [AvgQtyDenominator],
                   [ftCostBenchmark].[AvgCopayDenominator]              AS [AvgCopayDenominator],
                   [ftCostBenchmark].[ProjectedTotalCostAmt]            AS [ProjectedTotalCostAmt],
                   [ftCostBenchmark].[AllowedRxCnt]                     AS [AllowedRxCnt],
                   [ftCostBenchmark].[CopayRxCnt]                       AS [CopayRxCnt],
                   [ftCostBenchmark].[TotalAllowedAmt]                  AS [TotalAllowedAmt],
                   [ftCostBenchmark].[TotalCopayAmt]                    AS [TotalCopayAmt],
                   [ftCostBenchmark].[TotalCostPerUnitAmt]              AS [TotalCostPerUnitAmt],
                   [ftCostBenchmark].[TotalUnitQty]                     AS [TotalUnitQty],
                   [ftCostBenchmark].[RC]                               AS [RC]
            FROM   [dbo].[ftCostBenchmark] AS [ftCostBenchmark]) AS [Extent1]
    ORDER  BY [Extent1].[MonthBeginDt] ASC,
              [Extent1].[dmCostBenchmarkKey] ASC,
              [Extent1].[dmProductKey] ASC,
              [Extent1].[IsImputedFlg] ASC

     

    Although I have not explicity requested any ordering, an order by clause is added that includes the fields included in the compound entity key defined for the Entity.  Execution of this query takes an inordinate amount of time and generates page locks on the database.  Removal of the Order By from the query in the MSSQL environment returns results in less than a milisecond.

    So my question is:

    How can I stop EF from adding that order by clause to the query?

     

     Edit:

    It would appear that it is the WCF Data service used to access the model that is adding the Order by clause, not the EF Model itself.  If I create a unit test that references the EF Model assembly directly, rather than connecting via the WCF Data Service I have generated over the model, no order by clause is added.



    • Edited by David J Cohen Friday, January 27, 2012 9:23 PM
    • Moved by Alan_chenModerator Monday, January 30, 2012 6:27 AM (From:ADO.NET Entity Framework and LINQ to Entities)
    Friday, January 27, 2012 8:14 PM

Answers

  • Hi,

    WCF DS Adds the order by. This is necessary for the server driven paging to work correctly. The way the built-in server driven paging is implemented is this:

    Sort the results to have a stable ordering and return the first 10 results (if the page size is 10).

    In it include a next link which has the values of all the properties according to which the sort was performed of the last entity in the result set (so the 10th entity).

    When the next link is requested, sort the results using the same order by clause and append a filter which selects only entities with values greater than those of the last entity from the previous results.

    And so on.

    This algorithm absolutely relies on the stable ordering of the results across requests. Since WCF DS has no knowledge of the underlying provider, it has to request such ordering. The only relyable way to perform such ordering is to order by all the key properties, since those are guaranteed to be unique.

    It is possible to use a custom implementation of the server driven paging, but it's quite a lot of work. Usually this problem is resolved by adding the right indeces into the DB itself (most of the time this already works just fine since primary keys are usually indexed by default).

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by David J Cohen Monday, January 30, 2012 7:14 PM
    Monday, January 30, 2012 9:24 AM
    Moderator

All replies

  • Hi David,

    Welcome!

    Based on my testing, the order by will be append the query as you said. I do some researching, I found a blog here: http://blogs.msdn.com/b/astoriateam/archive/2010/02/02/server-paging-in-data-services.aspx

    I think you can make down the size of page to improve the performance and retrieve the rest of records by the code here:

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

    static void Main(string[] args) 
    { 
        //create the client context 
        NorthwindEntities nwsvc = new NorthwindEntities( 
        new Uri("http://localhost:56304/nw.svc/"));
    
        //create a query to get the customers 
        var query = nwsvc.Customers.Execute(); 
    
        //load the customers into a list 
        List<Customers> custList = query.ToList(); 
    
        //check for the continuation token 
        while (((QueryOperationResponse)query).GetContinuation() != null) 
        { 
            //query for the next partial set of customers 
            query = nwsvc.Execute<Customers>( 
                ((QueryOperationResponse)query).GetContinuation().NextLinkUri 
                ); 
    
            //Add the next set of customers to the full list 
            custList.AddRange(query.ToList()); 
    
            //print the current count of customers retrieved 
            Console.WriteLine(custList.Count); 
        } 
    
        //print the count of customers retrieved 
        Console.WriteLine(custList.Count); 
        Console.Read(); 
    }
    
    

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

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 30, 2012 7:34 AM
    Moderator
  • Hi,

    WCF DS Adds the order by. This is necessary for the server driven paging to work correctly. The way the built-in server driven paging is implemented is this:

    Sort the results to have a stable ordering and return the first 10 results (if the page size is 10).

    In it include a next link which has the values of all the properties according to which the sort was performed of the last entity in the result set (so the 10th entity).

    When the next link is requested, sort the results using the same order by clause and append a filter which selects only entities with values greater than those of the last entity from the previous results.

    And so on.

    This algorithm absolutely relies on the stable ordering of the results across requests. Since WCF DS has no knowledge of the underlying provider, it has to request such ordering. The only relyable way to perform such ordering is to order by all the key properties, since those are guaranteed to be unique.

    It is possible to use a custom implementation of the server driven paging, but it's quite a lot of work. Usually this problem is resolved by adding the right indeces into the DB itself (most of the time this already works just fine since primary keys are usually indexed by default).

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by David J Cohen Monday, January 30, 2012 7:14 PM
    Monday, January 30, 2012 9:24 AM
    Moderator
  • "Usually this problem is resolved by adding the right indeces into the DB itself (most of the time this already works just fine since primary keys are usually indexed by default)."

     

    I beleive the answer to this issue will be in adding additional indexes on the views that are used by this model.  Unfortunately that responsibility lies in another department and I will have to wait on time and resources from the staff there to get these collections to perform reasonably.  Thank you for your responses.

    Monday, January 30, 2012 7:14 PM