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

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

  • Comments 5

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

        ' and bind it to DataGridView1.

 

        If ListBox1.SelectedValue IsNot Nothing Then

            DataGridView1.DataSource = GetOrders(ListBox1.SelectedValue)

        End If

 

        queryTimer.Stop()

 

        currentTime = queryTimer.ElapsedMilliseconds

 

        If slowestTime Is Nothing OrElse currentTime > slowestTime Then

            slowestTime = currentTime

        End If

 

        Label1.Text = "Elapsed Time: " & currentTime & "/" & slowestTime

    End Sub

 

Press F5 to see the code run. Click on different customers in the ListBox to see the performance difference using a pre-compiled query.

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • Sorry for offtopic, but... Why are we forced to write

    Dim data As Integer()()() = {New Integer()() {New Integer() {1, 2}, New Integer() {2, 3}}}

    instead of

    Dim data As Integer()()() = {{{1, 2},{2, 3}}}

    Isn't it typesafe?

  • It's not a type-safety issue. It's just syntax. VB doesn't support the second syntax example for jagged arrays.

  • Here's a summary of all the content the VB team members, including myself, have created for you on

  • Hello

    I am trying to do something similar in order to create a compiled query for reuse and using your code have created the same GetData function and variable using my own data context tables

    Private orderQuery As Func(Of HCADataContext, String, IQueryable(Of T_HCA_Suministro))

    Private Function GetData(ByVal pCity As String) As IQueryable(Of T_HCA_Suministro)

           ' 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 HCADataContext, pLocal As String) _

                       From data In db.T_HCA_Suministro

                       Where data.HCAPS_Localidad = pLocal

                       Select data)

               'From data In db.T_HCA_Suministro

               '      Where data.HCAPS_Localidad Is pLocal

               '      Select CUPS = data.HCAPS_CUPS, Localidad = data.HCAPS_Localidad, Provincia = data.T_Tipo_Provincias.Pr_Nombre)

           End If

           ' Execute the compiled query by calling the ToList method and

           ' return the results.

           Return orderQuery(db, pCity).ToList()

    End Function

    Call to populate grid is:

           If lbCity.SelectedValue IsNot Nothing Then

               DataGridView1.DataSource = GetData(lbCity.SelectedValue)

           End If

    With the ToList method I am receiving the error:

    No se puede convertir un objeto de tipo 'System.Collections.Generic.List`1[LINQ_Test_2.T_HCA_Suministro]' al tipo 'System.Linq.IQueryable`1[LINQ_Test_2.T_HCA_Suministro]'.

    If I remove the toList method I receive no error but the data is not displayed in the datagridview even though I can see that the datagridviews datasource contains the correct data.

    Any ideas where I might be going wrong?

  • Okay I have got this working by making the following changes:

    Return orderQuery(db, pCity)

    End Function

    Call to populate grid is:

          If lbCity.SelectedValue IsNot Nothing Then

              DataGridView1.DataSource = GetData(lbCity.SelectedValue).ToList()

          End If

Page 1 of 1 (5 items)