迁移到SQL后，我的表单搜索正在访问前端缓慢运行

Question

我有一个带有2个sub表格的单个表单，然后迁移到sql，我可以使用以下代码搜索我的表单。我的表单的记录来源是查询正在快速运行。现在，当我单击搜索按钮以获取此表格中的过滤记录时，在3分钟后运行非常缓慢地运行

         DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere

在1秒之前。

   Private Sub cmdSearch_Click()
  Dim varWhere As Variant, varWhere2 As Variant, varDateSearch As Variant
 Dim rst As DAO.Recordset
' Initialize to Null
  varWhere = Null
   varWhere2 = Null
   varDateSearch = Null
 If Not IsNothing(Me.txtTransmittal_to_Site) Then
    ' .. build the predicate
    ' Must use a subquery here because the value is in a linking table...
    varWhere = (varWhere + " AND ") & _
        "[Owner Document Number] IN (SELECT [Owner Document Number] FROM tblTransmittals " & _
        "WHERE tblTransmittals.[CT- Transmittals] LIKE '" &    Me.txtTransmittal_to_Site & "*')"
 End If
   If IsNothing(varWhere) Then
    MsgBox "You must enter at least one search criteria.", vbInformation,  gstrAppTitle
    Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblDocuments WHERE " & varWhere)
' See if found none
If rst.RecordCount = 0 Then
    MsgBox "No Documents meet your criteria.", vbInformation, gstrAppTitle
    ' Clean up recordset
    rst.Close
    Set rst = Nothing
    Exit Sub
End If
' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rst.MoveLast
  varWhere3 = Replace(varWhere, "*", "%")
      Set qdf = CurrentDb.CreateQueryDef("")
     qdf.Connect = "ODBC; DRIVER=SQL SERVER; SERVER=DESKTOP-JL7MJL4; 
     DATABASE=EDMS_BB2_with_relation - Copy (4)SQL;  Trusted_Connection=YES;"
       qdf.SQL = "SELECT tblDocuments.[Owner Document Number], tblDocuments.
   [Sazeh Document Number], tblDocuments.[Document Title], tblDocuments.
   [Project No], tblDocuments.Originator, tblOriginator.[Originator Des], 
  tblDocuments.Zone, tblDocuments.Unit, tblDocuments.Discipline, 
  tblDiscipline.DiscDesc, tblDocuments.[Document Type], tblDocumentType.
  [TYPE Description], tblDocuments.SheetNumber " & _
 "FROM tblDiscipline RIGHT JOIN ((tblDocumentType RIGHT JOIN tblDocuments ON 
  tblDocumentType.TYPE = tblDocuments.[Document Type]) LEFT JOIN 
  tblOriginator ON tblDocuments.Originator = tblOriginator.Originator) ON 
   tblDiscipline.DiscCode = tblDocuments.Discipline WHERE " & _
   varWhere3 & _
    " ORDER BY tblDocuments.[Owner Document Number]"
      qdf.ReturnsRecords = True
      Set rst2 = qdf.OpenRecordset

If IsFormLoaded("frmDocuments") Then
   DoCmd.OpenForm "frmDocuments", acNormal
    Set Forms!frmDocuments.Recordset = rst2
    Forms!frmDocuments.SetFocus
End If

Answer 1

首先，代码执行完整查询只是为了告诉用户是否有记录。没有必要返回所有记录。限制搜索查询，例如SELECT TOP 1 ...

出于相同的原因，无需返回所有列，因此仅指定一个字段，例如SELECT TOP 1 [Owner Document Number] ...

即使您已经移至SQL后端，我想从您使用链接表的问题中的信息中想到。访问数据库引擎不是特别智能的，因此它可能会单独执行子查询，然后它将在主查询中的每个记录中搜索子查询...并且该子查询的数据可能不会被索引。它甚至可能正在为主查询中的每个单独行执行子查询。您可以做一些事情来提高这一效率。使用加入。这更有可能使用索引使查询更有效。像

SELECT TOP 1 Docs.[Owner Document Number] 
FROM tblDocuments AS Docs INNER JOIN tblTransmittals AS Tmls
  ON Docs.[Owner Document Number] = Tmls.[Owner Document Number]
WHERE Tmls.[CT- Transmittals] LIKE 'foobar*'

将由您正确地放在一起。

最后（实际上我很惊讶，还没有几个评论），您切勿将用户数据直接插入SQL语句中。始终为此使用参数化查询。对于访问，这意味着创建带有参数的QueryDef。可以像db.CreateQueryDef("", SQL)

一样创建一个临时的QueryDef

如果这些提示不够改善性能，请考虑直接向SQL Server发送通行查询以进行初始记录检查。还要考虑使用优化的查询重置form.recordsource，而不是将were条件发送到docmd.openform。这两种技术都可以分开研究，这些细节超出了这个问题的范围。