Recommendations for improving the performance of PDO_SQLSRV and SQLSRV

Below, we will try to explain how SQLSRV and PDO_SQLSRV performance can be improved.

1. Multiple Active Result Sets (MARS)

MARS is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection. MARS is enabled by default in both SQLSRV and PDO_SQLSRV drivers. There can be some performance drop when MARS is enabled. Existing code optimized to run in the non-MARS world may show a performance dip when run un-modified with MARS.

To disable MARS for SQLSRV:

$connectionInfo = array( "Database"=>"AdventureWorks", 'MultipleActiveResultSets'=>false );  
$conn = sqlsrv_connect( $serverName, $connectionInfo );

To disable MARS for PDO_SQLSRV:

 $conn = new PDO( "sqlsrv:server=$serverName; Database=$database; MultipleActiveResultSets=false", $uid, $pwd );

The caveat is that disabling MARS means you can only have one active statement/result at a time. So depending on how your PHP code is structured you could encounter errors when executing a second statement or accessing a result.

2. Fetching numeric data types

Fetching numeric data as strings may show a performance dip too. SQLSRV returns the data as its default PHP type by default, whereas PDO_SQLSRV returns the numeric data as strings. SQLSRV_ATTR_FETCHES_NUMERIC_TYPE flag can be used to fetch numeric data as its default PHP type when using PDO_SQLSRV.

    $conn = new PDO( "sqlsrv:server=$serverName; Database=$database",  $uid, $pwd );
    $conn->setAttribute( PDO::ATTR_STRINGIFY_FETCHES,false );
    $conn->setAttribute( PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE, true );

Note that, when connection option flag ATTR_STRINGIFY_FETCHES is on, even when SQLSRV_ATTR_FETCHES_NUMERIC_TYPE is on, the return value will still be a string.

3. Encoding

If the code-page of the machine and user account under which the PHP process is running matches the server collation, SQLSRV_ENC_CHAR/PDO::SQLSRV_ENCODING_SYSTEM can be used to avoid conversions to UTF-8. These flags encode all character data into a one-byte-per-character 8-bit encoding that matches the local code-page.

SQLSRV:

$connectionInfo = array( "Database"=>"AdventureWorks", "CharacterSet"=>SQLSRV_ENC_CHAR );  // This is also default
$conn = sqlsrv_connect( $serverName, $connectionInfo );

PDO_SQLSRV:

$conn = new PDO( "sqlsrv:server=$serverName ; Database=$database",  $uid, $pwd );
$conn->setAttribute( PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM );

4. Client-side cursor queries

Client-side cursors allow you to cache an entire result set in memory. Note that, when using these cursors, the data is fetched into local memory right after a query is executed. As a result, Execute APIs are usually much slower. Client-side cursors should be used for small to medium-sized result sets. Large result sets should use server-side cursors. More information about client-side cursors can be found here.

SQLSRV:

$stmt = sqlsrv_query( $conn, $tsql, array(), array( "Scrollable"=>"buffered" )); //data fetched into local memory

PDO_SQLSRV:

$stmt = $conn->prepare( $query, array( PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED ));  
$stmt->execute(); //data fetched into local memory