SQL Performance Tuning: 5 Things to Do for Faster Results

query tuning in sql

Is your SQL Server falling behind in terms of performance? Are poorly-written queries slowing down your applications? Before you set out in search of professional help, make sure you’ve tried everything you could to resolve the issues you’re facing on your database. 

Many problems related to SQL Server can be handled easily with preventative maintenance, patches, and a few activities performed on a regular basis. You can always depend on our SQL performance tuning tools if nothing works for a particular situation. But before that, read the five important things you can do to fix database performance.

5 Things to Do for Effective SQL Performance Tuning

Given below are five simple things you can do to improve database performance:

Check if your SQL Server is up-to-date

An older query engine out of active development is bound to get you in performance-related trouble every now and then. Moreover, the newer versions have much better diagnostic support and will make things faster in multiple ways.

For starters, they come with new versions of the query optimizer. Although Microsoft provides a few tweaks here and there in its service packs, major version releases contain all the best improvements. Other advantages include:

  • Bug fixes
  • New CPU instruction sets
  • Latest software development techniques

Even a 32-bit to 64-bit upgrade can go a long way in improving database performance. This will help regardless of whether you are performing SQL tuning for Oracle or SQL Server.

Increase the Memory

Maxing out its memory will make a difference in its performance. This is because the database utilizes it to cache data instead of making additional trips to disk. Additionally, you gain more memory for cache query plans and can use it for larger sorts and joins. Another advantage is a potential decrease in disk and CPU utilisation, which further helps with SQL performance tuning. Just remember to raise the memory configuration in your SQL Server so it actually makes use of the new RAM.

Open Task Manager

If things remain slow after you’ve increased memory and upgraded your SQL Server,  it’s time to open Task Manager. Sort by CPU, followed by memory, and close any running apps, processes, or software that’s eating up space and you’re unaware of. Configure exceptions for antivirus software, if you have anything installed.

Windows may also be caching data for file system access, stealing RAM from server-side apps such as SQL Server. This can be checked by looking at the Cached number in the “Physical Memory (MB)” tab.

Check the Event Log

This includes both – the Windows Event Log and the SQL Server Log – as they both have potentially useful information. In case Windows or SQL Server are facing any sort of issue, these logs will certainly have more details about it.

You’ll know whether SQL Server is lagging due to hardware-related problems, facing long disk wait times, or dumping core. There may be other services with issues on the server that you can find out about here.

You can also read our post on SQL tuning for Oracle for some useful tips.

See if SQL Server alerts are set up

It is important to ensure these alerts are configured. They will, in turn, help you make sure you’re updated on everything that’s happening in SQL Server. As the person in charge of the database, you need to be aware in case the storage is falling short or other serious errors in SQL Server.

SQL Query Performance Tuning: A Look at Various Plan Formats

SQL query performance tuning

Database professionals are often familiar with the fundamental maintenance tasks for SQL Server. However, they may have to perform the optimization of SQL queries and keep an eye on query plans from time to time.

This may lead to confusion as to which type of query plan they should use. Here, we will cover the different types and formats of query plans and how to obtain them for SQL query performance tuning.

Query Plan Types and Formats for Efficient SQL Query Performance Tuning

To start with, there are two major kinds of query plans: the estimated and the actual execution plan. Users can obtain these in three distinct formats – text, graphical, and XML.

Keep in mind that users who wish to create these execution plans will require SHOWPLAN permission first. This is also true in the case of query plans and SQL tuning for Oracle.

Text-Based Query Plans

This type of plan can be procured using one of the three methods given below:

  • Set SHOWPLAN_Text – The query will not be run by SQL Server, but this method should fetch information about the manner in which queries run. In short, this statement will display information regarding the Estimated Execution plan.
  • Set SHOWPLAN_All – Again, SQL Server won’t run the query but it will fetch thorough details regarding query execution, i.e the way it executes them and which resources it uses for this purpose. You can also get more details about the Estimated Execution plan.
  • Set Statistics Profile – SQL Server will run the statement and display comprehensive data regarding query execution. This information includes the precise number of rows that were actually processed and all the resources that have been utilised for executing these queries. This command will also fetch details regarding the Actual Execution plan.

