How to Increase the Speed of SQL Queries?

SQL (Structured Query Language) is the most widely used programming language for creating and designing relational databases, and for the fulfillment of various operations on the data. Many times developers fail to understand the importance of query optimization. The speed of SQL queries or the performance of the database application depends on the efficient SQL queries. This article will explain how to increase the speed of SQL queries.

1. SQL Query Optimization
2. Executing Stored Procedures in SQL Server
3. Subquery Unnesting
4. Make joins more efficient by removing calculated fields
5. Use of “OR” in the Join Across Multiple Columns
6. Always Check Resource Usage
7. Use Tuning Advisor

SQL Query Optimization

Poor database design is responsible for poor database performance. So production and implementation of data is a completely different responsibility. After the creation of a database, SQL query optimization to increase the speed of SQL queries is also critical. A stable database configuration gives the most high-grade performance during data administration.

For example, in Oracle, the Cost Based Optimizer (CBO) applies numbers and statistics to determine which performance plan to utilize. If these statistics are wrong the option selected by the CBO may be wrong. For this purpose, these statistics must be revived constantly. The following query will help you accomplish this purpose.

SELECT eno, ename, eage, job FROM emp;

The above query is faster than using ‘*’ wildcard. So, it is essential to always use the column names instead of a wildcard in your SQL queries.

Executing Stored Procedures in SQL Server

Many SQL developers only apply the Connection object’s Execute plan to perform stored procedures. This saves the output to a recordset if required:

Dim oRS As ADODB.Recordset, oCN As New ADODB.Connection oCN.Open “MyFileSource” Set oRS = oCN.Execute(“EXEC sp_NewProcedure(‘EmpSalary’)”)

The method used above runs quite properly but it fails to take full benefit of ADO’s capacity to combine efficiently with SQL Server. ADO gives an object that developers can utilize to run stored procedures. This object is a command object. This object gives the most desirable performance for running stored procedures. The following is the query:

Dim oRS As ADODB.Recordset, oCN As New ADODB.Connection
Dim oCmd As New ADODB.Command oCN.Open “MyFileSource”
oCmd.ActiveConnection = oCN
oCmd.CommandText=”sp_NewProcedure” 
oCmd.CommandType=adCmdStoredProc oCmd.Parameters.Append 
“EmpSalary” Set oRS = oCmd.Execute 

Subquery Unnesting

The unnesting of a subquery is crucial if you want to improve the speed of your SQL query. In unnesting, the SQL optimizer converts a nested SQL query into a similar join statement to boost its speed. This change allows the optimizer to analyze the subquery tables throughout the access route, join, and range. The optimizer can execute this change only if the output join statement is confirmed to return the corresponding rows as the primary statement, and if subqueries do not include the aggregate functions.

For example,

SELECT *

FROM emp WHERE deptno IN ( SELECT deptno

FROM dept );

Because the emp.deptno column is a primary key, the optimizer can reconstruct the complicated query into the following join condition that is confirmed to give the same data.

SELECT emp.*

FROM emp, dept

WHERE emp.deptno = dept.deptno;

If the optimizer cannot change a complicated statement into a join condition, it chooses execution methods for the parent condition and the subquery as if they were different statements. The optimizer then runs the subquery and utilizes the rows delivered to run the parent query. To increase the speed of the entire execution method, the optimizer adjusts the subplans efficiently.

Make joins more efficient by removing calculated fields

This is a different and easy to use method depending on the changes to the schema. This can be achieved by designing a field with the computed values applied in the join on the table. Given the following SQL statement:

FROM salesdata s

JOIN funds f ON((year(s.date_of_sale)* 100) + month(s.date_of_sale)) = f.funds_year_month

Now, the speed and performance of the above join query can be increased by inserting or joining a column with the year and month in the salesdata table. The modified SQL statement that increases the speed of the query would be as follows:

SELECT * FROM PRODUCTS FROM sales a

JOIN budget b ON    a.sale_year_month = b.budget_year_month

Also, always remember to use the WHERE condition efficiently. If you are aiming to bypass specific column values then do not apply the NOT condition and rather use BETWEEN operator. Likewise, if you are applying a LIKE condition with a wild card then make sure to create the statement so that it chooses the least number of rows.

 Use of “OR” in the Join Across Multiple Columns

The database can effectively separate a data set by applying indexes with the WHERE clause or any sequence of filters that are divided by an AND operator. By being selective, these actions get data into smaller parts, till only our output set persists. In the case of “OR,” it is a completely different thing. It is comprehensive, SQL Server cannot treat it in a particular order. Instead, each element of the OR must be assessed individually. When this costly process is finished, the results can then be combined and returned.

The situation in which OR functions poorest is when many columns or tables are included. Programmers not only require to assess each element of the OR condition but require to understand that path through the other tables within the query. So, it is always good to split a short and uncomplicated SQL query into a more long-drawn query:

SELECT

PROD.ProductNO,

PROD.PName

FROM Production.Prod PROD

INNER JOIN Salesdata.SalesDetail DETAIL

ON PROD.ProductNO = DETAIL.ProductNO

UNION

SELECT

PROD.ProductNO,

PROD.PName

FROM Production.Prod PROD

INNER JOIN Salesdata.SalesDetail DETAIL

ON PROD.rowguid = DETAIL.rowguid

Always Check Resource Usage

Resource usage is a crucial portion when it comes to SQL query speed and performance. Since developers can’t always fix what they fail to scale, they clearly should watch resource usage. For example, If you are working as a SQL programmer and you are using Windows, then always use the System Monitor tool to analyze the speed and performance of SQL Server. It allows you to inspect SQL Server objects, speed counters, and the performance of other objects.

Use Tuning Advisor

Another valuable method for increasing the speed and performance of the SQL query is to examine the execution of Transact-SQL statements that are operated against the database you plan to optimize. For example, you can utilize the Database Tuning Advisor to examine the speed and performance connections. In reality, the tool works beyond that. It also suggests steps you should adopt based on its investigation. For example, it might suggest you to design or delete indexes.

Every database has a peculiar method of performing a query, named a query execution plan. If a complicated SQL query is slow then examine the query execution plan of the implementation of SQL. If you notice a complete table scan on a big table then decide to index the property through which the table is obtained. Indexing the table allows programmers to find a row instantly, but can reduce speed for smaller database tables.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.