{"id":5233,"date":"2007-11-06T18:55:00","date_gmt":"2007-11-06T18:55:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2007\/11\/06\/linq-cookbook-recipe-10-pre-compiling-queries-for-performance-doug-rothaus\/"},"modified":"2024-07-05T14:39:05","modified_gmt":"2024-07-05T21:39:05","slug":"linq-cookbook-recipe-10-pre-compiling-queries-for-performance-doug-rothaus","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/linq-cookbook-recipe-10-pre-compiling-queries-for-performance-doug-rothaus\/","title":{"rendered":"LINQ Cookbook, Recipe 10: Pre-compiling Queries for Performance (Doug Rothaus)"},"content":{"rendered":"

Ingredients:<\/p>\n

<\/font><\/span><\/b><\/p>\n

·         <\/span><\/span><\/span>Visual Studio 2008 (Beta2 or Higher)<\/span><\/p>\n

<\/span><\/font><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Categories:<\/span><\/b> LINQ to SQL<\/p>\n

<\/span><\/font><\/p>\n

<\/p>\n

 <\/font><\/p>\n

<\/span><\/p>\n

Introduction:<\/p>\n

<\/font><\/span><\/b><\/p>\n

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.<\/p>\n

<\/font><\/span><\/p>\n

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

<\/font><\/span><\/p>\n

Dim query As<\/span> Func(Of<\/span> NorthwindDataContext, Customer, IQueryable(Of<\/span> Order)) = _<\/p>\n

<\/span><\/p>\n

    <\/span>System.Data.Linq.CompiledQuery.Compile( _<\/p>\n

<\/span><\/p>\n

       <\/span>Function<\/span>(database As<\/span> NorthwindDataContext, cust As<\/span> Customer) _<\/p>\n

<\/span><\/p>\n

           <\/span>From<\/span> order In<\/span> database.Orders _<\/p>\n

<\/span><\/p>\n

           <\/span>Where<\/span> order.Customer Is<\/span> cust)<\/span><\/p>\n

<\/p>\n

<\/span> <\/p>\n

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.<\/p>\n

<\/font><\/span><\/p>\n

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.<\/p>\n

<\/font><\/span><\/p>\n

Instructions:<\/font><\/span><\/b><\/p>\n

<\/p>\n

<\/font><\/span><\/b> <\/p>\n

·         <\/span><\/span><\/span>Create a Win<\/font><\/span>dows Forms Application with a connection to the Northwind database. Add a LINQ to SQL Classes item named Northwind.dbml<\/b> 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 <\/font><\/span>LINQ Cookbook Recipe 6: Your first LINQ application using Northwind<\/font><\/span><\/a>.<\/p>\n

<\/font><\/span><\/p>\n

·         <\/span><\/span><\/span>From the Toolbox<\/span><\/b>, drag a ListBox<\/b> control (to display customer information), a DataGridView<\/b> control (to display order information), and a Label<\/b> control (to display timer information) onto the form. Resize the form and controls as needed.<\/p>\n

<\/font><\/span><\/p>\n

·         <\/span><\/span><\/span>Double-click the Form<\/b> to edit the application code.<\/p>\n

<\/font><\/span><\/p>\n

·         <\/span><\/span><\/span>Replace the default Form1 <\/b>class with the following code that defines class-level variables and the Form1.Load<\/strong> event handler:<\/p>\n

<\/font><\/span><\/p>\n

<\/p>\n

    Imports<\/p>\n

<\/font> System.Data.Linq<\/font><\/p>\n

    Public<\/p>\n

<\/font> <\/font>Class<\/font> Form1<\/p>\n

\n

<\/font>    <\/span>‘ Variable to hold the pre-compiled query.<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Private<\/span> orderQuery As<\/span> Func(Of<\/span> NorthwindDataContext, Customer, IQueryable(Of<\/span> Order))<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

    <\/span>‘ LINQ to SQL DataContext object for Northwind.<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Private<\/span> db As<\/span> New<\/span> NorthwindDataContext<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

    <\/span>‘ Timer to show performance difference.<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Dim<\/span> queryTimer As<\/span> New<\/span> Stopwatch<\/span><\/p>\n

    <\/span>Dim<\/span> slowestTime As<\/span> Long?<\/font><\/span><\/span><\/p>\n

    <\/span>Dim<\/span> currentTime As<\/span> Long<\/p>\n

<\/span><\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

    <\/span>Private<\/span> Sub<\/span> Form1_Load() Handles<\/span> MyBase<\/span>.Load<\/p>\n

<\/span><\/p>\n

        <\/span>‘ Get the list of Customers and bind it to ListBox1.<\/p>\n

<\/span><\/span><\/p>\n

        <\/span>ListBox1.DataSource = From<\/span> cust In<\/span> db.Customers _<\/p>\n

<\/span><\/p>\n

                              <\/span>Select<\/span> cust _<\/p>\n

<\/span><\/p>\n

                              <\/span>Order<\/span> By<\/span> cust.CompanyName<\/p>\n

<\/span><\/p>\n

        <\/span>ListBox1.DisplayMember = “CompanyName”<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>End<\/span> Sub<\/span><\/span><\/p>\n

<\/span><\/span> <\/p>\n

End Class<\/span><\/span><\/p>\n

<\/p>\n

<\/span><\/span> <\/p>\n

·         <\/span><\/span><\/span>After the Form.Load<\/b> event, add the following <\/font><\/span>GetOrders<\/span> method to retrieve orders for a customer.<\/p>\n

<\/font><\/span><\/p>\n

    <\/span>Private<\/span> Function<\/span> GetOrders(ByVal<\/span> selectedCustomer As<\/span> Customer) As<\/span> List(Of<\/span> Order)<\/span><\/p>\n

<\/p>\n

<\/span> <\/p>\n

        <\/span>‘ If the query for orders has not been compiled yet, compile it. Otherwise, <\/p>\n

<\/span><\/span><\/p>\n

        <\/span>‘ use the compiled query.<\/span><\/p>\n

<\/p>\n

<\/span> <\/p>\n

        <\/span>If<\/span> orderQuery Is<\/span> Nothing<\/span> Then<\/p>\n

<\/span><\/span><\/p>\n

            <\/span>orderQuery = CompiledQuery.Compile( _<\/p>\n

<\/span><\/p>\n

                <\/span>Function<\/span>(database As<\/span> NorthwindDataContext, cust As<\/span> Customer) _<\/p>\n

<\/span><\/p>\n

                    <\/span>From<\/span> order In<\/span> database.Orders _<\/p>\n

<\/span><\/p>\n

                    <\/span>Where<\/span> order.Customer Is<\/span> cust)<\/p>\n

<\/span><\/p>\n

   <\/span>     <\/span>End<\/span> If<\/p>\n

<\/span><\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

<\/p>\n

<\/span> <\/p>\n

        <\/span>‘ Execute the compiled query by calling the ToList method and <\/p>\n

<\/span><\/span><\/p>\n

        <\/span>‘ return the<\/span> results.<\/span><\/span><\/p>\n

<\/p>\n

<\/span><\/span> <\/p>\n

        <\/span>Return<\/span> orderQuery(db, selectedCustomer).ToList()<\/p>\n

<\/span><\/p>\n

    <\/span>End<\/span> Function<\/span><\/span><\/p>\n

<\/p>\n

<\/span><\/span> <\/p>\n

·         <\/span><\/span><\/span>After the <\/font><\/span>GetOrders<\/span> method, add the following event handler for the ListBox.SelectedIndexChanged<\/b> event. The event handler will pass the current selected customer to the <\/font><\/span>GetOrders<\/span> 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.<\/p>\n

<\/font><\/span><\/p>\n

    <\/span>Private<\/span> Sub<\/span> ListBox1_SelectedIndexChanged() Handles<\/span> ListBox1.SelectedIndexChanged<\/p>\n

<\/span><\/p>\n

        <\/span>queryTimer.Reset()<\/p>\n

<\/span><\/p>\n

        <\/span>queryTimer.Start()<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

        <\/span>‘ If a customer has been selected, get the list of orders for the customer<\/p>\n

<\/span><\/span><\/p>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

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, […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[192,195],"tags":[59,93,94,117,166],"class_list":["post-5233","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-visual-basic","tag-doug-rothaus","tag-linq-cookbook","tag-linqvb9","tag-orcas","tag-vb2008"],"acf":[],"blog_post_summary":"

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, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=5233"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5233\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=5233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=5233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=5233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}