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

In this issue, the editor will bring you what are the simple skills to optimize the performance of SQL select sentences. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

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.

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 fields you need

Additional fields usually increase 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:

Customer_idcustomer_name1John Doe2Mary Jane3Peter Pan4Joe Soapcustomer_idsales_personNULLNewbee Smith2Oldie Jones1Another OldieNULLGreenhorn

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.

Customer_idcustomer_name0NO CUSTOMER1John Doe2Mary Jane3Peter Pan4Joe Soapcustomer_idsales_person0Newbee Smith2Oldie Jones1Another Oldie0Greenhorn

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:

FROM sales a

JOIN budget b ON ((YEAR (a.sale_date) * 100) + MONTH (a.sale_date)) = b.budget_year_month

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

SELECT * FROM PRODUCTSFROM sales a

JOIN budget b ON a.sale_year_month = b.budget_year_month

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

If all of these suggestions don't improve your SQL query performance, the last one is to move to Venus. All you need is a day to tune your SQL statements.

These are the simple tips for optimizing the performance of SQL select statements shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to 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.