Graphical Query Plans

This format allows users a look at numerous sources of information and plenty of tooltips in SQL Server Management Studio.

Note that if you want to view the Estimated Execution Plan, you can do so once you press Ctrl+L in the query window. To view the Actual Execution Plan in the same results set, press Ctrl+M.

XML Based Query Plans

This type of query plan gives the most comprehensive details of the plan in the extremely portable XML format. You can obtain query plans in this format using two methods:

  • Set SHOWPLAN_XML – The query doesn’t run but returns detailed information about how the statements execute and the resources used for the query execution. SQL Server also displays a detailed XML document that has the Estimated Execution plan.
  • Set Statistics XML – SQL Server runs the statement and shows information regarding query execution in exhaustive detail. This includes information on the actual number of rows processed as well as the resources applied in the query execution. It fetches a properly created XML document consisting of the Actual Execution plan, helping in SQL query performance tuning.

How these Query Plan Formats Differ in Use

The graphical format is usually the simplest to read which is why beginners usually start with them. The best way to read graphical query plans is from right to left moving upwards from the bottom while following the arrows. Additionally, you can make things easier with the Zoom In and Zoom Out functions.

You can view further details from Graphical plans with the help of ToolTips. All you have to do is point your cursor at the icon you want to know more about. While this is an extremely useful feature, it can get complicated to view every detail on a complex query with the help of this tool.

This format also forms the link between the Text and the XML formats. This is because you can save Graphical Plans in XML format. If you’re trying to perform SQL tuning for Oracle, you may consider a SQL tuning tool depending on your requirements.

Text plans, on the other hand, are more difficult to read and lack simpler rules to understand them. However, they prove useful for those who have experience with execution plans and know what and how to look for.

Optimization of SQL Queries: Database Engine Tuning Advisor

optimization of SQL queries

If there is one complaint business users may have about their production database, it is slow performance. Database professionals, therefore, often try to focus strictly on finding out and resolving the source of the issue in the database.

The Database Engine Tuning Advisor (DTA) is one of the best tools in this regard. It helps DBAs analyze workloads and uncover areas that can be improved. In this blog, we will discuss the working of this tool along with a few additional details.

What is the Database Engine Tuning Advisor?

It is a tool that assists with the optimization of SQL queries and was introduced in SQL Server 2005. Before it, SQL Server had a feature known as the Index Tuning Wizard.

The DTA is designed to assess a workload and provide recommendations to boost query performance. Some of its suggestions include:

  • Making partitions
  • Incorporating indexes
  • Adding statistics (this helps resolve the issue of automatic statistics not getting created despite the auto_create_statistics option being ON)

Apart from locating every type of potential for enhancement, the Oracle Database Engine Tuning Advisor will build a T-SQL script for users to execute in order to carry out the recommendations made by it.

How to Launch the DTA for the Optimization of SQL Queries

You can commence its use through multiple methods:

  • Open the Start menu, scroll to the SQL Server application group and look for the Performance Tools folder.
  • If you’re using the SQL Server Management Studio application, you can select the tool from the Tools menu.
  • The SQL Server Profiler also has this tool in its Tools menu.
  • You can find it with the select analyze query in DTA on the SQL Server Management Studio Query menu. This also enables you to pass a T-SQL section to the tool for analysis.
  • Open the Command Prompt, type “DTA -?” for a glimpse at the available alternatives.

Normally, you can create a workload by collecting multiple statements in a file or with the help of the SQL Server Profiler. An important point to bear in mind is that the workload being sent to the Advisor for evaluation needs to be representative of the average workload.

What the Database Engine Tuning Advisor Does

