An extract query opens its database connection successfully but fails after 30 seconds with a command timeout error. The query scans a very large table without using any filter on an indexed column, although an index exists on the date column the extract needs. What is the best approach?
Select an answer to reveal the explanation.
Short Explanation
Two different clocks are ticking here: one for getting connected, one for the query to finish. You already got connected, so it's the query clock running out, and the real cure is making the query faster, not just giving it more time. Setting the timeout to unlimited is the trap; you'd just hang forever instead.
Full Explanation
Client libraries typically enforce two separate limits. A connection timeout bounds how long the client waits to establish a session with the server, covering network reachability, listener response and authentication. A command timeout bounds how long a single statement may run after the connection exists. Because the connection opened and the failure occurred after the statement ran for 30 seconds, this is a command timeout. The underlying cause is an inefficient query: scanning the whole table instead of using the available index on the date column. Rewriting the extract to filter on that column, and possibly extracting incrementally, reduces the run time, and the command timeout can be raised modestly afterward if a legitimate workload still needs it. Raising the connection timeout changes a limit that is not being hit, so the error would continue. An unlimited command timeout hides the performance problem and lets runaway queries hold locks and resources indefinitely, harming other workloads. Restarting the server disrupts every user and does nothing for a query that is slow by design; the same plan would run again with the same result. Exam caveat: timeouts can also stem from blocking, so check for lock waits as well as the query plan. Operational check: review the execution plan before and after the change to confirm an index seek or range scan replaces the full scan, and record the new run time.