CODEWITHSUNDEEP
phpsql-server
Kevin Labécot
Kevin Labécot

Reputation: 1995

PHP - Why is new SQLSRV driver slower than the old mssql driver?

I've a lot of components using the old driver mssql from PHP. I want to switch to the new driver SQLSRV by Microsoft but my queries are a lot of slower.

I've many processes handling +400 000 rows.

Here are my tests with 40 000 rows :

Here my biggest proccess (+480 000 rows) :

Is the new driver definitely slower ? Or am I'm missing something ?

Edit 1:

By "old driver" I mean the deprecated MSSQL library (see php.net/mssql).

The new driver is the one made directly by Microsoft (see http://www.microsoft.com/en-us/download/details.aspx?id=20098)

My query is 

SELECT * FROM myTable 
WHERE pdvSaisie IN 
       (SELECT number FROM pdvs WHERE nom LIKE 'ZUEE %')

and using a direct query() (no prepare and same result if I use a prepared statement).

Edit 2:

Added PDO/ODBC test. Surprise, it's faster :o

Upvotes: 7

Views: 9790

Answers (3)

Jibieta
Jibieta

Reputation: 341

I had a similar problem with the driver SQLSRV, the final solution in my case was change the option "TraceOn" to "0", this configuration prevent the tracing of the driver.

For more details, see Connection Options

Example:

$connectionInfo = array( "Database"=>"dbName", "TraceOn" => "0");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

Upvotes: 1

Ivan Voitovych
Ivan Voitovych

Reputation: 136

For speed up fetch up to 3 times please use "MultipleActiveResultSets"=>'0' in your sqlsrv_connect connection options.

Ex: 

$db = sqlsrv_connect('127.0.0.1', array('Database'=>'dbname','UID'=> 'sa','PWD'=> 'pass',"CharacterSet" =>"UTF-8","ConnectionPooling" => "1"
                    ,"MultipleActiveResultSets"=>'0'

            ));

Upvotes: 1

Kevin Labécot
Kevin Labécot

Reputation: 1995

Known bug : http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/6c4d2c96-6ddc-4872-a5b6-51daddf3a095/

Workaround : Use PDO/ODBC.

Upvotes: 2

Related Questions