The Database Engine Tuning Advisor makes recommendations on the basis of the workload you send for analysis. Therefore, a limited workload will result in inadequate recommendations. The best thing to do is to collect the workload through the Profiler, save the results in a text file, and send the trace to the Advisor.

The SQL Server Profiler plays an important role in the optimization of SQL queries. It can be launched from its location in the SQL Server application folder (in the Start menu) or among the list of tools in the SQL Server Management Studio.

In case you want to know the properties of the new trace, follow these steps –

  • Click on the dropdown list in the dropdown menu of the tool window (for the trace template) and select Tuning. The trace template gathers the events considered necessary by DTA.
  • Select the ‘Save to file’ and type in the file name you want before passing it to the Database Engine Tuning Advisor once the trace is complete.

Selecting a SQL Server Database Version for Your Company

SQL Server database and SQL

Choosing the right version of SQL Server is important for the performance you desire. If you’re installing an older one because your organization’s management prefers an older build or the vendor is unable to support newer versions, it is important to let them know which version your company needs, and why.

For this reason, we will discuss some popular versions of SQL Server from older to newer and mention their advantages in this blog.

Which SQL Server Version Works Best with SQL Performance Tuning?

Knowing the versions that support this task is extremely important because it will give you the ability to improve the SQL Server database and SQL performance.

To that effect, we will discuss the SQL Server 2016, 2017, and 2019 versions here.

SQL Server 2016

This version was chosen by a lot of independent software vendors or ISVs for one reason – 2016’s Service Pack 1 edition came with Enterprise features in Standard mode. These helped create a single application version that worked simultaneously for both Standard as well as Enterprise clients.

Advantages of Choosing this Version:

  • It is easy to find support material online as this version is quite popular and numerous database professionals are well-versed with this version’s tools.
  • Standard Edition users may find this version appealing since it supports 128GB RAM and additional space for internal functions such as query plans.
  • Support for this version ends after 2026 – longer than the older versions (2012/2014).
  • Newer applications that have additional compliance requirements will benefit from features in this version such as Always Encrypted, temporal tables, and Dynamic Data Masking. These will make it somewhat easier to protect and monitor sensitive information.
  • You can have both row store and column store indexes in this version, unlike the earlier ones that only had row store indexes.
  • If you need query plan monitoring to help with SQL performance tuning, you can use the Query Store’s features provided in SQL Server 2016 for this purpose.

SQL Server 2017

Being a newer release, it is one of the most regularly updated versions with patches coming in almost every other month. These patches are important because they resolve significant problems. It also comes with a minimum commit replica configuration to ensure commits are accepted by several replicas.

Advantages of Choosing this Version:

  • The upgrades are easier to get from this version onward due to a Distributed Availability Group that contains multiple SQL Server versions in it. Before this, we had AG version upgrades that were not as convenient, leading most users to construct a new cluster and migrate to it rather than opt for an upgrade.
  • This version contains batch mode execution plans, which gives those who require high-performance column store statements an advantage.
  • If you must run your SQL Server on Linux, you may consider SQL Server 2017 as several bugs have been resolved in the Cumulative Updates.
  • It’s a newer version so support will last longer than that of its predecessor.

SQL Server 2019

Released on November 4, 2019, this version is the latest in the SQL Server series. Naturally, it comes with the longest support lifespan, i.e. it will be supported until 2030. This version also receives regular patch updates to fix many significant issues in the form of Cumulative Updates.

Changes and Features in this Version:

  • Patch contents aren’t documented anymore. Moreover, you are likely to receive updates with undocumented new features – something to consider in case you require it for mission-critical production environments.
  • There is a bit of a learning curve thanks to some cutting-edge features in this version, so be prepared to perform some experimentation as you learn.
  • Some of the best performance features are included in the 2019 compatibility mode. However, you will have to keep a close eye on all SQL Server databases and SQL queries – even the ones running fast at present – as these will alter your current execution plans. In other words, you will have to test both slow and fast queries to make sure the slow ones speed up and the fast ones don’t fall behind in performance.
  • Table variables have gotten better in this version along with user-defined functions.
  • Additional features to watch out for including Big Data Clusters, Java support, and high container availability, so you may want to explore this version if you’re looking for perks like these in the SQL Server you want.

