What is "runaway query"?

Question

In the context of SQL, what does a runaway query mean?

Does it mean any query that runs wild when it takes too long? Or does it mean when it has some side-effects due to triggers?

Answer 1

I believe it means one that you execute and never returns. For example, doing a select statement that never comes back with results (or takes a really long time to do so).

Answer 2

A runaway query is a query whose execution time is taking longer than the execution time estimated by the optimizer. Runaway queries can lead to using up all of your processor cycles or other resources during its execution.

Answer 3

It's a query that starts running and never comes back (for some value of "never").

Usually it means that the query isn't using an index it's supposed to, or using a bad join method, or a bad join order, or doing a bunch of string conversion/comparison.

It is possible to write SQL queries that take weeks/years to perform.

Answer 4

When a query joins more rows than are necessary to the point of excess.

From Wikipedia: The language makes it too easy to do a Cartesian join (joining all possible combinations), which results in "run-away" result sets when WHERE clauses are mistyped. Cartesian joins are so rarely used in practice that requiring an explicit CARTESIAN keyword may be warranted. SQL 1992 introduced the CROSS JOIN keyword that allows the user to make clear that a cartesian join is intended, but the shorthand "comma-join" with no predicate is still acceptable syntax.

Answer 5

I apply this term specifically to a query that triggers, usually accidentally, behavior with runtime of unexpected complexity. If you expect a query to take O(n * m) (that is, a single join between two tables) and it takes O(n * n * m) then I would call it runaway, even if n * n * m is acceptably small in the instant case. More commonly this is experienced as a query expected to take O(log(n) * log(m)) taking O(n * n * m * m), which turns out to be unacceptably complex.

Answer 6

Runaway queries have a number of characteristics - pick some from:

• they produce giant result sets
• they lock lots of tables
• they result in lots of entries in the transaction log
• they consume vast ampounts of I/O and CPU resources

In all cases, they tend to prevent other users doing any useful work.

Answer 7

A runaway query is usually a query that would take a long time and / or a great deal of system resources (CPU, Memory, etc.) to complete.

Common reasons may be:

• Failure to use indexed columns
• Failure to properly join tables (so you get at least one cross product)
• Bad table statistics on used indexed columns
• Many string comparisons or manipulations

A "runaway" query may evenually come back, it would just take too long or too many system resources to make it worthwhile.

Answer 8

Just to mention that a runaway query could happen if a poorly written trigger is involved. Triggers must be able to process multi-record inserts/updates or deltes and some people have been known to do this through the use of a cursor rather than through the use of set-based language. This could casue a query to update a large number of records to runaway. Was recently able to reduce query time for a large set of records being updated from over 40 minutes to 40 seconds by replacing one such trigger.

Other causes include accidental cross joins (fixed with a distinct), nonsargable where clauses, use of subqueries or used defined functions, incorrect indexing, statistics which have not been updated, use of cursors, insufficient definition in the join or where. And on and on.