时间戳

首页 > 解决方案 > （假设）异步查询等待结果

vb.net - （假设）异步查询等待结果

问题描述

我的目的是查询远程数据库，在许多（最多 5000 个）表中查询单个（但每个表不同）字段：

SELECT FOO FROM TABLEA WHERE ...
SELECT BAR FROM TABLEB WHERE ...

到目前为止，使用的代码是：

Public Class ValuesRepository
    Private connectionString As String
    Private BatchOdbcConnection As IDbConnection

    Public Sub New(connectionString As String)
        Me.connectionString = connectionString
        BatchOdbcConnection = New Odbc.OdbcConnection("DRIVER={AspenTech ODBC driver for Production Record Manager};" + connectionString)
    End Sub

    PublicFunction GetAllBatchValues(tagList As IEnumerable(Of BatchTag)) As IEnumerable(Of IEnumerable(Of Double?))
        Dim returnList As New List(Of IEnumerable(Of Double?))
        If tagList.Count = 0 Then Return returnList
        BatchOdbcConnection.Open()
        For Each batchTag In tagList
            Dim result As Double? = GetBatchList(batchTag.fieldName, batchTag.tableName)
            If result Is Nothing Then
                Continue For
            Else
                returnList.Add(result.Value)
            End If
        Next
        BatchOdbcConnection.Close()
        Return returnList
    End Function

    Private Function GetBatchList(fieldName As String, tableName As String) As IEnumerable(Of Double?)
        Const SQLQuery As String = "SELECT ""{0}"" FROM ""{1}"";"

        Dim query = String.Format(SQLQuery,
                                  fieldName,
                                  tableName)
        Dim queryResult = BatchOdbcConnection.Query(Of Double?)(query, commandType:=CommandType.Text)
        Return queryResult
    End Function
End Class

我已经对代码进行了概要分析，在 Dapper 的方法中，多达 70% 的时间都在等待，Query效率相当低。

我一直在尝试通过使其全部异步来改进这一点，以便在完成之前启动下一个查询：

Public Class ValuesRepository
    Private connectionString As String

    Public Sub New(connectionString As String)
        Me.connectionString = connectionString
    End Sub

    Private Function GetOdbcConnection() As IDbConnection
        Return New Odbc.OdbcConnection("DRIVER={AspenTech ODBC driver for Production Record Manager};" + connectionString)
    End Function

    Public Function GetAllBatchValues(tagList As IEnumerable(Of BatchTag)) As IEnumerable(Of IEnumerable(Of Double))
        Dim returnList As New List(Of IEnumerable(Of Double?))
        If tagList.Count = 0 Then Return returnList

        Dim tasks As New List(Of Task(Of IEnumerable(Of Double?)))

        For Each batchTag In tagList
            tasks.Add(GetBatchList(batchTag.fieldName, batchTag.tableName))
        Next

        Task.WaitAll(tasks.ToArray())

        For Each task In tasks
            Dim result = task.Result
            If result Is Nothing Then
                Continue For
            Else
                returnList.Add(result.Value)
            End If
        Next
        Return returnList
    End Function

    Private Async Function GetBatchList(fieldName As String, tableName As String) As Task(Of IEnumerable(Of Double?))
        Console.WriteLine("Begin " + fieldName + " -> " + tableName)
        Using conn = GetOdbcConnection()
            Const SQLQuery As String = "SELECT ""{0}"" FROM ""{1}"";"

            Dim query = String.Format(SQLQuery,
                                  fieldName,
                                  tableName)

            Dim queryResult = Await conn.QueryAsync(Of Double?)(query, commandType:=CommandType.Text)
            Console.WriteLine("End " + fieldName + " -> " + tableName)
            Return queryResult
        End Using
    End Function
End Class

我一直期望它启动所有查询，然后等待结果，因此在控制台中读取如下内容：

Begin: Foo -> TableA
Begin: Bar -> TableB
...
End: Bar -> TableB
End: Foo-> TableA

相反，控制台显示：

Begin: Foo -> TableA
End: Foo-> TableA
Begin: Bar -> TableB

从节奏中可以清楚地看出，应用程序实际上是在每次调用后等待查询结束。

我究竟做错了什么？

标签： vb.netasynchronous

解决方案

尝试用 Await Task.Delay 替换 Await conn.QueryAsync ，看看是否可行。如果它确实有效，那么您会遇到一个新问题“为什么 Await conn.QueryAsync 会阻止所有活动任务？”。检查您的驱动程序版本是否支持异步调用。可能有一个名为 QueryAsync 的方法，但在后面，它调用了一个不支持 Async 的驱动程序。

推荐阅读