Hello everyone. When we carry out work on optimization of a database environment in general, we usually review the database parameters, waits, indexes, etc. This type of question is important, but it becomes more important in some cases, the revision of the consultations. That is why we are going to teach you this type of tips for optimizing SQL queries . These tips are useful for SQL Server, but mostly they can be extrapolated to other relational databases like MySQL. Let’s get started.
How to optimize SQL Server queries?
Although there are several factors that can influence the performance of a query, such as indexes, bottlenecks, locks, server resources … we are going to show you some general criteria to be able to optimize an SQL query as much as possible, regardless of the server resources or other types of problems that indirectly affect the execution of the query.
Always specify the schema / owner
Without going into much detail, in SQL Server, all tables have a schema / owner to facilitate the management of permissions for example, or have a greater organization of the tables. In case of not indicating any (which is usual), it is used dbo .
If we go to SSMS we can check how the tables have all of them in front (unless another schema has been specified), the schema dbo.
In an application query we can have:
select id, name from students
If we execute it internally, the engine will first look for the schema to which it belongs among all those available and then it will execute the query adding the corresponding schema to it. Therefore, the most optimal would be to put it directly in the schema query to avoid that unnecessary loss of resources. Remaining as follows:
select id, name from dbo. students
A priori it may not be that important, but if we have a large application with a thousand tables and several schemas, it can be a great waste of resources. It is also especially important in queries with several joins, since if we use 50 tables in a statement and we do not specify the schema, it has to locate those 50 tables by previously searching among a thousand tables, following the example.
Use column names instead of the * operator
This is very common, especially when it is in development (although it is also seen in production). Continuing with the table of students from the previous example, we see that only id and name have been specified because they are the fields we need. If this table had 5000 students and 30 fields, the information to be returned would be considerable and we would only need only 2 fields of the 30 that the table has. When using * it goes through all the records, of the whole table, showing all the fields. That is why it is important to limit the information we want to return as much as possible.
Optimize SQL Server queries that use nullable columns
Use NOT EXISTS instead of NOT IN . When using NOT IN with null values, it also compares them. However, if NOT EXISTS is set, it only finds results in the rows that do not have NULL values.
Names of stored procedures
Prevent stored procedures from starting with sp_ or SP_, as this will cause the engine to look for them within the “master” system database if the database and schema are not specified. For this we must call its execution as:
Use SET NOCOUNT ON in DML operations
When we use DML queries (INSERT, UPDATE, DELETE …), the engine returns the number of records affected. If they are few, you may not notice the difference. But if it is millions of records or a large number, using this parameter will prevent the engine from “counting” all the affected records, thus saving the engine a lot of effort.
Avoid using GROUP BY, ORDER BY, and DISTINCT unless necessary
For this type of operations, the engine creates a temporary table, orders the results according to the criteria established in it, and returns the result in an ordered way. This delays the return of results, so, unless necessary, it is better to avoid them.
This is only part of what needs to be reviewed in an environment to optimize SQL Server queries, but if you want us to review your environment or specific queries. You can contact us without obligation in our contact page . We are database experts with extensive experience in SQL Server, Oracle, MySQL, PostgreSQL …
Follow GPS on LinkedIn