Crucial Tips to Improve MySQL Database Performance Pt. 2
This blog is a continuation of the two-part series where we have been discussing some interesting tips to help users improve performance of SQL query. Without more ado, let’s continue where we stopped at in the previous blog:
Tip 7: Steer clear of these three clauses wherever you can -
- GROUP BY
- DISTINCT
- ORDER BY
There is a simple reason behind this: the database engine has to work extra before it returns the outcome on the screen. Whenever you mention these clauses in your statements, it arranges the data in the work table it creates. This reduces performance.
Tip 8: Keep an eye on your indexes. If you don’t have enough, make more, but be careful not to overdo it. Check whether there are indexes on the fields that you are going to make changes to through the JOIN and WHERE clauses of your query.
This is important because, without indexes, the database engine will conduct a scan of the entire table. You may require SQL query optimizer tools for SQL Server more often than not. This will not only be unnecessary but will also take a lot of time and resources. In fact, a query that requires a table scan shows the worst performance in comparison with other queries, except when the table is extremely small.
Tip 9: Try not to change the datatype when you use WHERE and JOIN clauses in your query. It may be difficult to do based on the permissions you have regarding schema changes, but it is important nonetheless.
That’s because the database has to perform conversions immediately before making comparisons with the data if you choose different data types. This is true even in the case of indexed fields if you want to improve performance of SQL query. However, if you cannot help but use mismatched data types, see if you can cast the bigger one over its smaller counterpart.
Tip 10: Prevent the use of calculated values wherever WHERE and JOIN are involved. You can work on performance improvement by inserting a field that contains those items instead. On the other hand, adding calculated values will force the database to perform on-the-spot calculations prior to comparing. This will occur regardless of whether or not the table has indexes.
Tip 11: Don’t use too many joins whenever you write a statement. When there are multiple joins in addition to elements such as outer joins, cross applies and other complications, there are a few resulting issues.
To begin with, there is a sharp rise in the number of execution plans that SQL query optimizer tools for SQL Server can choose from. As a result, there will be fewer options for the optimizer to pick from regarding the type and order of the join.
There are situations in which the optimizer has no choice but to pick nested loop joins. It will do this no matter what the consequent performance of the query will be, given the presence of other subqueries or complex components. Moreover, the database has time and performance limitations, therefore, it doesn’t try to build the best-performing plans, generating so-so ones instead.
Tip 12: Refrain from using TVFs — Table-Valued Functions that work for several queries. They are also called multi-query TVFs and are more expensive than the inline ones. The reason behind this is the manner in which SQL Server treats both of these. It uses inline TV functions like views, opening them in the main statement.
However, it assesses multi-statement TV functions separately and returns their results in temp work tables. The extra efforts make them more expensive.
Tip 13: Data compression is quite useful, so use it wherever you can to improve performance of SQL query. It helps in case the database in case of input-output bound but not CPU bound operations.
Conclusion
When you analyse slow queries, you will find that reading data takes most of the time during query execution. That’s why many of these tips focus on limiting the amount of time it takes to scan the information the user needs.
If you require a professional query optimizing tool for SQL Server, check out our range of options. These will help you eliminate I\O level bottlenecks and more.
