6

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 )
  ;
Ray
  • 360
  • 3
  • 8
  • Ray, have you found out what the problem was and how to solve it? I'm having a similar problem with a MySQL query running fast in SQLyog and slow from PHP using mysql_query... – user254875486 Jul 04 '11 at 08:03
  • I'm having the same issue. The only difference is that I'm using codeigniter. Please let me know when you have a solution on this. Thanks – Janzell Jurilla Jun 02 '14 at 08:32

4 Answers4

3

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

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
SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • Hello, thank you for this answer. I have a question. I set it on and my query is running very fast now, but when I run your recommended query it still shows arithabort is off on the PHP connection (even though the query runs very very fast now) do you know why it might do that? – Roofus McDuffle May 01 '17 at 18:58
0

Run the SQL Profiler, and set up a trace and see if there are any differences between the two runs.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
0

Using the LOGIN EVENT (and EXISTING CONNECTION) in SQL Profiler with the Text column will show the connection settings of a lot of important SET commands--Arithabort, Isolation Level, Quoted Identifier, and others. Compare and contrast these between the fast and slow connections to see if anything stands out.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
0

SET ARITHABORT ON; in your session, might improve query performance.
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql?view=sql-server-ver16

Always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization, leading to performance issues.

Saharis9988
  • 384
  • 2
  • 5