In Conclusion

At this point, SQL Server 2017 might seem like the best version to go with, thanks to a balance of features, stability, and support lifespan. Furthermore, you’ll receive plenty of help with SQL performance tuning – a lifesaver for overworked professionals who may not have the time or resources to upgrade every server every year.

5 Essential Paid & Free SQL Tools for Windows SQL Database

MySQL database and SQL

Effective database management requires one or more tools regardless of the platform your databases run on. The benefit of these tools is, whether they are operating on Windows, macOS, Linux, or the cloud, the tools mentioned below don’t require the same platform as the databases. 

Thanks to plenty of choices when it comes to SQL management tools, it may seem difficult to select the ones that will work best as per your specific needs. In this blog, we’ve picked out the best paid and free SQL tools for Windows along with their essential features.

Free SQL Tools for Windows with a Graphical User Interface (GUI)

Consider the following free SQL tools for Windows to help you with efficient database management:

  1. SQL Server Data Tools (SSDT)

The SSDT is designed for a variety of databases such as Azure SQL, SQL Server relational databases, RS reports(Reporting Services), IS packages(Integration Services), and AS data models (Analysis Services). It is a development tool that allows users to create and implement any SQL Server content form with a convenience identical to that of Visual Studio.

  1. SQL Server Management Studio (SSMS)

The SQL Server Management Studio tool comes with an interactive Graphical User Interface that helps users control a SQL Server database or an instance. Users can gain access to any part of the SQL Server, Azure Synapse Analytics, or the Azure SQL Database and make changes, regulate, supervise, and develop them. 

You may want to look for this tool when you download free SQL Server as it also offers an all-encompassing utility which brings together a wide range of graphical tools along with several rich script editors. These are useful to DBAs and developers of varying skill levels.

  1. Visual Studio Code

The Visual Studio Code enables users to write T-SQL scripts using a compact editor. We are talking about the mssql extension – the official extension for Visual Studio Code that supports SQL Server linking and offers a productive T-SQL editing experience.

  1. Azure Data Studio

This is also a compact and handy editor capable of running SQL various functions including –

  • Executing SQL statements whenever required
  • Organizing preferred database connections
  • Viewing and storing results in text, JSON, or Excel format
  • Exploring database objects in a familiar environment
  • Editing information

Tosska’s Range of SQL Tuning & Query Optimizer Tools

Tosska Technologies offers several solutions to improve database performance through query optimization in SQL. With the goal of introducing new technologies that will help users overcome SQL-related obstacles, they provide a range of software designed using AI technology capable of solving a broad range of database performance issues.

Here’s what this range includes:

  • Tosska SQL Tuning Expert (TSES™) for SQL Server® – Unlike some of the free SQL tools for Windows, this is a powerful tool that doesn’t require professional expertise to tune your SQL queries. The AI engine does all the work, generating the most useful hints and alternate SQL statements that are semantically equivalent to the query that’s been entered to know whether there are better execution plans. You may pick the best option among the ones provided by the engine.
  • Tosska SQL Tuning Expert (TSEM™) for MySQL® – The TSEM™ comes with the A.I. capabilities of the TSE product range. However, this one-button-solution tool is tailored specifically for the MySQL database, tuning MySQL SQL queries without the users’ intervention. Users don’t have to perform manual rewriting or use the hit-and-trial method for each troublesome query, since it’s all handled by our embedded AI engine.
  • Tosska SQL Tuning Expert for Oracle® (TSE™ and TSE Pro™) version 4 – This tool comes with features exclusive to this family of tools, such as SQL rewrite, index exploration, and injecting Oracle hints to help tune SQL queries and boost their performance. It may or may not access your source code depending on the requirements, and the tool comes with a smart Indexes Advisor that offers cost-efficient indexes as per the workload. Make sure you get it when you download free tool and take advantage of this cutting-edge technology!
  • Tosska In-Memory Maestro (TIM™) for Oracle® – The TIM™ transforms the in-memory SQL optimization process into an automatic one and gives suggestions according to the SQL workload in question through our proprietary A.I. engine. It also offers a user-friendly simulation feature that virtually assesses table objects present in the memory for a SQL workload but doesn’t occupy those table objects.

