Optimize SQL databases, and compile

xiaoxiao2021-03-06  100

1, test different statements, compare

2. The easiest way to avoid speed reduction when using Left Join is to design a database as much as possible around them. For example, suppose that a product may have a category or there is no category. If the Products table stores its category ID, without the category of a particular product, you can store NULL values ​​in the field. You must then perform Left Join to get all products and their categories. You can create a category that is "No Category", which specifies that the foreign key relationship does not allow NULL values. By performing the above, you can now retrieve all products and categories using Inner Join. Although this seems to be a variable method with excess data, it may be a very valuable technology because it eliminates Left Join that consumes more resources in the SQL batch statement. All this concept in the database can save you a lot of processing

3. Another technique that improves efficiency is to use the DistINCT keyword to find a separate report of the data line to replace the Group By clause. In this case, the SQL efficiency of the Distinct keyword is higher. Please use the group by in the case where the aggregate function (SUM, COUNT, MAX, etc.) is required. Also, if your query always returns a unique line, don't use the Distinct keyword. In this case, the Distinct keyword will only increase system overhead.

转载请注明原文地址:https://www.9cbs.com/read-124394.html

New Post(0)