Insufficient database performance is a fly in every DBA’s ointment. To make matters worse, finding the root cause of performance-related problems can get really difficult.
One of the best methods to get rid of performance issues is through performance tuning. However, you may not always be sure where to begin with the optimization process.
At times, you may find your database performance lacking even after you have eliminated certain factors that adversely impact hardware and network performance, such as memory and disk space. In such cases, you need to examine your queries.
Watch Out for These 6 Query Related Mistakes!
Unoptimized queries can lead to multiple performance related difficulties for your systems. Here, we’ll analyze six common query mistakes that typically form the cause of database performance deterioration.
Prepare yourself for a good amount of tuning, if you detect any of these problems in your queries –
Query 1: Like Queries that Contain Leading Wildcards
SQL may not be able to utilize indexes the way it should due to leading wildcards. As a result, a full table scan will take place regardless of any indexed fields present in the table. Scanning each and every row in a table every single time means it’ll take more time to fetch query results. Therefore, it is advisable to remove the leading wildcard to boost efficiency.
Query 2: WHERE or GROUP BY Clauses that Have Non-Indexed Columns
When a column is indexed, it returns query results more quickly. This is because there is no need to conduct a complete table scan due to the presence of an index.
Arranging records also becomes much simpler with indexing at the time of return and ensures that the records can be uniquely identified.
Query 3: The Presence of the ‘OR’ Operator in Like Statements
The ‘OR’ operator is used while comparing fields or columns in a table. However, doing it too often in a WHERE clause is another way to invite unwanted full table scans.
If you want to improve the speed of your Oracle SQL queries, try using a union clause instead. They succeed in making SQL queries run faster, particularly in the presence of separate indexes at each end of the query. Basically, the union clause combines the results of two quick, indexed queries.
Query 4: Unavoidable Wildcard Searches
If you find that a wildcard search is unavoidable but would rather avoid the performance hit, see if you can conduct a SQL full-text search. This type of search is considerably faster than performing searches using wildcard characters. Additionally, you obtain more relevant results when the searches take place in huge databases.
Query 5: Inefficient Database Schema
The efforts you make to improve SQL query performance will only take you so far, unless you also work on optimizing your database schema. Consider the following tips to do so:
- Normalizing Tables – Duplicate data slows down performance. So, make sure that a fact is represented just once in the database. For instance, use “cust_name” just one time if you’re referencing its data in multiple tables, use another column such as “cust_ID” for subsequent references.
- Using Suitable Data Types – Given below are a few important points to keep in mind regarding data types:
- Try to create tables with data types of shorter sizes, such as a “user_id” field with “TINYINT” data type – in case there are fewer than a hundred users.
- If one or more fields will need a date value, consider using a “date_time” data type for those fields. This way, you won’t have to convert the records to date format afterwards.
- SQL functions more favorably with integer values in comparison with text data types, including varchar.
- Don’t Use Null Values – The presence of multiple null values in a column negatively affects your query results. Instead, it is better to set a default value for fields in which a mandatory value is not needed.
- Try to Use Fewer Columns – Tables that contain over a hundred columns are considered too wide because they take up a considerable amount of processing power and resources. See whether you can split a wide table into smaller tables that are also logical – unless a wide table is unavoidable.
- Optimize Joins – SQL queries that contain an excessive number of joins as well as joins that include several tables have a negative effect on performance. Aim for queries with at most twelve joins.
Query 6: Un-Cached SQL Statements
Caching SQL queries proves advantageous for websites and applications that perform a lot of select queries. SQL query caching increases performance speed when read operations are performed. This is because it caches the select statement along with the resulting data set. Also, it retrieves the information from memory rather than the disk in case the statement is carried out more than once.
Performance tuning is vital for keeping your database available and preserving user satisfaction. Unfortunately, it isn’t always immediately clear where you need to perform tuning. If you have ruled out the typical hardware and network sources for performance issues, start examining your queries.
As a DBA, you are likely to face one or multiple of the problem queries mentioned above. Now that you are aware of the possible sources of the problem, keep an eye out for these and work on your performance improvement efforts. You can do this by correcting these common query problem areas so you can make the most of Oracle SQL database query optimization.