LINQ Cookbook, Recipe 10: Pre-compiling Queries for Performance (Doug Rothaus)

Ingredients:

·         Visual Studio 2008 (Beta2 or Higher)

 

Categories: LINQ to SQL

 

Introduction:

As we add cookbook entries, we will include performance improvement tips where appropriate. Here’s a tip that you can use with LINQ to SQL to speed up queries that you will call numerous times: Pre-compiled queries. By pre-compiling a query, you perform the processing to compile the query once, and then skip the compile step every consecutive time that you run the query.

Pre-compiled queries are created using the Compile method of the CompiledQuery class in the System.Data.Linq namespace. You pass a Lambda expression that contains the variables used by your query (the DataContext object, filter variables, and so on) to the Compile method to create a compiled query as shown in the following example.

Dim query As Func(Of NorthwindDataContext, Customer, IQueryable(Of Order)) = _

    System.Data.Linq.CompiledQuery.Compile( _

       Function(database As NorthwindDataContext, cust As Customer) _

           From order In database.Orders _

           Where order.Customer Is cust)

 

The first time the query executes, the query will be compiled and stored in the specified variable. After that, the compiled query is used when the query executes.

In this recipe, you will create a classic master-detail application using the Northwind sample database. The “master” list will display the customers from the Northwind database using a LINQ query, and the “detail” list will display the orders for the selected customer using a pre-compiled query.

Instructions:

 

·         Create a Windows Forms Application with a connection to the Northwind database. Add a LINQ to SQL Classes item named Northwind.dbml and drag the Customers and Orders tables to the data classes design surface in the Object Relational Designer. For steps on creating an application that uses the Northwind database, see LINQ Cookbook Recipe 6: Your first LINQ application using Northwind.

·         From the Toolbox, drag a ListBox control (to display customer information), a DataGridView control (to display order information), and a Label control (to display timer information) onto the form. Resize the form and controls as needed.

·         Double-click the Form to edit the application code.

·         Replace the default Form1 class with the following code that defines class-level variables and the Form1.Load event handler:

    Imports

System.Data.Linq

    Public

Class Form1

    ‘ Variable to hold the pre-compiled query.

    Private orderQuery As Func(Of NorthwindDataContext, Customer, IQueryable(Of Order))

 

    ‘ LINQ to SQL DataContext object for Northwind.

    Private db As New NorthwindDataContext

 

    ‘ Timer to show performance difference.

    Dim queryTimer As New Stopwatch

    Dim slowestTime As Long?

    Dim currentTime As Long

 

    Private Sub Form1_Load() Handles MyBase.Load

        ‘ Get the list of Customers and bind it to ListBox1.

        ListBox1.DataSource = From cust In db.Customers _

                              Select cust _

                              Order By cust.CompanyName

        ListBox1.DisplayMember = “CompanyName”

    End Sub

 

End Class

 

·         After the Form.Load event, add the following GetOrders method to retrieve orders for a customer.

    Private Function GetOrders(ByVal selectedCustomer As Customer) As List(Of Order)

 

        ‘ If the query for orders has not been compiled yet, compile it. Otherwise,

        ‘ use the compiled query.

 

        If orderQuery Is Nothing Then

            orderQuery = CompiledQuery.Compile( _

                Function(database As NorthwindDataContext, cust As Customer) _

                    From order In database.Orders _

                    Where order.Customer Is cust)

        End If

 

 

        ‘ Execute the compiled query by calling the ToList method and

        ‘ return the results.

 

        Return orderQuery(db, selectedCustomer).ToList()

    End Function

 

·         After the GetOrders method, add the following event handler for the ListBox.SelectedIndexChanged event. The event handler will pass the current selected customer to the GetOrders method to call the pre-compiled query and retrieve the orders for that customer. Some simple timer code is added to show the difference between the first time the query executes, and the current execution time.

    Private Sub ListBox1_SelectedIndexChanged() Handles ListBox1.SelectedIndexChanged

        queryTimer.Reset()

        queryTimer.Start()

 

        ‘ If a customer has been selected, get the list of orders for the customer