What is Performance tuning
Performance tuning in SQL is the practice of ensuring SQL queries run as fast as possible.
SQL Performance tuning can be difficult, especially when working with large amount of data. SQL performance tuning is handled by Database Administrators (DBA’s) in large corporations.
However, to improve the speed of data retrieval with SQL queries, the following points will be of help:
1. Define SELECT fields instead of SELECT *
Selecting exact fields from a database helps speed up our SQL query. The SELECT statement directs the database to query only required fields data needed by the data consumer.
An example below for defining SELECT fields:
SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers
2. Use of Wildcards to pull a data set
Wildcards are helpful when searching for plaintext such as names, locations that contain a letter inserted in your wildcard syntax.
To pull Cities in a field that have ‘LO’:
SELECT City
FROM Customers
WHERE City LIKE ‘%LO%’
This query will pull results of Louisiana, Los Angeles and other cities linked to the table with letters LO.
3.The use of Indexes
ndex when added to your query has proved to reduce the amount of time for retrieving data, thereby increasing query performance.
An index tracks a targeted subset of a table’s data so that selecting and ordering can be done much faster without the server having to look through every last bit of data for that table.
Types of Index:
Clustered and
Non-Clustered Index
4. Avoid multiple joins in a single query
Using multiple joins (outer, inner, cross apply) and other complex sub-queries reduce the choice for optimizer to decide the join type and order.
5. Use Temporary tables (#temp)
For a Mid-level to Advanced level SQL developer, #temp table is helpful when it comes to running queries with many joined tables.
Creating a temporary table (#temp) speeds up your query but adds ambiguity to your SQL code. Usually data is transferred into your #temp table.
An example of #temp table:
SELECT Name, Sex, Age
INTO #db (temporary table)
FROM Hospital
WHERE Age >6
To retrieve data from the temporary table (#db), simply use this syntax:
SELECT *
FROM #db
In summary, we have listed how SQL queries can be improved to speed up data retrieval process. There are many more areas for improving the SQL query performance like eliminating unnecessary data, creating joins with INNER rather than WHERE, use WHERE instead of HAVING to define filters, DISTINCT and UNION should be used if necessary also ORDER BY clause is mandatory in SQL if the sorted result set is expected. ORDER BY clause is mandatory in SQL if the sorted result set is expected etc.
