The One With

OData Provider for XPO – Using Server Mode to handle Huge Datasets

OData + !summary ExtensionsThe most exciting part of OData is its simplicity and at the same time its flexibility. The idea of accessing data over HTTP using a well defined RESTful API, and the fact that by doing so we completely remove direct access to the database from our clients, is indeed very powerful!

So what is Server Mode?

Simply put, Server Mode is a concept of our Grid Controls that, if enabled, instead of fetching the entire data set, the grid only fetches the number of records relevant to the current view. Similarly, sorting, filtering, summary operations and group summaries are delegated to the underlying IQueryable<T>. This is why !summary was very important.

Let’s have a closer look at what the grid actually does. I have a resource set “ServerModeItems” on the server that exposes a SQL Server table with 100,000 records. I then bind it to a WinForms Grid Control is Server Mode like so:

this.gridControl1.DataSource = ServerModeSource2.Create<ServerModeItem>(
    new Uri("http://localhost:59906/ServerMode.svc"),
    "ServerModeItems",
    "OID");

Note: Never mind the name ServerModeSource2. It’s a variation of LinqServerModeDataSource designed specifically for handling IQueryables that implement IServerMode2. The actual name will change once this goes to a full CTP. Suggestions are welcome :). Although binding directly to LinqServerModeDataSource and AtomPubQuery<> will work just fine, the ServerModeSource2 is able to utilize a custom !contains extension which is explained below.

Under the Hood

Initial Load

The initial grid data load is incredibly lightweight: Query.OrderBy(it => it.OID).Take(128).Select() which than translates into a ?$top=128&$orderby=OID request.

WinForms Grid Control in Server Mode with an OData data source.

Sorting by the Sent column for example will execute the following: ?$top=128&$orderby=Sent desc,OID desc 

Scrolling

As I start scrolling down, the Grid (the Data Controller actually) will start requesting more data from our IQueryable. It will do this in 2 steps.

Step 1: Fetch the Keys for a specific range.

  • LINQ: Query.OrderBy(e => e.OID).Skip(100).Take(768).Select(e => e.OID)
  • URL: ?$skip=100&$top=768&$orderby=OID

The range for key selection is not arbitrary. It is calculated and optimized on the fly as you scroll based on how much time it takes to fetch N number records vs. k * N number of records. If getting 2 * N keys takes the same time as getting N keys then we’ll get 2 * N keys.

Step 2: Fetching records for the keys.

Once the keys are selected, the Grid will request the actual data rows for the visible range.

  • LINQ: Query.Where(e = > e.OID == @id1 || e.OID == @id2 || e.OID == @id3 etc…)
  • URL: ?$filter=OID eq @id1 or OID eq @id2 or OID eq @id3…

Grouping

When grouping is performed, we first run the GroupBy query:

  • LINQ: GroupBy(e => e.Sent).OrderBy(e => e.Key).Select([Key+Summaries])
  • URL: ?!summary=Sent asc,[Summaries]

and than when we expand a group, the process of initial load and scrolling repeats itself but this time only for the current group.

  • LINQ: Query.Where(e => (e.Sent == 4/6/2010 12:00:00 AM)).OrderBy(e => e.Sent).ThenBy(e => e.OID).Take(128)
  • URL: ?$filter=(Sent eq datetime'2010-04-06T00:00:00')&$top=128&$orderby=Sent,OID

WinForms Grid Control in Server Mode with an OData data source.

Optimizations

Of course, a big trade off of having to fetch data over HTTP is that it is slower than if you had a DB right next to you. Utilizing the Grid in Server Mode solves this, but there is still room for improvements. One improvement that we are thinking about for 10.2 release or right after, is a smart Data Controller that will perform key fetches in the background thus giving us a very smooth scrolling experience.

Other optimization we can do at the query level is to delay load the record fetches (see Step 2). This means that when data is needed for a visible range, we return fakes and issue a fetch request asynchronously. Than when the response comes back, we notify the Grid to repaint itself.

Dealing with URL Length Limitations

Look back at Step 2 above:

