sql-server - VB6 - 如何在一次调用中执行多个 SQL 查询

Question

我在 VB6 中有一个旧的缓慢过程，使用 ADO 在许多调用中运行许多 SQL 查询。它花费了太多时间。我想测试是否在一个查询中发送所有查询(大多数只是更新或插入)以节省网络和调用时间。我该如何实现这一点，并建议这是否可以节省时间并提高性能？

    qry = "Update table1 Set col1 = 'eerere' Where 1=1"
    qry = qry & vbCrLf & "  AND MyID = " & MyID & vbCrLf
    ExecSQL qry, DBCon, adAsyncExecute

    qry = "Insert Into TableMain"
    qry = qry & "Select  col1,col2,col3 from Table2 Where 1=1"
    qry = qry & vbCrLf & "  AND MyID = " & MyID & vbCrLf
    ExecSQL qry, DBCon, adAsyncExecute

    qry = "Update table5 Set col1 = 'eerere' Where 1=1"
    qry = qry & vbCrLf & "  AND MyID = " & MyID & vbCrLf
    ExecSQL qry, DBCon, adAsyncExecute

Answer 1

您可以通过用分号分隔命令来将它们合并为一个调用:

qry = "Update table1 Set col1 = 'eerere' Where 1=1"
qry = qry & vbCrLf & "  AND MyID = " & MyID & "; " & vbCrLf

qry = qry & "Insert Into TableMain"
qry = qry & "Select  col1,col2,col3 from Table2 Where 1=1"
qry = qry & vbCrLf & "  AND MyID = " & MyID & "; " & vbCrLf

如果不导致任何错误，它可能会给您带来一些小的性能改进。