Changing QueryTimeout from sqlsrv_query

Question

I'm experiencing problems with a slow running query that takes more than 30 seconds to execute at times, I would therefore like to increase my sqlsrv_query timeout.

Fatal error: Maximum execution time of 30 seconds exceeded

I'm having trouble with my PHP syntax, as the examples at: http://php.net/manual/en/function.sqlsrv-query.php did not really make sense to me.

Currently my connection / setup looks as follows:

$testServer = 'IP\servername,PORT';
$testDetails = array('Database' => 'DBNAME', 'UID' => 'USERNAME', 'PWD' => 'Password');
$testConnect = sqlsrv_connect($testServer, $testDetails);

My understanding is that I need to pass the timout details through as parameters within sqlsrv_connect but I'm not getting my syntax right.

(I have optimized the query as much as I can, unfortunately given the design of the db table I am not able to get it to return consistently in less than 30 seconds.)

Answer 1

This error is actually a PHP error, and has nothing to do with the sqlsrv drivers; by default, the sqlsrv drivers will run a query until it receives a result.

Key: QueryTimeout
Value: A positive integer value
Description: Sets the query timeout in seconds. By default, the driver will wait indefinitely for results.
_{(emphasis mine)}

^{Source: Options parameter - sqlsrv_query (php.net)}

---

The error is the max_execution_time defined in the php.ini file - default 30 seconds. As the script runs over 30 seconds the parser terminates the script, throwing the fatal error.

To solve this error you can either change the max_execution_time setting in the php.ini file, or alternatively at the top of your script add:

ini_set("max_execution_time", value); //The value will only be changed for this script!

Where value is the maximum time in seconds you want the script to run for.

---

As your question is asking about setting a timeout for queries, the syntax for that would be:

$result = sqlsrv_query($conn, $query, $params, array("QueryTimeout" => 30));

Where, again, 30 is the maximum time in seconds you want the query to run for.