sharepoint0520
asked on
What is default and recommend value for SQL command timeout parameter?
Experts,
We have one visual web part in share point where we are pulling data from external SQL database by SQL connection string. We used default connection string. We have concern about execution time of query and timeout. Which parameter used for command execution and what is the default limit?
We have one visual web part in share point where we are pulling data from external SQL database by SQL connection string. We used default connection string. We have concern about execution time of query and timeout. Which parameter used for command execution and what is the default limit?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
tune the query so it runs in .1 sec
no need to worry about timeouts after that
no need to worry about timeouts after that
ASKER
I just wanted to clarify one more point. When i sql throw time out error for execute command ? (After 600 seconds)?
Hi,
Three timeouts
Connection Timeout
I believe the connection timeout default is 30 seconds - but it depends on which provider you are using - you haven't specified it. For some SQL providers it is 30 seconds. There can be a lot of benefit in changing this to a more reasonable value like 5 seconds. This can aid in trouble-shooting if using DSN.
Command Timeout
As discussed above - the likely time the query should run in, and then some. While you don't want this to be a nuisance, there can be a lot of value in having a long-running query aborted if it is taking an extraordinarily long time - say several multiples of expected runtime.
Remote Query Timeout
Mentioned above - SQL could be timing out your query at the 10min mark
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-2017
HTH
David
Three timeouts
Connection Timeout
I believe the connection timeout default is 30 seconds - but it depends on which provider you are using - you haven't specified it. For some SQL providers it is 30 seconds. There can be a lot of benefit in changing this to a more reasonable value like 5 seconds. This can aid in trouble-shooting if using DSN.
Command Timeout
As discussed above - the likely time the query should run in, and then some. While you don't want this to be a nuisance, there can be a lot of value in having a long-running query aborted if it is taking an extraordinarily long time - say several multiples of expected runtime.
Remote Query Timeout
Mentioned above - SQL could be timing out your query at the 10min mark
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-2017
HTH
David
ASKER
David,
What is default value for Command Timeout if we don't mention to connection string ? And how it's works?
What is default value for Command Timeout if we don't mention to connection string ? And how it's works?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the connection string <add connectionString="Server=s
When SQL throw error for lengthy query? Please advice about best practice for sql connection.