SQL Timeout revisited

[MartinDH]

Hi all,

I've seen quite a few posts on fixing a timeout from a VB.NET program. Usually, add a couple of seconds to the timeout setting of the connection, or add an index to make the query go faster.
But I can't help thinking (and in fact I am encountering those right now) that there will always be queries who just take longer, due to their complexity, the amount of data to process, etc.
Isn't it possible and even -in some cases- normal that there are queries that run for a minute and a half?


Hope you can shed some light on this, thanks in advance!
Martin

[si_the_geek]

Welcome to VBForums

Some database queries (but not many) will always run for a long time, but there are usually things that can be done to improve queries.

If you show us a slow query, and give us a clue as to the amount of rows in each table (and preferably data types and index info), we can probably come up with improvements.

[wqweto]

Isn't it possible and even -in some cases- normal that there are queries that run for a minute and a half?

That would be pretty normal for reporting queries, esp. multi-year aggregations.

Anyway, when doing synchronous queries it always makes sense to put an upper limit to the execution time, otherwise the user might decide your application is "broken" while waiting for it to finish for a minute or two.

But then when your db requests are async and the UI shows a looping progress indicator with a nice Cancel button then there is no reason to put timeouts to your queries, just let them run until the user decides they can no longer tolerate the delay or in case of long reports just expect it to take longer and relax.

So, either do cancel button or (configurable) timeouts.

cheers,
</wqw>

[jmcilhinney]

add a couple of seconds to the timeout setting of the connection

Nope. the connection timeout is only about how long it takes to connect tot he database. For long-running queries, you need to increase the timeout on the command.

[szlamany]

Hi all,

I've seen quite a few posts on fixing a timeout from a VB.NET program. Usually, add a couple of seconds to the timeout setting of the connection, or add an index to make the query go faster.
But I can't help thinking (and in fact I am encountering those right now) that there will always be queries who just take longer, due to their complexity, the amount of data to process, etc.
Isn't it possible and even -in some cases- normal that there are queries that run for a minute and a half?


Hope you can shed some light on this, thanks in advance!
Martin

I use a technique in my applications that usually have me setting the TIMEOUT to 0 (NO timeout).

For instance, in my payroll application the actual calculation of the payroll is done in a stored procedure - and this calc can take some time (1000+ employees not uncommon). Records get loaded into PayHistory and PayCheck tables (and several other deduction and distribution tables).

As a part of a payroll application there are lots of reports that need to be run - Deduction Registers, Distribution Registers, Detail Registers.

All of these reports are also done in SPROCS, and each of these SPROCS first checks to see if the payroll calculation has been committed (records appearing in the PayHistory table) and if it finds it has not, the calculation SPROC is called with a BEGIN TRAN transaction. Then the query is run for the report output and then the calculation is ROLLBACK'ed.

This means that all my payroll reports can be run in a PRE-CHECK mode - reports and totals can be verified and reconciled against other reports and what not.

Then when done, the calculation SPROC is run and COMMITTED - and all those reports re-printed and filed away for that payroll cycle.

I've used this same technique in SO many other places - scheduling students into classes in a high school, assigning student damage charges for college dorm housing.

[MartinDH]

Welcome to VBForums

Thanks, I appreciate it!

[MartinDH]

Nope. the connection timeout is only about how long it takes to connect tot he database. For long-running queries, you need to increase the timeout on the command.

Sorry, I did mean command

[MartinDH]

I use a technique in my applications that usually have me setting the TIMEOUT to 0 (NO timeout).

Thanks, that's worth a try, that seems like a real easy modification.

[MartinDH]

That would be pretty normal for reporting queries, esp. multi-year aggregations.

But then when your db requests are async and the UI shows a looping progress indicator with a nice Cancel button then there is no reason to put timeouts to your queries, just let them run until the user decides they can no longer tolerate the delay or in case of long reports just expect it to take longer and relax.

Async, seems to me like the most structured solution. I have -briefly- looked into that some time ago, but found it hard because we're using a 3-tier architecture, with DO's and BO's. But I better look into that again.
Thanks!