VB.NET如何根据文本框内容选择MS Access表解决3万条数据查询卡顿问题

卡顿根因 #

你当前代码卡顿主要来自3个核心问题：

• HELPER字段无索引，每次查询都要全表扫描3万条数据
• 同一次数据库连接内执行了5次独立查询，重复的IO开销拉低了执行速度
• 没有做空值过滤，即使某个工艺文本框为空也会执行无效查询

优先推荐优化方案（无需修改表结构，改动最小） #

1. 先给ProdOutput_Lathe表的HELPER字段添加普通索引：打开Access表设计视图，选中HELPER字段，将「索引」属性设置为「有（可重复）」即可，单表3万条数据场景下加索引后查询速度可提升10~20倍。
2. 将5次独立查询合并为1次批量查询，减少和Access的交互开销。
3. 增加空值判断，工艺文本框为空时跳过对应查询。

优化后代码示例 #

Private Sub txtProcYM_Leave(sender As Object, e As EventArgs) Handles txtProcYM.Leave
    Try
        Dim processList = New List(Of String) From {txtProcess1.Text, txtProcess2.Text, txtProcess3.Text, txtProcess4.Text, txtProcess5.Text}
        ' 过滤空的工艺项
        Dim validProcess = processList.Where(Function(p) Not String.IsNullOrWhiteSpace(p)).ToList()
        Dim resultDict = qtyCheck(txtLot.Text, txtPartNumber.Text, validProcess)
        
        ' 赋值，没有对应数据默认填0
        proc1QTYIn.Text = If(resultDict.ContainsKey(processList(0)), resultDict(processList(0)).Item1, "0")
        proc1QTYOut.Text = If(resultDict.ContainsKey(processList(0)), resultDict(processList(0)).Item2, "0")
        proc2QTYIn.Text = If(resultDict.ContainsKey(processList(1)), resultDict(processList(1)).Item1, "0")
        proc2QTYOut.Text = If(resultDict.ContainsKey(processList(1)), resultDict(processList(1)).Item2, "0")
        proc3QTYIn.Text = If(resultDict.ContainsKey(processList(2)), resultDict(processList(2)).Item1, "0")
        proc3QTYOut.Text = If(resultDict.ContainsKey(processList(2)), resultDict(processList(2)).Item2, "0")
        proc4QTYIn.Text = If(resultDict.ContainsKey(processList(3)), resultDict(processList(3)).Item1, "0")
        proc4QTYOut.Text = If(resultDict.ContainsKey(processList(3)), resultDict(processList(3)).Item2, "0")
        proc5QTYIn.Text = If(resultDict.ContainsKey(processList(4)), resultDict(processList(4)).Item1, "0")
        proc5QTYOut.Text = If(resultDict.ContainsKey(processList(4)), resultDict(processList(4)).Item2, "0")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return
    End Try
End Sub

Private Function qtyCheck(Lot As String, PartNum As String, validProcess As List(Of String)) As Dictionary(Of String, (String, String))
    Dim result As New Dictionary(Of String, (String, String))
    If validProcess.Count = 0 Then Return result
    
    Dim constr As String = con.ConnectionString
    ' 生成参数占位符
    Dim paramPlaceholders As New List(Of String)
    Dim params As New List(Of OleDb.OleDbParameter)
    For i = 0 To validProcess.Count - 1
        Dim paramName = $"@HELPER{i}"
        paramPlaceholders.Add(paramName)
        params.Add(New OleDb.OleDbParameter(paramName, OleDbType.VarChar) With {.Value = $"{Lot}-{PartNum}-{validProcess(i)}"})
    Next
    
    Dim query As String = $"SELECT HELPER, SUM(prdINPUT) AS prdInput,SUM(prdOUTPUT) AS prdOutput FROM ProdOutput_Lathe WHERE HELPER IN ({String.Join(",", paramPlaceholders)}) GROUP BY HELPER;"
    
    Using cn As New OleDb.OleDbConnection(constr),
          cmd As New OleDb.OleDbCommand(query, cn)
        cmd.Parameters.AddRange(params.ToArray())
        cn.Open()
        Using reader = cmd.ExecuteReader
            While reader.Read()
                Dim helper = reader("HELPER").ToString()
                ' 提取对应的process值
                Dim process = helper.Split("-")(2)
                Dim input = If(IsDBNull(reader("prdInput")), "0", reader("prdInput").ToString())
                Dim output = If(IsDBNull(reader("prdOutput")), "0", reader("prdOutput").ToString())
                result(process) = (input, output)
            End While
        End Using
    End Using
    Return result
End Function

分表方案实现注意事项 #

你提出的按类别拆分表的方案也可落地，适合后续数据量继续增长的场景：

• 拆分规则可按工艺类别分表，比如ProdOutput_Lathe_ProcessA、ProdOutput_Lathe_ProcessB，每个表只存对应工艺的数据
• 查询时根据输入的Process值直接查对应表，无需扫描无关数据，查询效率会比单表更高
• 注意每个拆分后的表都要给Lot和PartNumber字段加联合索引，不需要拼接HELPER字段，直接按两个字段过滤即可，查询性能会更好
• 缺点是后续新增工艺类型时需要同步新增对应表，维护成本比单表高

内容的提问来源于stack exchange，提问作者bertot