SQL Server Query Slow from PHP, but FAST from SQL Mgt Studio - WHY???
- by Ray
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).
The tables I'm hitting have an index on a date field that I'm using in the Where clause.
Could it be that PHP's mssql functions aren't utilizing the index?
I have also tried putting the query inside a stored procedure, then executing the SP from PHP - the same results in time difference occurs.
I have also tried adding a WITH ( INDEX( .. ) ) clause on the table where that has the date index, but no luck either.
Here's the query:
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 )
;
