Learn how to query faster in SQL

Tram Ho

When programming we often have to work with Sql query statements. So understanding and applying the optimal way of the statement is essential. Optimizing the command will help the system faster, meaning that users do not have to wait. When programming normally, we will get the results returned quickly. But the problem occurred when the project went into operation and the data increased day by day, resulting in cases of slow system response resulting in users having to wait and users unhappy about this.

Optimizing Sql statements is a necessity and is especially needed for systems with large data scales. When working with large-scale data, even the smallest changes can have a major impact on performance. So optimizing the retrieval statement is still quite a difficult job.

In this article I will talk about some ways to optimize to help increase the performance of Sql queries

  • Indexing: indexing logically
  • Select query: specify the field name in the SELECT statement instead of SELECT * select all the fields in the table.
  • Running queries: avoiding queries in a loop
  • Matching records: use EXITS () in case a record is found.
  • Subqueries: use Joins instead of SubQuery
  • Use DISTINCT and UNION only when needed
  • Use WHERE instead of HAVING.
  • Use the Stored Procedure instead of complex query trees
  • Avoid using indexed columns with functions
  1. Properly indexing Indexing helps to improve the speed of data retrieval operations on the table. However, for INSERTS, UPDATES, DELETES statements, indexing increases the execution time. so limit should not hit much. For tables that are only for reading data, the number of indexes can be higher. An index is a data structure that improves the speed of data retrieval operations on a database table. A unique index creates separate data columns that do not overlap. Proper indexing ensures faster access to the database, meaning you can select or sort rows faster. The following diagram explains the basics of indexing while structuring tables.

  1. Specify the field name in the SELECT statement instead of SELECT * select all the fields in the table

    Using SELECT * will retrieve unnecessary fields that lead to long query execution. The less data that is retrieved, the faster the query will run. Filter data as much as possible at the server. This limits the data sent to the client. Therefore, you should not write queries of the form:

    Should use :

  2. Avoid queries in a loop

    Do not write queries inside the loop which slows down the whole process

    Instead of writing the query in the loop as above, we can use the query as follows

  3. Use EXITS () in case the record was found

    To check if a record exists in the table we can use EXITS () or COUNT () but using EXITS () will be more optimal in this problem. Because the EXITS () function will stop as soon as a matching record is found, COUNT () will scan it all and count the matching records.

    should not:

    Candlestick

  4. Use Joins instead of SubQuery

    SubQuery depends on external queries (not independently) that reduce the speed of the whole process. With MySQL there is a mechanism to support Join, making it faster than Subquery, and so we should use Join, restricting the use of Subquery. (refer)

    should not use:

    should use

  5. Use DISTINCT and UNION only when needed

    Using union and distinct in case of unnecessary may lead to a decrease in query performance. Instead of using UNION, you can use UNION ALL for better results.

  6. Use WHERE instead of HAVING.

    HAVING clause should be avoided when possible. The HAVING clause is used to restrict the results returned by the GROUP BY clause. When using the GROUP BY clause with the HAVING clause, the GROUP BY clause will split all lines into sets consisting of multiple sets of lines and their values. The HAVING clause will then restrict the undesired output of those collections. In many cases, it is possible to write a SELECT statement that only has the WHERE, GROUP BY clause without the HAVING clause. This will improve the speed of your query.

  7. Use the Stored Procedure instead of complex query trees

    Stored Procedure executes code faster and reduces bandwidth loading.

    • Faster execution: Stored Procedure will be compiled and stored in memory when created. That means that it will execute faster than sending each SQL statement. Because if you send each code several times, the SQL will have to be recompiled many times, which is a lot of time compared to precompiling.
    • Reduce bandwidth load: If you send many SQL statements through the network to SQL will affect the performance of the line. Instead of sending multiple times, you can gather SQL statements into a Stored Procedure and only have to call it once over the network.
    • In addition, writing the Stored Procedure will facilitate decentralization and better security
  8. Avoid using indexed columns with functions

    We use the YEAR function together with the AccountCreatedOn column, which will not allow the database to use the index in the AccountCreatedOn column because index values ​​of AccountCreatedOn rather than YEAR (AccountCreatedOn).

    Therefore we should use

References

Share the news now

Source : Viblo