SQL Server 2022 Express对比Access查询性能优化求助

问题背景 #

我在SQL Server Express 2022和MS Access 2016（2002-2003格式.MDB）中有结构、数据完全一致的表，包含约10万条记录，且都创建了名为ID的索引。通过ADO连接两者，SQL Server采用共享内存（shared memory）连接方式，数据库均为默认配置。

连接字符串如下：

• MS Access：PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=xxxxx
• SQL Server：Provider=MSOLEDBSQL;DataTypeCompatibility=80;Data Source=\.SQLEXPRESS;Initial Catalog=xxxxx;User ID=yyyyy;Password=zzzzz;Use Encryption for Data=False;Persist Security Info=True;TrustServerCertificate=True

执行基准测试：循环2000次执行查询SELECT value FROM [fad] WHERE userid=[random] AND module=1 AND course=1，结果SQL Server耗时17.04秒，MS Access仅3.92秒。但参考资料显示SQL Server性能应至少比Access快2.5倍，修改连接字符串无效，在Windows 10 Pro和2016服务器上测试结果一致。

附测试用VBScript（ASP classic）代码：

<%
server.scripttimeout=9000000

gotest(1) ' MSSQL
gotest(0) ' MSACCESS

function gotest(DbMSSQL)
    response.write "DATABASE TYPE: <b>"
    Set Conn = Server.CreateObject("ADODB.Connection")  
    conn.CursorLocation=3 ' adUseClient
    
    select case DbMSSQL
        case "0": response.write "MSAccess"
                  PathDB="c:\inetpub\wwwroot\accessdb\accessdb.mdb"
                  Conn.Open ("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&PathDB&"")
        case "1": response.write "MSSQL"
                  PathDB="sqldb"
                  conn.open("Provider=MSOLEDBSQL19;DataTypeCompatibility=80;Data Source=\.SQLEXPRESS;Initial Catalog="&pathdb&";User ID=sa;Password=Pnzip1xt-;Use Encryption for Data=False;Persist Security Info=True;TrustServerCertificate=True")              
                  
                  set RS=Conn.Execute("SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;")
                  if not RS.eof then response.write "</b><br>Connection type: <b>"&RS("net_transport")&"<br>"
                  
    end select

    response.write "</B><br>ADO Version: <b>"&Conn.Version&"</b><br><br>PERFORMANCE TEST IN PROGRESS...<br>"
    response.flush
    t1=timer
    randomize timer
    for dummy=1 to 2000
        iduten=(Int((53700-13123)*Rnd+13122))
        
        set RS=conn.execute("SELECT value FROM [fad] WHERE userid="&iduten&" AND module=1 AND course=1")
    
        if dummy=1 then
            response.write "<br>Cursor type: "&RS.cursortype
            response.write "<br>Cursor location: "&RS.cursorlocation
            response.write "<br>Cursor lock type: "&RS.LockType&"<br><br>"
        end if  
        if dummy mod 200=0 then
            response.write "X"
            response.flush
        end if
        if dummy mod 1000=0 then
            response.write " "&dummy&"<br>"
            response.flush
        end if
        RS.close
        set RS=nothing
        if not response.isclientconnected then response.end
    next    
    response.write "<br><br>TEST HAS ENDED. TIME TAKEN (sec): "
    response.write timer-t1
    response.write "<br><br>"
    Conn.close
    set conn=nothing
end function
%>

优化方案 #

1. 创建匹配查询条件的复合索引 #

当前仅创建了ID索引，但你的查询过滤条件是userid + module + course组合，SQL Server每次都要全表扫描匹配数据，这是性能差的核心原因。

• 执行以下语句创建覆盖索引：

  CREATE NONCLUSTERED INDEX IX_fad_userid_module_course ON fad(userid, module, course) INCLUDE (value);
  

  加上INCLUDE (value)可以让索引直接包含查询所需字段，避免额外的书签查找操作。

2. 调整游标位置为服务器端 #

代码中设置conn.CursorLocation=3（adUseClient）会强制把查询结果加载到客户端内存，增加了数据传输和处理开销，而本地的Access受此影响更小。

• 将conn.CursorLocation=3改为conn.CursorLocation=2（adUseServer），让SQL Server在服务器端处理游标，减少不必要的数据移动。

3. 改用参数化查询 #

当前直接拼接userid到SQL语句中，2000次循环会触发2000次查询计划编译，极大消耗资源。Access的Jet引擎处理简单动态SQL的编译开销更低。

• 修改为参数化查询，示例代码调整如下：

  function gotest(DbMSSQL)
      ' ... 其他代码不变
      t1=timer
      randomize timer
      
      ' 提前创建命令和参数对象（循环外）
      Set cmd = Server.CreateObject("ADODB.Command")
      cmd.ActiveConnection = Conn
      cmd.CommandText = "SELECT value FROM [fad] WHERE userid=? AND module=1 AND course=1"
      Set param = cmd.CreateParameter("@userid", 3, 1) ' adInteger, adParamInput
      cmd.Parameters.Append param
  
      for dummy=1 to 2000
          iduten=(Int((53700-13123)*Rnd+13122))
          cmd.Parameters("@userid").Value = iduten
          set RS = cmd.Execute()
          
          ' ... 后续处理逻辑不变
      next
      ' ... 其他代码不变
  end function
  

  参数化查询可以让SQL Server复用执行计划，大幅降低编译开销。

4. 调整SQL Server Express的资源配置 #

SQL Server Express默认限制最大使用1GB内存，若服务器内存充足，可修改配置提升可用资源：

• 打开SQL Server Management Studio，连接实例后右键点击→属性→内存，将“最大服务器内存(MB)”设置为合适值（比如4096，根据服务器总内存调整）。
• 确保SQL Server服务启动账户具备足够权限，避免资源受限。

5. 更新统计信息并验证执行计划 #

统计信息过期会导致SQL Server生成低效的执行计划，可执行以下语句更新：

UPDATE STATISTICS fad;

同时查看查询执行计划确认是否用到索引：

SET SHOWPLAN_XML ON;
GO
SELECT value FROM [fad] WHERE userid=12345 AND module=1 AND course=1;
GO
SET SHOWPLAN_XML OFF;

若结果显示Table Scan，需检查索引是否正确创建，或是否存在索引碎片，可执行ALTER INDEX IX_fad_userid_module_course ON fad REBUILD;重建索引。

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