:Troubleshoot SQL query timeout errors

Problem description

When you run an SQL query in the SQL Console of Data Management Service (DMS), the query times out and displays the following error message.

select count(*) from table_name where gmt_create>='2020-01-01';
Failed, Details: Operation terminated (user interruption or execution timeout)
1. Add an indexed column to the query conditions to improve query performance.
2. Narrow the query scope as much as possible.
3. For instances in Secure Collaboration mode, submit a schema design ticket to add an index to the query column.
4. Reset the query timeout by editing the instance.
TraceId : 0bc059b716002426281173397e58b3

Solution

Use one of the following methods to resolve this issue:

1. Add a condition that uses an indexed column to improve performance. For example, if an index exists on the user_id column, add a condition for that column to your query.

   select count(*) from table_name where gmt_create>='2020-01-01' and user_id = '1000';

2. Narrow the query scope as much as possible. For example, add a condition for the gmt_create column to narrow the query's time range.

   select count(*) from table_name where gmt_create>='2020-01-01' and gmt_create<'2020-04-01';
   

3. For an instance in Secure Collaboration mode, use the schema design or data change features to add an index to a query column. Then, add a condition to your query that uses the new index.

   • Schema design: Add an index to the gmt_create column. For more information, see schema design.

   • Data change: Use the following SQL statement to add an index to the gmt_create column.

     alter table table_name add index idx_gmt(gmt_create);

4. Reset the query timeout in the instance settings. For more details, see Edit Instance.

   1. On the Data Management Service (DMS) console, find your instance in the left-side navigation pane, right-click the instance ID, and then select Edit Instance.

   2. Click Advanced Settings, adjust the query timeout value based on your needs, and then click Submit.

      Note

      Note: In this example, the query timeout is set to 60s.

Applies to

• Data Management Service (DMS)