5 Reasons Why a Hardware Upgrade can Worsen Performance

SQL Server query optimizer tool

After gaining approval to switch to new hardware (at last) and a newer SQL Server version, you start to make preparations. But what will you do if the performance worsens after you finally do the migration?

Hardware upgrades are often preferred over MySQL SQL performance tuning but the transition may not necessarily take place smoothly. In this post, we will consider five of the most common reasons why a hardware upgrade may actually worsen performance, and how to identify these reasons.

Hardware Upgrade Over MySQL SQL Performance Tuning: 5 Things to Check

Using a SQL Server query optimizer tool might not be the best bet here since the hardware has been changed. In such cases, consider the five most common ways given below to troubleshoot this problem:

Check a Few Sys.configurations Settings

The default setting for “max degree of parallelism” is zero in SQL Server. This means all the cores get used by queries that go parallel, so your workload on the new instance may be facing tasks struggling for CPU usage due to this setting.

You can find out if this is the case by comparing all the configurations in sys.configurations between instances and searching for anything that stands out. Some users might accidentally set the lowest memory value for a query.

Investigate for Bottlenecks in Hardware Settings

Certain unexpected situations can lead to a few problems that might not get detected until after hardware upgrading or migration. For instance, the server might not be receiving sufficient power, which can affect CPU operation as well. They become less capable and therefore, show greater utilization when checked in Windows Task Manager.

Identification can be done in two ways:

  1. Keep an eye on the Windows System log. You may find messages related to improperly plugged power sources.
  2. Try running a free tool to gauge the clock speed of the processors and whether all of them are operational. Different from a SQL Server query optimizer tool, you can find one online nevertheless.
Check if Your Processors are on Power Saving Mode

It might not be the sole cause behind greater CPU usage but you can expect it to be one of the elements contributing to the issue, aside from a need for MySQL SQL performance tuning. Fortunately, this is something you can resolve rather quickly, so it’s certainly worth a glance.

This setting can be checked in the BIOS – disable power savings in case it is enabled. However, you may have to confirm the change with the help of a server management tool such as HP System Insight Manager and a planned outage that will give you time to make the desired change in the BIOS and reboot the server.

Check the New SQL Server Version for Stack Dumps

A stack dump, also known as a “15-second error” is responsible for leaving the storage unresponsive for fifteen seconds, followed by increased load.

To check for a stack dump, you will have to open the SQL Server error log and look for errors in the span of some days. You can filter using the terms “15 seconds” or “stack dump” to locate the culprit.

Inspect Factors Outside SQL Server for CPU Usage

Although you may have already looked into this, experts recommend double-checking to ensure the database professional doesn’t miss anything. You may be surprised to see a user testing with the server or using a SQL Server query optimizer tool. They may neglect to inform you – or forget to revert to the original settings. Sometimes, users may forget about previously running tasks while a new instance goes live.

To find out what went wrong, open the Windows Task Manager to take a look at all the processes that are utilizing memory and CPU. There shouldn’t be any agent tasks or scheduled jobs that were absent on the previous server.

Using Free SQL Tools for Windows to Monitor Your Database

MySQL database and SQL

There are several reasons why SQL monitoring is a must, especially for organizational databases. However, you need to understand those reasons to know the importance of regular database maintenance.

