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

Question

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 :

• testOldDriver_mssql = Rows 40000 : 1 seconds
• testNewDriver_nonPDO = Rows 40000 : 7 seconds
• testNewDriver_PDO = Rows 40000 : 4 seconds

Here my biggest proccess (+480 000 rows) :

• testOldDriver_mssql = Rows 484856 : 27 seconds
• testNewDriver_nonPDO = Rows 484856 : 120 seconds
• testNewDriver_PDO = Rows 484856 : 47 seconds
• testPDO_ODBC = Rows 484856 : 24 seconds

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

Answer 1

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

Workaround : Use PDO/ODBC.

Answer 2

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'

            ));

Answer 3

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);