3

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

BernardV
  • 640
  • 10
  • 28

1 Answers1

7

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.

ImClarky
  • 1,933
  • 1
  • 25
  • 29
  • Thank you very much for the comprehensive answer! (even including the sqlsrv_query syntax bit) I modified my php.ini and my problem has been resolved. – BernardV Jul 20 '16 at 09:53
  • The "array("QueryTimeout" => 30)" solved a similar problem for me. Thanks. – luisdev Nov 29 '16 at 10:59
  • Adding set_time_limit(300); to the php code that ran an expensive database query was a workaround that "fixed" another similar timeout issue I had in a php script on a slow, old and grumpy server. – luisdev Feb 17 '17 at 14:39