locked
server-side enforced page size also adds .OrderBy(e => e.KeyField)...? RRS feed

  • Question

  • I added server side paging by calling SetEntityPageSize(queryPropName, size) in the service initializer for a query property that can potentially return a very large set of data. That correctly adds a .Take(size) if I hit the service with just the name of the IQueryable property. However, it seems like it also adds a .OrderBy(e => e.keyfield) for the field that is defined as the DataServiceKey for the entity type in question. This has some unwanted side-effects performance-wise since the underlying query provider then tries to apply a sort... Is it possible to make dataservices based on the reflection provider do paging without adding any orderby..?  Or did I miss something basic again?  (again, dataservices/odata is a new playing field for me so bear with me if I'm doing something backwards here... :) )

    (I filed a connect item on this too: https://connect.microsoft.com/data/feedback/details/532810/wcf-dataservice-an-unwanted-orderby-is-added-to-the-iqueryable )
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Thursday, February 11, 2010 12:50 PM

Answers

  • You can always do the .Skip(n).Take(m) paging from the client. (we refer to this as client side paging). You can set a server side paging limit for performance reasons, but until the client asks for something less than that using Skip(n).Take(m) pattern, this should work.

    Hope this helps.
    Thanks
    Pratik


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 12, 2010 5:01 PM
    Moderator
  • You can always do the .Skip(n).Take(m) paging from the client. (we refer to this as client side paging). You can set a server side paging limit for performance reasons, but until the client asks for something less than that using Skip(n).Take(m) pattern, this should work.

    Hope this helps.
    Thanks
    Pratik


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thanks. Yes, client side paging would do it but in this particular case I'm just the publisher, not the client, so I need to do something server-side to avoid having people or search engines or remote apps do db-killer-requests (the underlying dataset is fairly large). The service will be put up on a public website with links to access the OData version from the normal 'user-content' pages. I guess exposing though service operations is the best way to go (this is what I did originally, but then I really wanted to have navigation properties within the data set to get to metadata etc so fell back to IQueryable properties).

    Anyways, please consider additional config options and/or extensibility points to give service authors more control in future versions...
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Saturday, February 13, 2010 1:12 AM

All replies

  • Hi,

    Unfortunately the OrderBy is necessary. The reason is that when the service returns the first page it also needs to return a link to the next page. This link contains so called "skiptoken" which identifies where the next page should start. The way this works is that we take the value of the key property for the last item on the page being returned and use that as the skip token. When the client then asks for the next page we get that value from the skiptoken and use it to construct a query which will start from that place on.
    So for example the first page is returned running a query like this entities.OrderBy(e => e.key).Take(4). Then the second page is retrieved by a query like entities.Where(e => e.key > 123).OrderBy(e => e.key).Take(4), where 123 is the value of the key on the last entity returned by the first page. In order for this to work the result set must be ordered.
    Note that we didn't use the simple paging approach where the second page would be something like entities.Skip(4).Take(4). The reason for that is that we wanted the paging to work even if the entity set changes underneath. With this simple approad it could happen that we would either skip some entities or return some of them twice if the underlying entity set changed.
    The approach with OrderBy and Where is resilient to the changes of the entity set and has some nicely defined behavior in these cases.

    There's also the assumption, that underlying storage will be able to sort by the key property rather fast as it is usually indexed.
    So to answer your question, there's no simple way to do paging on reflection provider without orderby.

    Thanks,
    Vitek Karas [MSFT]
    Thursday, February 11, 2010 1:13 PM
    Moderator
  • Thanks. I think it would be a good idea to provide a way to fall back to .Skip(n).Take(m) paging. The reason for that is that even if the key field is indexed, there are situations where it will still have a negative performance impact.

    In my particular case, the underlying data source is SQL Server and the column is the primary key in one of the tables involved. However, there are also 8 other tables involved and due to the main reducing filter criteria being on four [much smaller] tables that is one table away from the main table, the query will have to go through a significant amount of data in order to join on the PK on the data table...   ...the effect is that the SQL query will time out with an "order by" at the end but will run sub-second without the orderby...
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Thursday, February 11, 2010 1:46 PM
  • You can always do the .Skip(n).Take(m) paging from the client. (we refer to this as client side paging). You can set a server side paging limit for performance reasons, but until the client asks for something less than that using Skip(n).Take(m) pattern, this should work.

    Hope this helps.
    Thanks
    Pratik


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 12, 2010 5:01 PM
    Moderator
  • You can always do the .Skip(n).Take(m) paging from the client. (we refer to this as client side paging). You can set a server side paging limit for performance reasons, but until the client asks for something less than that using Skip(n).Take(m) pattern, this should work.

    Hope this helps.
    Thanks
    Pratik


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thanks. Yes, client side paging would do it but in this particular case I'm just the publisher, not the client, so I need to do something server-side to avoid having people or search engines or remote apps do db-killer-requests (the underlying dataset is fairly large). The service will be put up on a public website with links to access the OData version from the normal 'user-content' pages. I guess exposing though service operations is the best way to go (this is what I did originally, but then I really wanted to have navigation properties within the data set to get to metadata etc so fell back to IQueryable properties).

    Anyways, please consider additional config options and/or extensibility points to give service authors more control in future versions...
    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Saturday, February 13, 2010 1:12 AM