Access环境下SQL语句在部分Windows10设备卡顿及语句优化咨询

问题排查与SQL优化方案 #

1. 故障根因初步判断

你遇到的部分Win10设备卡死问题，大概率不是当前SQL语句本身逻辑导致的：

• 现有SQL属于非常简单的单条件内连接查询，且所有Win7设备和部分Win10设备运行正常，已经排除SQL本身的逻辑缺陷
• 建议优先排查非SQL方向的问题：
  • 故障Win10设备的Access数据库引擎（你当前用#包裹日期是Access的语法规范）版本/位数不匹配，VB6编译的程序默认是32位，部分新Win10默认没有安装32位Access引擎，调用时会出现无响应
  • 故障设备的ODBC配置和正常设备存在差异，可直接对比正常/故障Win10设备的ODBC数据源参数
  • 新Win10的安全策略/Defender拦截了数据库临时文件的读写，导致查询时IO阻塞

2. 当前SQL可做的优化调整

' 优化点1：改用参数化查询，避免字符串拼接日期的格式兼容问题，同时防范SQL注入
Dim sql As String = "SELECT WeightCert, s.SwatDate, TareDate, SaleCode, " &
                    "Species, Qual, SaleDesc, Trucker, TruckNo, TruckState, " &
                    "TruckLic, TrlState, TrlLic, TruckType, Comments, TareLoad, " &
                    "ScaleLoad, LoadNo, Logger, LogMethod, Block, Val(Gross) as GrossWt, " &
                    "Val(Tare) as TareWt, Weight, PrintAvg, Brand, Commodity, SortCode, " &
                    "Deck, UserInfo1, UserInfo2, EmergencyLevel, ReprintCount, " &
                    "Reason, LocationName, Addr1, Addr2, OwnerName, LoggerName," &
                    "Contract, Weighmaster, TT, Reprint, TareoutBarcode, PrintTare, TruckName, " &
                    "ManualWeight, DeputyName, CertStatus, ReplacedCert  " &
                    "FROM Swatlog s INNER JOIN tblTempCert t " &
                    "ON s.SwatDate = t.SwatDate " &
                    "WHERE t.SwatDate = ?"
' 后续直接给参数赋值SwatKey即可，不需要用#包裹，可解决不同系统日期格式差异导致的查询异常

• 配套优化建议：
  • 给tblTempCert.SwatDate、SwatLog.SwatDate字段添加索引，关联和查询速度会有明显提升
  • 排查SELECT列表中是否有报表不需要展示的冗余字段，删掉冗余字段可减少数据传输量

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