Sqlsrv Very Slow Over Network

I'm using sqlsrv (php_pdo_sqlsrv_55_ts.dll and php_sqlsrv_55_ts.dll) in PHP (5.5.12) to connect to a MSSQL 2012 Server over a VPN tunnel. But the transfer rate for large result set

Solution 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'

            ));

Solution 2:

How about replacing

$options["Scrollable"] = SQLSRV_CURSOR_CLIENT_BUFFERED;

with

$options["Scrollable"] = SQLSRV_CURSOR_FORWARD;

Solution 3:

One of the most common "fixes" for slow sqlsrv operations is to change the scrollable value. I tried this and it didn't help...in fact SQLSRV_CURSOR_CLIENT_BUFFERED caused erratic behavior.

Example:

$stmt = sqlsrv_query( $this->connection,  $sql, array(), array(
                "Scrollable"=>SQLSRV_CURSOR_CLIENT_BUFFERED
));

I believe newer versions of sqlsrv (which I have 4.3.0) already optimize this somewhat and suspect this isn't needed for latest versions.

The second most popular suggestion is to change the connection options...I tried many solutions...and they really didn't help. Again, I think newer sqlsrv drivers have changed the defaults for this.

Example:

    sqlsrv_configure('ClientBufferMaxKBSize', 0);           
$serverName = Config::get('settings.MS_DB_HOST').", 1433"; 
$connectionInfo = array( 
    "Database"=>Config::get('settings.MS_DB_NAME'), 
    "UID"=>Config::get('settings.MS_DB_UID'), 
    "PWD"=>Config::get('settings.MS_DB_PWD'),
    "MultipleActiveResultSets"=>'0',
    "connectionpooling"=>"0",
    "TraceOn"=>"0"
);                  
$this->connection = sqlsrv_connect($serverName, $connectionInfo);   

All that said...what finally helped for me was to realize that sqlsrv_connect was my culprit and incredibly slow. For some apps you won't notice this...but in my case I was dealing with code that would constantly reconnect to the database before executing subsequent sql operations. So say this takes a half second to run...if you have 10 different sql statements that each connects on their own "sqlsrv_connect()"...this (for me) could take 5 seconds. The solution was to reuse the connection object from the first instantiation. Once I did this, the performance improvements were dramatic.

Hopefully in the future the sqlsrv library will program something like PHP/MySQL persistent connections and this won't be needed.