We have a huge (part-VBA, part-.NET-based) LOB application, with thousands of (different) SQL statements littered all over the code¹. Currently, there is one global "Query Timeout" that can be configured. Since the application (among others) allows the user to create quite complex reports, this default timeout is set very high (5 minutes).
This, however, leads to problems when, during normal operation, undetected deadlocks² occur: All machines involved freeze for 5 minutes, until the timeout resolves the issue. So, clearly, different timeouts are suitable for different situations: For a complex report, a few minutes might be OK, whereas for querying a single, indexed database value, a few seconds should suffice³.
What's a good strategy to implement this? My first idea would be to categorize SQLs into "fast", "medium" and "slow" and, thus, have three different timeouts that can be configured. What are your experiences with this issue? How have you solved it and how did it work?
¹ Yes, I know that there are object-relational mappers available, but (1) we do have a lot of legacy code and (2) I don't think it makes a difference to the question.
² An undetected deadlock is a deadlock that is not resolved by the database engine (for example by detecting the deadlock and aborting one of the transactions involved). Such deadlocks can occur under certain circumstances (ask me, if you want details, but I don't think it's relevant for the question).
³ I know that such a DB access should only take a few ms, but the single, quick query might need to wait for a resource to be released by a longer-running transaction.