PHP的SQL Server查询速度很慢，而SQL Mgt Studio的SQL Server查询速度却很快-为什么? [英] SQL Server Query Slow from PHP, but FAST from SQL Mgt Studio - WHY?

问题描述

当我在SQL Server Mgt Studio中执行查询时，我有一个快速运行的查询(不到1秒)，但是当我在PHP中(在相同的数据库实例上)运行完全相同的查询时， 使用FreeTDS v8 mssql_query()，它需要更长的时间(超过70秒).

I have a fast running query (sub 1 sec) when I execute the query in SQL Server Mgt Studio, but when I run the exact same query in PHP (on the same db instace) using FreeTDS v8, mssql_query(), it takes much longer (70+ seconds).

我要打的表在Where子句中使用的日期字段上有一个索引.

The tables I'm hitting have an index on a date field that I'm using in the Where clause.

难道是PHP的mssql函数没有利用索引吗?

Could it be that PHP's mssql functions aren't utilizing the index?

我还尝试过将查询放入存储过程中，然后从PHP执行SP-发生时差相同的结果.

I have also tried putting the query inside a stored procedure, then executing the SP from PHP - the same results in time difference occurs.

我还尝试过在具有日期索引但也没有运气的表上添加WITH(INDEX(..))子句.

I have also tried adding a WITH ( INDEX( .. ) ) clause on the table where that has the date index, but no luck either.

SELECT
        1 History,
        h.CUSTNMBR CustNmbr,
        CONVERT(VARCHAR(10), h.ORDRDATE, 120 ) OrdDate,
        h.SOPNUMBE OrdNmbr,
        h.SUBTOTAL OrdTotal,
        h.CSTPONBR PONmbr,
        h.SHIPMTHD Shipper,    
        h.VOIDSTTS VoidStatus,
        h.BACHNUMB  BatchNmbr,
        h.MODIFDT ModifDt

  FROM  SOP30200 h
        WITH (INDEX (AK2SOP30200))
  WHERE
        h.SOPTYPE = 2 AND
        h.DOCDATE >= DATEADD(dd, -61, GETDATE()) AND
        h.VOIDSTTS = 0 AND
        h.MODIFDT = CONVERT(VARCHAR(10), DATEADD(dd, -1*@daysAgo, GETDATE()) , 120 )
  ;

推荐答案

已启用哪些设置，通常ARITHABORT是罪魁祸首，在SSMS中为ON，但您可能会关闭它与之连接

what settings are on, usually ARITHABORT is the culprit, it is ON in SSMS but you might be connecting with it off

在运行查询时在SSMS中运行此命令，并查看与PHP连接的会话的第一列

Run this in SSMS while you are running your query and see what the first column is for the session that is connected from PHP

select arithabort,* from sys.dm_exec_sessions
where session_id > 50

这篇关于PHP的SQL Server查询速度很慢，而SQL Mgt Studio的SQL Server查询速度却很快-为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！