登录查看更多内容

What are the best practices for querying tables with millions of rows?

由人工智能和领英社区提供技术支持

Querying tables with millions of rows can be a challenging task for database administrators. Large tables can slow down performance, consume resources, and complicate analysis. However, with some best practices, you can optimize your queries and get the results you need faster and easier. In this article, we will cover six tips to help you query tables with millions of rows more efficiently and effectively.

此文章中的业界达人

由社区从 7 条内容中精选。了解更多

Amin Balouchi

Data Team Lead at Soshyant
Matras Radoslaw

IBM Db2 LUW Consultant

1 1. Use indexes

Indexes are structures that store a subset of the table's data in a sorted order, making it easier to locate and retrieve specific rows. Indexes can speed up queries that use filtering, sorting, joining, or aggregating operations. However, indexes also have some drawbacks, such as taking up space, requiring maintenance, and affecting insert, update, and delete operations. Therefore, you should use indexes wisely, choosing the most relevant columns and avoiding over-indexing.

添加您的观点

Matras Radoslaw

IBM Db2 LUW Consultant
举报内容
In my experience, I noticed that indexes are the most important matter when it comes to querying databases. The standard practice is to index columns, which are part of the where clause for heavy queries. However, there's more than meets the eye. Instead of focusing on a particular query, I'd recommend paying attention to a transaction. That allows the database to keep the health ratio between the performance of queries and IUD operations. E.g. Adding 5 indexes increases performance by 90% while adding only 2 gives a similar result - 88%. Of course, it's case-specific. One should also keep in mind there are special types of indexes: cluster, partitioned, included, based on regular expression, etc.

已翻译

赞

2 2. Limit the number of columns

When querying tables with millions of rows, you should only select the columns that you need for your analysis. Selecting all columns or using the wildcard (*) can result in unnecessary data transfer, network congestion, and memory usage. Moreover, some columns may have large or complex data types, such as text, JSON, or XML, that can further slow down the query. Therefore, you should limit the number of columns to the minimum required and avoid selecting large or complex data types.

添加您的观点

Matras Radoslaw

IBM Db2 LUW Consultant
举报内容
One thing I've found helpful is being specific. Use what you need, not what you want. In some cases, the usage of a course would be very beneficial. In the case of complex data types, one should keep in mind that an enterprise database has more than an SQL engine. Usually, there is an XML and JSON engine available as well. Instead of fetching an entire column, one should use that specific engine.

已翻译

赞

3 3. Filter the data

Filtering the data is another way to reduce the amount of rows that the query has to process. Filtering can be done using the WHERE clause, which allows you to specify conditions that the rows must satisfy to be included in the result. You can also use the LIMIT clause, which allows you to specify the maximum number of rows that the query should return. However, you should be careful not to use too many or too complex filters, as they can also affect the query performance.

添加您的观点

Matras Radoslaw

IBM Db2 LUW Consultant
举报内容
Filtering the data is crucial when working with large datasets. One of the rules that helps me here is the more complex the filtering the more lucid query must be. In order to achieve it one can use views, UDFs, set operators, and common table expressions. In my experience, I've found SQL engines are vulnerable to OR operators. The final thing is to keep metadata up-to-date. If the database engine doesn't have detailed statistics about a table it can't use advanced optimizer functions.

已翻译

赞

4 4. Use partitions

Partitions are segments of a table that are stored separately and have the same schema. Partitions can improve query performance by dividing the table into smaller and more manageable chunks. You can partition a table based on a column or a range of values, such as date, category, or region. By doing so, you can reduce the scan time and the I/O cost of the query, as it only has to access the relevant partitions. However, partitions also have some limitations, such as requiring additional storage, maintenance, and planning.

添加您的观点

Matras Radoslaw

IBM Db2 LUW Consultant
举报内容
Whenever it's possible and it makes sense one should always use table partitions. Query optimizer scans ranges and only fetches data from affected partitions. The important matter is to keep partition indexes in such cases. If there's no column with a reasonable partion key then there's always the option to use the MODULO operator along with the primary key. Still, it's better to manage 10 tables with 50 million rows than one with 500.

已翻译

赞

5 5. Use subqueries

Subqueries are queries that are nested inside another query and return a subset of the data. Subqueries can help you query tables with millions of rows by breaking down the problem into smaller and simpler steps. You can use subqueries to perform calculations, filter data, join tables, or aggregate data. However, you should be careful not to use too many or too deep subqueries, as they can also affect the query performance and readability.

添加您的观点

Amin Balouchi

Data Team Lead at Soshyant
举报内容
Most of the times, using subqueries has bad impact on the performance of the query and it's not a recommended workaround. There is a better option like common table expression that has a significant improvement on a cost of the query.

已翻译

赞
Matras Radoslaw

IBM Db2 LUW Consultant
举报内容
Enterprise databases usually re-write queries, so in most cases subqueries end up as join tables, or some other optimal form. Though it's good to take a look at the query plan. Of course, it's case-specific. Anyway, simple and elegant subqueries are welcome, but complex ones are better to avoid. In such case, I'd recommend views, UDFs, and CTE/

已翻译

赞

6 6. Use batch processing

Batch processing is a technique that involves processing data in batches or chunks, rather than in one go. Batch processing can help you query tables with millions of rows by avoiding timeouts, errors, or resource exhaustion. You can use batch processing to perform operations such as loading, exporting, updating, or deleting data. However, you should also consider the trade-offs of batch processing, such as latency, complexity, and concurrency.

添加您的观点

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

加载更多内容

Database Administration

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

What are the best practices for querying tables with millions of rows?

1

2

3

4

5

6

7

1 1. Use indexes

2 2. Limit the number of columns

3 3. Filter the data

4 4. Use partitions

5 5. Use subqueries

6 6. Use batch processing

7 Here’s what else to consider

Database Administration

给文章评分

感谢您的反馈

更多Database Administration相关文章

更多相关阅读内容

What are the best practices for querying tables with millions of rows?

1

2

3

4

5

6

7

1 1. Use indexes

2 2. Limit the number of columns

3 3. Filter the data

4 4. Use partitions

5 5. Use subqueries

6 6. Use batch processing

7 Here’s what else to consider

Database Administration

给文章评分

感谢您的反馈

查看其他技能