SQL tools have a prominent role to play in this, and while some of them may cost money, there are several free SQL tools for Windows to enable successful database monitoring. In this blog, we’ll go through some functions and perks of using such tools.

Benefits and Features of Using Free Tools for Windows

Consider the following advantages of such tools in the management of your database:

  1. Ease of Operation

Monitoring a database involves several complicated tasks that can fill your schedule and keep you from performing other necessary activities. As a database professional, you already have plenty to do, so there may not always be time or resources to get it all done on a regular basis. 

In such cases, you can choose specially designed software to automate a majority of these tasks. Such tools also handle smaller issues on their own (based on the settings you choose). This leaves you more time for the rest while giving you updates at the frequency and priority of your choice. Of course, you will have to figure out an estimate of your organization’s requirements and affordability while considering which tool to use to monitor SQL.

  1. Easier to fix issues

Regardless of how smooth and streamlined a database seems, it is bound to run into a few problems sooner than you think. This is why you not only need to know what kind of problem it is, but you also have to find the cause behind such issues – which can prove to be a challenge.

Monitoring using paid or free SQL tools for Windows means you get better access to information regarding the server’s present condition. This simplifies the process of uncovering faulty processes that are leading to deadlocks and other issues without much effort. You can find what’s going wrong on your own as well. However, these tools make the process much more convenient, giving you more time to troubleshoot a greater number and variety of problems quickly.

  1. Use as per Your Unique Requirements

Although SQL monitoring tools offer a one-size-fits-all solution to users, a majority of DBAs prefer the option to customize their settings and receive alerts according to their preferences. It is an incredible advantage of using such tools because it allows users to adjust their functions as per their specific requirements, making them useful for years to come because your needs can change as time passes.

  1. Remote surveillance is simplified

Optimization in SQL becomes more convenient once users have the ability to keep an eye on database performance and make changes when necessary, regardless of their location. Monitoring tools grant this ability to their users, thanks to their browser-based interface.

Aside from providing remote tasking to professional Database Administrators, numerous tools also enable users to adjust their dashboards as they like for even greater customization. Ultimately, it is up to you to select the right monitoring tools, whether they are free SQL tools for Windows or paid ones for the platform you require. All you need to do is acquire the knowledge and skills needed to make the most of such tools and your database.

5 Chief Oracle SQL Performance Tuning Methodologies

optimization of SQL queries

Nearly every organization in the present era stores its information in separate databases depending on their specifications. Soft copies are given greater preference due to advancements in storage technology, making databases – and their performance – important in the day-to-day operations of an organization.

Therefore, DBAs conduct regular checks and Oracle SQL performance tuning to make sure the database is running the way it should. Performance tuning is done with the help of different methods and tools to maintain maximum efficiency.

5 Major Tools and Methods to Conduct SQL Performance Tuning

Consider these techniques and tools to streamline SQL tuning for your database:

  1. Implement Regular Server Health Check-ups

Optimal server health is essential for good database performance and performance tuning tasks also depend on it, which is why DBAs must perform server health screenings from time to time. You can detect whether server health is ideal or if there are slowdowns using Dynamic Management Views or DMVs.

  1. Assess Statement-related Statistics Simultaneously

Since Oracle SQL query tuning impacts real-time tasks, it is recommended to track the same in real-time to determine the source of slowdowns more quickly.

Live Query Statistics can help you in this regard: it shows statistics of all the statements that are running at that instant to enable the analysis of every step. Such a tool proves useful in troubleshooting SQL performance tuning related problems.

  1. Examine Execution Plans

DBAs use the Execution Plan tool to find out all the data retrieval techniques selected by the SQL Server query optimizer. All they have to do is choose the “Include Actual Execution Plan” before they execute the SQL statement they wish to optimize.

Once the Execution Plan tab shows up, you can determine whether there are any missing indexes by right-clicking and selecting the “Missing Index Details” option. Doing it will create the missing index and improve database performance.

  1. Determine Performance Impact of Transact-SQL Queries

