Geek Answers Handbook

SQL Server Query Slow from PHP, but FAST from SQL Mgt Studio - WHY?

I have a quick query (younger 1 sec) when I execute a query in SQL Server Mgt Studio, but when I run the same query in PHP (on the same db server) using FreeTDS v8, mssql_query (), it takes much longer time (70 + seconds).

The tables that I click on have an index in the date field that I use in the Where clause.

Could it be that PHP mssql functions are not using an index?

I also tried putting the request into a stored procedure, and then running SP from PHP - the same results in time difference.

I also tried adding a WITH clause (INDEX (..)) in a table where it has a date index, but also no luck.

Here's the request:

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 )
  ;
+5
3

, ARITHABORT , SSMS,

SSMS , , PHP

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

SQL Profiler , .

0

( ) SQL Profiler "", SET - Arithabort, , Quoted Identifier . , , - .

0
source

More articles:


All Articles