Shulou(Shulou.com)05/31 Report--

This article is to share with you what are the six simple tips for optimizing the performance of SQL SELECT sentences. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

Tuning the performance of SELECT statements can be a very time-consuming task, and in my opinion it follows the Pareto principle. A 20% effort is likely to give you an 80% performance improvement, while it may take 80% of your time to get another 20% performance improvement. Unless you work at Venus, where every day equals 243days on Earth, delivery deadlines are likely to leave you with insufficient time to tune SQL queries. Based on my years of experience writing and running SQL statements, I began to develop a checklist that I can refer to when I try to improve query performance. Before making a query plan and reading the database documents I use, I refer to them, which can be complex at times. My checklist is by no means comprehensive or scientific, it's more like a conservative calculation, but I can say that following these simple steps does improve my performance most of the time. The checklist is as follows.

Check the index

All fields used in the WHERE and JOIN sections of the SQL statement should be indexed. Conduct this 3-minute SQL performance test. No matter what your grades are, be sure to read the results with information.

Limit the size of the working dataset

Check the tables used in those SELECT statements to see if you can apply the WHERE clause to filter. A typical example is that a query executes well when there are only a few thousand rows in the table. But as the application grew, the query slowed down. The solution may be very simple, just restrict the query to view the data for the current month. When you have a subquery in your query, be careful to use filtering on the internal statement of the subquery, not on the external statement.

Select only the extra fields you need

It usually increases the texture of the returned data, resulting in more data being returned to the SQL client. In addition:

When using applications with reporting and analysis capabilities, sometimes poor reporting performance is due to the fact that the reporting tool must aggregate data received with detailed forms.

Occasional queries may run fast enough, but your problem may be a network-related problem because a large amount of detailed data is sent to the report server over the network.

When using a column-oriented DBMS, only the columns you choose will be read from disk. The fewer columns you include in your query, the less IO overhead you will have.

Remove unnecessary tables

The reason for removing unnecessary tables is the same as the reason for removing unwanted fields in the query statement. Writing SQL statements is a process that usually requires a large number of iterations to write and test SQL statements. During development, you may add tables to the query, which may have no effect on the data returned by the SQL code. Once the SQL runs correctly, I find that many people don't review their scripts and delete tables that have no effect on the final returned data. By removing JOINS operations with unnecessary tables, you reduce a large number of processes that the database must perform. Sometimes, just like removing a column, you will find that your reduced data has been returned through the database.

Remove external join query

This is easier said than done, and it depends on the impact of changing the contents of the table. One workaround is to delete the OUTER JOINS operation by placing placeholders in the rows of both tables. Suppose you have the following tables that ensure that all data is returned by defining OUTER JOINS:

The solution is to add a placeholder to the row of the customer table and update all null values in the sales table to the placeholder.

Not only have you removed the dependence on OUTER JOIN operations, but you have also standardized how salespeople without customers behave. Other developers do not have to write additional statements, such as ISNULL (customer_id, "No customer yet").

Delete calculated fields in the JOIN and WHERE clauses

This is another technique that may sometimes be easier said than done, depending on the permissions you have to change the table schema. The calculated field used in the join statement can be created in the table as a new field. The following SQL statement is given:

Adding one column by year and month in the sales table can improve performance. The updated SQL statement will be as follows:

The above suggestions can be summed up as follows:

Check the index

Operate on the smallest dataset required

Remove unnecessary fields and tables

Remove computations from your JOIN and WHERE clauses

These are the six simple tips for optimizing the performance of SQL SELECT statements. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.