This is the second blog in our two-part series to explain the best ways to optimize your database, which is best done by enhancing the SQL queries being used. Without much ado, let’s pick up where we left off –
Give Preference to WHERE, instead of HAVING (when defining filters)
A query is efficient when it saves resources by fetching only what’s needed from the database. According to the Order of Operations defined in SQL, WHERE queries are calculated before HAVING statements.
Therefore, it is advisable to give preference to WHERE over HAVING when the goal is to filter a query on the basis of conditions for greater efficiency.
For instance, let us suppose a hundred sales have been made during the year 2019, and a user wishes to put in a query to determine what the number of sales was for the same time period. They may write something like this:
SELECT Clients.ClientID, Clients.Name, Count(Sales.SalesID)
FROM Clients
INNER JOIN Sales
ON Clients.ClientID = Sales.ClientID
GROUP BY Clients.ClientID, Clients.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2019# AND #12/31/2019#
This statement would return at least a thousand sales records from the Sales table, then filter these thousand records to find the hundred records generated in the year 2019, and lastly, tally the data in the dataset.
If we compare the above with the same instance using the WHERE clause instead, there is a limit placed on the number of records fetched:
SELECT Clients.ClientID, Clients.Name, Count(Sales.SalesID)
FROM Clients
INNER JOIN Sales
ON Clients.ClientID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2019# AND #12/31/2019#
GROUP BY Clients.ClientID, Clients.Name
This statement would return the hundred records from the year 2019, after which it would count the records in the dataset, thereby getting rid of the first step in the HAVING clause.
Keep wildcards strictly at the end of a statement
A wildcard creates the largest search possible when looking for plaintext information like names or designations. However, the wider a search, the less efficient it is, and a leading wildcard worsen the performance – particularly when it’s used with an ending wildcard.
That’s because the database has to find every single record that remotely matches the selected field. Take this query to fetch cities beginning with ‘Ch’, for instance:
SELECT Cities FROM Clients
WHERE Cities LIKE ‘%Ch%’
This statement will not just fetch the expected results of Chicago, Chester, and Chelsea, but will also return unintended results, like Richardson, Canal Winchester, and Cannon Beach.
A more productive statement would be:
SELECT Cities FROM Clients
WHERE Cities LIKE ‘Ch%’
This query will lead only to the expected results of Chicago, Chester, and Chelsea.
Use LIMIT to sample query results
The use of a LIMIT query will make sure the results of new SQL queries are relevant and desirable. As the name suggests, its function is to limit the quantity of records to the number mentioned, saving a lot of resources in the process.
Considering the 2019 sales query from above, let us suppose a limit of 15 records:
SELECT Clients.ClientID, Clients.Name, Count(Sales.SalesID)
FROM Clients
INNER JOIN Sales
ON Clients.ClientID = Sales.ClientID
WHERE Sales.LastSaleDate BETWEEN #1/1/2019# AND #12/31/2019#
GROUP BY Clients.ClientID, Clients.Name
LIMIT 15
The results will indicate if the data set is worth using or not.
Adjust Your Timing a Bit
If you’re looking to minimize the impact of your analytical queries on the production database, consult with an Oracle Database Administrator regarding the scheduling of your SQL queries so that they can be run during off-peak hours.
Specific hours when there are fewest concurrent users, generally in the middle of the night, should be chosen to run such resource consuming queries. If your SQL queries are more likely to include the following criteria, consider running it during off-peak timings:
- Selecting from huge tables (where there are over a million records)
- Queries with Cartesian or Cross Joins
- Looping queries
- SELECT DISTINCT queries
- Subqueries that are nested
- Search queries involving wildcards in long text or memo areas
- Numerous schema statements
Query with Confidence!
Keeping these and other SQL tips into consideration will certainly enable you to construct efficient, smart queries that will operate swiftly and fetch your team the game-changing insights it needs.