title: Async Task - read database data, got: 'System.Data.SqlClient.SqlException' The query processor could not start the necessary thread resources
body: <h2>I'm creating Async Tasks in a loop, The task has to read data from sql database.
  as the source data size gets bigger, I got this error: 
  
  <ul><li>The query processor could not start the necessary thread resources for parallel query execution.</li></ul>
  
  </h2>
  
  <p>here is my code:</p>
  
  <pre><code>
  For Each objLocDataTable In pLocationData
  
      If objLocDataTable IsNot Nothing AndAlso objLocDataTable.ISVALID Then
  
         listLocationELTTasks.Add(TaskEx.Run(
               Async Function() As Task
                      Dim resTable As System.Data.DataTable = Await mDBUtil.QueryAsync(strQry)
  
                      ....... 'do calculation works based on the query result
               End Function))
      END if
  Next
  
  
   Public Async Function QueryAsync(ByVal strSqlQuery As String) As Task(Of DataTable)
  
    Dim returnDataTable As DataTable = Nothing
  
    Dim nTriedNumber As Integer = 0, nMaxTryNumber = 10
    Dim bStop As Boolean = False
  
    While (Not bStop)
     Try
      Using dbConnection As New SqlConnection(mStrConn)
       Using dbComm As New SqlCommand(strSqlQuery, dbConnection)
  
        Await dbConnection.OpenAsync()
        dbComm.CommandType = CommandType.Text
  
        Dim readerTask As Task(Of SqlDataReader) = Task.Factory.FromAsync(AddressOf dbComm.BeginExecuteReader, AddressOf dbComm.EndExecuteReader, Nothing)
  
        returnDataTable = Await readerTask.ContinueWith(
         Function(pervTask)
          Dim resultTable As New DataTable()
          Dim reader As SqlDataReader = Nothing
          Try
           reader = pervTask.Result
           reader.Read()
           resultTable.Load(reader)
           bStop = True
          Catch ex As Exception
           System.Diagnostics.Debug.WriteLine(String.Format("Thread ID [{0}]: nTriedNumber [{1}], QueryAsync error: {2}", Threading.Thread.CurrentThread.ManagedThreadId, nTriedNumber, ex.Message))
          Finally
           If (reader IsNot Nothing) Then
            reader.Close()
           End If
           If (Not dbConnection.State = ConnectionState.Closed) Then
            dbConnection.Close()
           End If
          End Try
          Return resultTable
         End Function)
       End Using
      End Using
  
     Catch ex As Exception
      System.Diagnostics.Debug.WriteLine(String.Format("Not datareader error, nTriedNumber [{0}], QueryAsync error: {1}", nTriedNumber, ex.Message))
     Finally
      'could be shorten as if( returnDataTable Is Nothing or returnDataTable.Rows.Count() = 5) Then
       System.Diagnostics.Debug.WriteLine(String.Format("Thread ID [{0}]: QueryAsync is sleeping for 1 sec", Threading.Thread.CurrentThread.ManagedThreadId))
       System.Threading.Thread.Sleep(1000)
      End If
  
      If (nTriedNumber >= nMaxTryNumber) Then
       System.Diagnostics.Debug.WriteLine(String.Format("Thread ID [{0}]: Reach to max try number, failed to retrieve data", Threading.Thread.CurrentThread.ManagedThreadId))
       bStop = True
      End If
     End Try
    End While
  
    Return returnDataTable
   End Function
  </code></pre>
  
  <h3>
  As in function QueryAsync, I tried
  - close the db connection as quick as possible.
  - even sleep for a while
  none of them working
  </h3>
  
  <h2>
  Note:
  - the sql data source is about 50 million records, I can NOT load it into memory at once before the for loop to do in-memory linq query.
  
  I tried to replace the Task with Parrel.foreach, same result.
  
  what's the efficient way to read data from sql database in task.
  
  </h2>