Data Query

Updated on 2024-08-30 GMT+08:00

View PDF

This topic describes the rules and suggestions for querying Doris data.

Doris Data Query Rules

When you are using the data query code, retry the query and issue the query again if the query fails.
If the enumerated value of the constant in exceeds 1000, you must use a subquery.
Do not use the Statement Execution Action REST APIs to execute a large number of SQL queries. These interfaces are used only for cluster maintenance.
When dealing with query results exceeding 50,000 records, consider using either JDBC Catalog or the OUTFILE method to export the data. Otherwise, allowing a large amount of data to accumulate on the front end (FE) can impact cluster stability.
- When performing interactive queries, export data using pagination with an offset limit. You can achieve this by using the ORDER BY command.
- If data is exported for a third party, use outfile or export.
Utilize Colocation Join for joining more than two tables with over 300 million records.
Avoid using select * for querying large tables with hundreds of millions of records and specify the required fields instead.
- Do not use select * when you use SQL Block.
- For high-concurrency point queries, enable row-based storage (supported by Doris 2.x) and use PreparedStatement.
Bucketing conditions must be set for queries of tables of hundreds of millions records.
Do not perform full-partition scan on partitioned tables.

Doris Data Query Suggestions

When an INSERT INTO SELECT statement inserts over 100 million data records, divide them into smaller batches for execution.
Do not use OR as a JOIN condition.
Do not frequently delete and modify data. Instead, delete data in batches occasionally with conditions to improve system stability and deletion efficiency.
To return som data after sorting a large amount of data (more than 500 million records), reduce the data range for sorting. Sorting a large amount of data affects query performance. The following is an example:
Instead of using from table order by datatime desc limit 10, use from table where datatime='2023-10-20' order by datatime desc limit 10.
Pay attention to the following points when using parallel_fragment_exec_instance_num to optimize query task performance:
This parameter determines the maximum number of fragments that can run simultaneously at the session level. Too many concurrent fragments will use up a significant amount of CPU resources. You can leave this parameter blank. If you need to set this parameter to accelerate query speed, comply with the following rules:
- Do not set this parameter to take effect globally, that is, do not use the set global command to set this parameter.
- Set this parameter to an even number (2 or 4). The maximum value cannot exceed half of the number of CPU cores on a single node.
- Check the CPU usage before you set the parameter. You can set this parameter only when the CPU usage is less than 50%.
- If you use insert into select to insert a large amount of data, do not set this parameter.

Parent topic: Doris

Previous topic: Naming Conventions

Next topic: Data Import

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot