0

I have an application in which some reports time out before getting data. We started increasing timeout througout our code in different places, and as our database is growing bigger and bigger, we see that we're repeating/duplicating more code.

So we decided to increase database timeout for queries globally. But I have this feeling that this is wrong. Yet I'm not able to bring forward any reasons. Is it bad to increase timeout everywhere? Why shouldn't we do this?

We're using SQL Server and Entity Framework.

1
  • If that is what is takes. Eventually you might have too much load on SQL server and need to increase the query efficiency. Entity Framework is convenient but is not efficient.
    – paparazzo
    Commented Aug 27, 2016 at 9:28

2 Answers 2

3

So we decided to increase database timeout for queries globally. But I have this feeling that this is wrong. Yet I'm not able to bring forward any reasons. Is it bad to increase timeout everywhere? Why shouldn't we do this?

The default client API query timeout of 30 seconds is appropriate for a typical OLTP workload, albeit arbitrary. The value should be set high enough to allow queries on a reasonably healthy system to complete successfully yet low enough so that queries timeout when the rather than wait indefinitely when things aren't well, such as in a long-term blocking scenario or due to a bad execution plan. There is no single timeout value that is appropriate for all queries unless the system is a 100% OLTP workload or 100% reporting.

It would be better to first address the root cause of longer-running queries before increasing the timeout value. Perform query and index tuning, ensure hardware is adequately sized and configured properly for your workload, and make sure EF data access is optimal. These practices will make the most efficient use of available resources, reduce compute costs, and provide the best end user experience.

If the amount of data required by queries doesn't increase with table size yet queries run longer as the number of rows in tables increase, this suggests scans. Query and tuning would be a good first step, assuming SQL Server memory is appropriate for the database size.

2

First of all there is noting like query timeout, it's "remote query timeout". Increasing it affects remote query execution (query executed by remote database server using linked server). By default it's 600 sec. Timeout period of the query directly executed on database server depends on session timeout, you configured in your application.

Limiting query timeout may increase you problem and you would not able to see any reports because data continues to grow and based on that query will take longer then earlier.

So, your feeling is true. Instead of thinking about increasing query or session timeout. You should consider on:-

  1. Query optimization
  2. Proper indexing of tables
  3. Upgrade you hardware and software
  4. Patch them properly
  5. Partition big tables
  6. Schedule Job Rebuilding/Reorganize Indexes
  7. And monitor you network latency between database and application server.

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.