URL: ?$filter=OID eq @id1 or OID eq @id2 or OID eq @id3…

This URI is shortened for readability. The actual query string can get really big and ugly, and sooner or later you will run into URL Length Limitations, which you will need to configure for both .NET HTTP Runtime and IIS. Making the acceptable query string sizes bigger will only solve one problem. You will soon run into recursion limits set by the Data Service Library. Because filter expressions are recursive and when they are generated straight out of LINQ and are not simplified they look like this:

Query.Where(it => (((((((((((((((((((((((((((((((((((((((((((((((((((((((it.OID == 6792) OrElse (it.OID == 6818)) OrElse (it.OID == 6882)) OrElse (it.OID == 6959)) OrElse (it.OID == 7008)) OrElse (it.OID == 7038)) OrElse (it.OID == 7045)) OrElse (it.OID == 7064)) OrElse (it.OID == 7089)) OrElse (it.OID == 7124)) OrElse (it.OID == 7167)) OrElse (it.OID == 7176)) OrElse (it.OID == 7268)) OrElse (it.OID == 7270)) OrElse (it.OID == 7306)) OrElse (it.OID == 7349)) OrElse (it.OID == 7444)) OrElse (it.OID == 7560)) OrElse (it.OID == 7573)) OrElse (it.OID == 7607)) OrElse (it.OID == 7616)) OrElse (it.OID == 7649)) OrElse (it.OID == 7726)) OrElse (it.OID == 7755)) OrElse (it.OID == 7786)) OrElse (it.OID == 7862)) OrElse (it.OID == 8018)) OrElse (it.OID == 8069)) OrElse (it.OID == 8074)) OrElse (it.OID == 8104)) OrElse (it.OID == 8141)) OrElse (it.OID == 8215)) OrElse (it.OID == 8329)) OrElse (it.OID == 8371)) OrElse (it.OID == 8439)) OrElse (it.OID == 8590)) OrElse (it.OID == 8643)) OrElse (it.OID == 8660)) OrElse (it.OID == 8688)) OrElse (it.OID == 8792)) OrElse (it.OID == 8939)) OrElse (it.OID == 9033)) OrElse (it.OID == 9042)) OrElse (it.OID == 9067)) OrElse (it.OID == 9123)) OrElse (it.OID == 9143)) OrElse (it.OID == 9193)) OrElse (it.OID == 9287)) OrElse (it.OID == 9329)) OrElse (it.OID == 9341)) OrElse (it.OID == 9387)) OrElse (it.OID == 9393)) OrElse (it.OID == 9502)) OrElse (it.OID == 9507)) OrElse (it.OID == 9542)))

AtomPubQuery<> solves this by:

  • 1: Optimizing the the resulting URL to avoid unneeded parentheses. 
  • 2: Putting the $filter criteria into the HTTP header X-Filter-Criteria
  • 3: Using a custom !contains extension designed specifically for fetching data by multiple keys. !contains=OID in (1,3,4,5,6 etc…)

Hope you got excited about all of this :). You can download the Pre CTP from http://xpo.codeplex.com/.

Cheers

Azret

Published Aug 16 2010, 04:28 PM by
Bookmark and Share

Comments

Azret Botash (DevExpress)

Sample App. is available for download http://bit.ly/cdXFHK

August 17, 2010 2:04 AM
LIVE CHAT

Chat is one of the many ways you can contact members of the DevExpress Team.
We are available Monday-Friday between 7:30am and 4:30pm Pacific Time.

If you need additional product information, write to us at info@devexpress.com or call us at +1 (818) 844-3383

FOLLOW US

DevExpress engineers feature-complete Presentation Controls, IDE Productivity Tools, Business Application Frameworks, and Reporting Systems for Visual Studio, along with high-performance HTML JS Mobile Frameworks for developers targeting iOS, Android and Windows Phone. Whether using WPF, ASP.NET, WinForms, HTML5 or Windows 10, DevExpress tools help you build and deliver your best in the shortest time possible.

Copyright © 1998-2017 Developer Express Inc.
All trademarks or registered trademarks are property of their respective owners