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

What do you mean exactly by 'old' and 'new'? What kind of activity are we talking about? DO you use prepared statements? A lot of unknowns... — Erwin Moller, Aug 06 '12 at 09:56
Could it depend on your testing method? Testing database can be quite hard, with query caching going on and all. Also, did you read up on the performance differences? Check http://af-design.com/blog/2009/01/30/php-mysql-vs-mysqli-database-access-metrics/ and the 'local' http://stackoverflow.com/questions/171400/which-is-fastest-in-php-mysql-or-mysqli — Nanne, Aug 06 '12 at 11:44
My testing method is simple... A query, a while loop fetching data as array and a counter inside my loop. — Kevin Labécot, Aug 06 '12 at 12:08
But is that a good representation of real-life usage? How do you account for query-cache for instance? — Nanne, Aug 06 '12 at 12:51
Yes, it's a real life usage. We've a lot of exports process (without pagination :( )... This case is real and very often (many times a day). And my export of +484 000 is a test using the database of one or our biggest customer. It's why it's very important for me to get this process as fast as possible. — Kevin Labécot, Aug 06 '12 at 13:01
I can confirm that using PDO_ODBC is much faster than PDO_SQLSRV. Since I had the 64bit PHP 5.5.12 installed I used the unofficial 64bit SQLSRV drivers located at http://robsphp.blogspot.nl/2012/06/unofficial-microsoft-sql-server-driver.html A simple query retrieving 66 records took ~500ms (PDO_ODBC) vs ~5000ms (PDO_SQLSRV). For PDO_ODBC I used multiple ODBC drivers (SQL Server, SQL Server Native Client 11.0, ODBC Driver 11 for SQL Server) but all where approx. the same speed. — Jan, Jun 28 '15 at 22:36

score 2 · Accepted Answer · answered Aug 09 '12 at 06:14

2

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

Workaround : Use PDO/ODBC.

answered Aug 09 '12 at 06:14

Kevin Labécot

2,005
13
25

notes when using PDO/ODBC with SQL Native Client on windows: When comparing date datatypes(timestamp,etc), this setup seems to have a hard time so the easiest way I've found is to either cast them as dates in your query( `CAST(? AS DATE)` ) where appropriate or in a stored procedure make sure your input "date" parameters are varchar and allow SQL server to do the implicit conversion in the comparisons server-side. – AndrewPK Apr 05 '13 at 17:22

score 1 · Answer 2 · edited Dec 19 '14 at 21:08

1

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'

            ));

edited Dec 19 '14 at 21:08

Eric Hauenstein

2,557
6
31
41

answered Dec 19 '14 at 20:56

Ivan Voitovych

126
1
6

score 1 · Answer 3 · answered Apr 21 '15 at 01:07

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

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

3 Answers3

Linked