Queston on how LINQ to SQL executes its queries RRS feed

  • General discussion

  • Hi, I am writing a program in VB.NET (2010) that is using LINQ queries to pull information from a SQL database (SQL Azure, to be specific).

    The problem is that these queries run SO SLOW. It takes 3 to 4 seconds to get through the code. And my suspicion is that when I write the query, it does not execute the query immediately, but instead executes the query each time it is referenced. See the below code for an example:


    Dim CurRecord = From Transaction In db.Transactions.AsEnumerable() _
              Where Transaction.ID = ThisID _
              Select Transaction
    If Not CurRecord.Count = 0 Then
       If CurRecord.Count > 1 Then
         'Process Some Code
         For x As Int32 = 0 to 10000 Step 1
            If CurRecord.FirstOrDefault.ID = x Then
              'Process some other code
            End If
         Next x
         If CurRecord.FirstOrDefault.ItemA = "Y" Then
            'Do Something
            'Do something else
         End If
       End If
    End If

    I have this feeling that it is executing the query once each time it hits the Count statements, then it is executing the query 10000 times during the loop, then again when it gets the value for the ItemA field. Is this true?

    The parts of the code where it says "Process some code" or "Do Something", etc., are VERY SMALL amounts of code, like change a label on the screen. Nothing that should hold this process up. So why does it take sometimes between 1 and 5 seconds to execute the code?

    If this is the case, what are my other options? I used to write SQL commands using an OdbcConnection, and fill a datatable with the results. Then I would use the datatable object in the above code. This would make the above code run in about a tenth of a second.

    I am restricted to using LINQ to SQL in this new project, and I cannot use the old OdbcConnections, or else I would! Any suggestions?

    Friday, September 17, 2010 3:09 PM

All replies

  • Friend, I'm not a VB programmer, so sugestion about the code are off the table.  However in order to speed this up you could load the query into a list, in c# there is a ToList() that used in the query will execute it on the spot and put the result in a list.  This will prevent additional trips to the db.

    There should be something like that in vb.


    Friday, September 17, 2010 3:51 PM
  • Hi Greg,

    "Whether you write a query as a high-level query expression or build one out of the individual operators, the query that you write is not an imperative statement executed immediately. It is a description. For example, in the declaration below the local variable q refers to the description of the query not the result of executing it.

    Dim q = From cust In db.Customers _
                           where cust.City = "London"
    For Each cust  In q

    The actual type of q in this instance is IQueryable<Customer>. It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the foreach statement causes the execution to occur.

    Therefore, it follows that if a query is enumerated twice it will be executed twice.

    Dim londonCustomers = From cust In db.Customers _
                           where cust.City = "London"
    ' Execute first time
    For Each cust In londonCustomers
    ' Execute second time
    For Each cust In londonCustomers

    This behavior is known as deferred execution. Just like with an ADO.NET command object it is possible to hold onto a query and re-execute it.

    Of course, application writers often need to be very explicit about where and when a query is executed. It would be unexpected if an application were to execute a query multiple times simply because it needed to examine the results more than once. For example, you may want to bind the results of a query to something like a DataGrid. The control may enumerate the results each time it paints on the screen.

    To avoid executing multiple times convert the results into any number of standard collection classes. It is easy to convert the results into a list or array using the standard query operators ToList() or ToArray().

    Dim londonCustomers = From cust In db.Customers _
                           where cust.City = "London"
    ' Execute once using ToList() or ToArray()
    Dim londonCustList = londonCustomers.ToList()
    ' Neither of these iterations re-executes the query
    For Each cust In londonCustList
    For Each cust In londonCustList

    For more information, please refer to MSDN document.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 21, 2010 8:50 AM
  • Alex & Serguey,

    Thank you. That was exactly what I was looking for. The code went from taking between 17-35 seconds to run, down to 1-2 seconds.


    Wednesday, September 22, 2010 8:32 PM