Certain tools such as Database Engine Tuning Advisor can provide multiple benefits during Oracle database and SQL tuning. These include the analysis of the impact on performance and suggesting changes to be made on the basis of such observations.

  1. Observe Resource Consumption

DBAs can enhance database performance dramatically by keeping an eye on resource consumption and ensuring maximum productivity. There are certain parameters you can monitor such as buffer manager page requests with the help of System Monitor.

As its name suggests, it informs about the resources being utilized (Monitor Resource Usage) through predefined objects, counters that gather the counts and rates instead of event-related information. This tool also provides alert notifications when you want to set thresholds of the counts mentioned above.

To Conclude

Database Administrators can conveniently work on improving SQL database performance to a large extent using Oracle SQL performance tuning. This will help them lower the response time by taking the steps necessary to enhance throughput upon identification of all the areas that have been impacted.

The tips explained above mention some of the best SQL performance tuning tools to take care of some of the major tasks related to tuning. These are especially useful for large databases as they play an important role in boosting overall productivity.

Optimization in SQL: Answering 4 Commonly-Asked Questions

optimization of sql queries

A SQL query or statement is tasked with fetching the required information from the database. While the same output can be gained from different statements, they are likely to work at different performance levels.

The difference in performance output makes a lot of difference because a millisecond of lapse in query execution can result in huge losses for the organization. This makes it extremely necessary to ensure the best statement is being used, which is where optimization in SQL is considered.

#1: What is Query Optimization in Databases?

Query optimization in databases is the general process of picking out the most efficient way of obtaining data from the database i.e. carrying out the best query for a given requirement. Since SQL is nonprocedural, it can be processed, merged, and reorganized as seen fit by the optimizer and the database.

The database enhances each query on the basis of various statistics gathered about the information fetched from it. On the other hand, the optimizer selects the optimal plan for a query after assessing different access techniques including index and full-table scans. Various join methods and orders are also used along with certain probable transformations.

#2: What is Query Cost in Optimization?

Query cost is a metric that helps examine execution plans and determine the optimal one. Depending on the SQL statement and the environment, the optimizer sets an estimated numerical cost for every step throughout potential plans and considers an aggregate to derive the overall cost estimate for it.

The total query cost of a query is the sum of the costs incurred at every step in it. Since query cost is a comparative estimate of the resources needed to carry out every step of an execution plan, it doesn’t have any unit. The optimizer picks out the plan with the least cost projection once it has completed all its calculations of all the available plans.

#3: Is Query Cost the Best Way to Judge Performance?

In a word: No. Why? Although query cost proves useful in comprehending the manner in which a specific query is optimized, we must bear in mind its main goal: helping the optimizer select decent execution plans.

It does not offer a direct measure of parameters such as CPU, IO, memory, duration that are significant to users waiting for a statement to finish running. In other words, a low query cost won’t necessarily mean the plan is optimal or the query in question is the quickest. Similarly, a high query cost can prove more efficient in comparison, which is why it is not recommended to depend too much on query cost when considering performance.

Being a CPU-intensive operation query optimization in SQL takes a lot of resources to determine the best plan among the ones present. Time also needs to be factored in here as the user may not always have the time it may take for this entire process to take place. 

Therefore, the resources required to optimize a statement, those required to run the statement, and the time it takes for all of this to be done with shouldn’t exceed each other. 

#4: How Can We Optimize a SQL Query?

Query optimization often needs extra resources, such as the addition of indexes. However, we can boost query performance by simply rewriting a statement to decrease resource consumption without further expenses.

This lets us save significant resources, money, and time (if a query optimization tool is used). Through query optimization in SQL, we can focus on specific areas that are causing latency instead of examining the entire procedure. In such cases, looking for sections that are taking up more resources will help us narrow down the search and fix issues more quickly.

Improve SQL Queries & Database for Better Efficiency: Part 2

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.