Optimizing LINQ Queries with LiveLinq

In this section, we will show a sample that illustrates how LiveLinq can optimize queries that use the where operator to filter data.

To start, follow these steps:

Create a new WinForms project
Add a reference to the C1.LiveLinq.dll assembly
Use the Data | Add New DataSource menu and add a reference to the NORTHWND.MDF database. Accept all the default options offered by the wizard, and pick all the tables in the database.

At this point, your project should look like this

Next, double-click the form and add the following code:

	Copy Code
// declare northwind DataSet NORTHWNDDataSet _ds = new NORTHWNDDataSet(); private void Form1_Load(object sender, EventArgs e) { // load data into DataSet new NORTHWNDDataSetTableAdapters.CustomersTableAdapter() .Fill(_ds.Customers); new NORTHWNDDataSetTableAdapters.OrdersTableAdapter() .Fill(_ds.Orders); }

Copy Code

// declare northwind DataSet
NORTHWNDDataSet _ds = new NORTHWNDDataSet();
private void Form1_Load(object sender, EventArgs e)
{
  // load data into DataSet
  new NORTHWNDDataSetTableAdapters.CustomersTableAdapter()
    .Fill(_ds.Customers);
  new NORTHWNDDataSetTableAdapters.OrdersTableAdapter()
    .Fill(_ds.Orders);
}

This code declares an ADO.NET DataSet and loads some data into it.

Now that we have some data, let’s do something with it.

Add a button to the form, double-click it, and then enter the following code:

	Copy Code
private void button1_Click(object sender, EventArgs e) { // get reference to source data var customers = _ds.Customers; var orders = _ds.Orders; // find all orders for the first customer var q = from o in orders where o.CustomerID == customers[0].CustomerID select o; // benchmark the query (execute 1000 times) var start = DateTime.Now; int count = 0; for(int i = 0; i < 1000; i++) { foreach (var d in q) count++; } Console.WriteLine("LINQ query done in {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds); }

Copy Code

private void button1_Click(object sender, EventArgs e) 
{
  // get reference to source data
  var customers = _ds.Customers;
  var orders = _ds.Orders;
  // find all orders for the first customer
  var q =
    from o in orders
    where o.CustomerID == customers[0].CustomerID
    select o;
  // benchmark the query (execute 1000 times)
  var start = DateTime.Now;
  int count = 0;
  for(int i = 0; i < 1000; i++)
  {
    foreach (var d in q)
      count++;
  }
  Console.WriteLine("LINQ query done in {0} ms",
     DateTime.Now.Subtract(start).TotalMilliseconds);
}

The code creates a simple LINQ query that enumerates all orders for the first customer in the database, then executes the query 1000 times and reports how long the process took.

If you run the project now and click the button, the Visual Studio output window should show something like this:

LINK query done in 262 ms

Now let us optimize this query with LiveLinq.

Start by adding the following using statements to the top of the code:

using C1.LiveLinq;

using C1.LiveLinq.AdoNet;

Next, add a second button to the form, double-click it and enter the following code:

	Copy Code
private void button2_Click(object sender, EventArgs e) { // get reference to source data var customers = _ds.Customers; var orders = _ds.Orders; // find all orders for the first customer var q = from o in orders.AsIndexed() where o.CustomerID.Indexed() == customers[0].CustomerID select o; // benchmark the query (execute 1000 times) var start = DateTime.Now; int count = 0; for(int i = 0; i < 1000; i++) { foreach (var d in q) count++; } Console.WriteLine("LiveLinq query done in {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds); }

Copy Code

private void button2_Click(object sender, EventArgs e) 
{
  // get reference to source data
  var customers = _ds.Customers;
  var orders = _ds.Orders;
  // find all orders for the first customer
  var q =
    from o in orders.AsIndexed()
    where o.CustomerID.Indexed() == customers[0].CustomerID
    select o;
  // benchmark the query (execute 1000 times)
  var start = DateTime.Now;
  int count = 0;
  for(int i = 0; i < 1000; i++)
  {
    foreach (var d in q)
    count++;
  }
  Console.WriteLine("LiveLinq query done in {0} ms",
     DateTime.Now.Subtract(start).TotalMilliseconds);
}

The code is almost identical to the plain LINQ version we used before. The only differences are:

We use the AsIndexed method to convert the orders table into a LiveLinq indexed data source,
We use the Indexed method to indicate to LiveLinq that it should index the data source by customer id,

AND

The output message changed to show we are using LiveLinq now.

If you run the project again and click both buttons a few times, you should get something like this:

LINQ query done in 265 ms

LINQ query done in 305 ms

LINQ query done in 278 ms

LiveLinq query done in 124 ms

LiveLinq query done in 7 ms

LiveLinq query done in 2 ms

Notice how the plain LINQ query takes roughly the same amount of time whenever it executes. The LiveLinq query, on the other hand, is about twice as fast the first time it executes, and about one hundred times faster for all subsequent runs. This level of performance gain is typical for this type of query.

This happens because LiveLinq has to build the index when the query is executed for the first time. From then on, the same index is reused and performance improves dramatically. Note that the index is automatically maintained, so it is always up-to-date even when the underlying data changes.

In this example, the index was created when the Indexed method executed for the first time. You can also manage indexes using code if you need the extra control. For example, the code below declares an indexed collection and explicitly adds an index on the CustomerID field:

  var ordersIndexed = _ds.Orders.AsIndexed();

  ordersIndexed.Indexes.Add(o => o.CustomerID);

Note that the indexes are associated with the source data, and are kept even if the collection goes out of scope. If you executed the code above once, the index would remain active even after the ordersIndexed collection went out of scope.

Note also that the AsIndexed method is supported only for collections that provide change notifications (LiveLinq has to monitor changes in order to maintain its indexes). This requirement is satisfied for all common collections used in WinForms and WPF data binding. In particular, AsIndexed can be used with DataTable, DataView, BindingList<T>, ObservableCollection<T>, and LINQ to XML collections.

The LiveLinq installation includes a sample called LiveLinqQueries that contains many other examples with benchmarks, and includes queries against plain CLR objects, ADO.NET, and XML data.

Summarizing, LiveLinq can significantly optimize queries that search for any type of data that can be sorted. For example, searching for a specific customer or product, or listing products within a certain price range. These are typical queries that can be easily optimized to perform about a hundred times faster that they would using plain LINQ.