Support Request

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.

SQL statements or queries are designed to retrieve information from the database. A user can achieve the same results through optimization in SQL; using a tuned query is especially useful from an execution perspective. 

Tuning a database is a vital step in organizing and accessing the information in a database. Performance tuning in SQL requires streamlining and homogenizing the environment of a database and the files in it. This simplifies the way users access data in a big way. 

Why Companies Need to Consider Optimization in SQL

Several organizations own databases, but not all of them hire IT staff knowledgeable in the ways of optimization in SQL. Only professionals who have tuning skills and experience along with insider information about the working of databases should do this. 

In case your company has a database but it hasn’t undergone performance tuning, you might encounter inadequate responses to queries and face unnecessary complications when handling data. Don’t let your efficiency get affected because of something avoidable like this! 

Performance Tuning in SQL: What It Involves

Tasks related to performance tuning include optimization in SQL database, creating and managing indexes, and other related tasks to maintain or improve database performance. The goal of MySQL query optimization is to increase the speed and brevity of query responses and to simplify data retrieval. 

Let’s look at three major reasons why companies need to take performance tuning and seriously – 

1. To enhance the rate of data fetching options

If your database lacks optimization, then fetching data can get slower with increasing data loads. Optimizing queries enables users to create indexes and eradicate issues that may be slowing down data retrieval. After all, it can get quite frustrating for your employees to wait for the database to perform its operations, which can pass on to customers forced to wait for the same.

2. To refrain from coding loops

Making your database go through a coding loop is akin to hammering it repeatedly. That’s because the same query is executed several times when it is placed in a loop. However, once you remove the query from the loop, you will experience a definite surge in performance because the query is run only once rather than going through multiple iterations. 

3. To increase the performance of your SQL statements 

Query tuning in SQL includes changing previous query patterns and habits that were affecting the speed of data storage and retrieval. For example, the use of SELECT is reduced by opting for separate column declaration and eliminating correlated subqueries. Queries are also simplified by obviating temporary tables at times, aside from many other techniques of optimization in SQL

Your database will be able to manage much more data after the application of all these improvements as these will increase its efficiency, making it scalable as well. Once your database has scalability, it also overcomes lower performance and ensures user satisfaction in terms of experience. 
If you require professional tools to manage MySQL query optimization and tuning, then Tosska can help you. Tosska provides highly intuitive tools that can simplify query tuning beyond your imagination, and it does this with the help of innovative AI technologies. Contact us today to learn more about our range of query optimization products and services.

SQL performance tuning can be an extremely complicated task, especially where data in huge quantities is concerned. When implementing queries to insert data in large quantities, even the tiniest of changes can have a major impact on performance – for better or for worse. 

If you are new to databases, you may be wondering what SQL performance tuning is and how you can use it with sound knowledge of the fundamentals and a few tricks up your sleeve. In this blog, you will find some fundamental techniques for SQL tuning to improve performance of SQL query being entered in the database. 

Techniques to Improve the Performance of SQL Queries

Consider these five tips and techniques to enhance database performance – 

Indexing

Indexes are quite effective in SQL tuning but are often overlooked at the time of development. Basically, an index is a data structure that can boost data retrieval speeds in tables by supplying quick random lookups and prompt access to requested records. This implies that once you have made an index, selecting, SQL performance monitoring, and sorting operations are faster. 

They are also useful in defining a primary key that will prevent other columns from having the same values. Naturally, database indexing is a vast topic that deserves its own set of blogs, but for now, it is important to understand that the aim is to index the larger columns intended for searching and ordering.

  • Keep in mind, however, that indexes must be modified after INSERT, UPDATE, and DELETE operations, which means they could actually worsen the performance of the database if your tables are receiving a large number of these commands. 
  • Furthermore, Database Administrators usually discard their indexes before executing gigantic batch inserts involving millions of rows, to hasten the insertion process. Once the task is complete, they then create the indexes all over again. It is important to remember, in such cases, that when the indexes are dropped in this manner, it affects all the queries being executed in that table. Hence, to improve performance of SQL query, this approach is typically taken in certain situations that require a single sizable insertion.

Execution Plan Tool in SQL Server

This tool helps create indexes and it shows all the data retrieval techniques selected by the query optimizer. There are walkthroughs available that will help newcomers learn more about this tool.

  • If you are using the SQL Server Management Studio, you can fetch the execution plan by pressing on Ctrl+M to select the “Include Actual Execution Plan” option before executing your query. This leads to a third tab named “Execution Plan” that will show any missing indexes that it has detected.

Steer Clear of Coding Loops

Suppose you need to insert a thousand queries in your database in one go. In that case, you may be tempted to do it using a loop but you must, in fact, refrain from doing so. 

  • Instead, consider changing the snippets containing the loop in unique INSERT or UPDATE statements that have additional rows and values. 
  • Make sure that your WHERE clause avoids updating the stored value if it matches the existing value. Such a trivial optimization can dramatically improve performance of SQL query by updating only hundreds of rows instead of thousands.

Checking Whether a Record Exists 

This is a handy SQL optimization approach that concerns the use of EXISTS(). 

  • In case you want to know if a certain record is present in the database, make a preference for EXISTS() instead of COUNT(). That’s because EXISTS() can give you much better performance with more coherent code as it leaves the table the moment it gets the data it needs. On the other hand, COUNT() scans the whole table every single time, counting up each and every entry that matches your condition.

SQL performance tuning is about saving you a great deal of money by improving various aspects of database management. Be it changing inefficient queries or replacing unnecessarily large indexes, tuning your database can really help you regulate your expenses.  

SQL tuning will not only improve MySQL database performance, but it will also help you save multiple resources, whether you operate your database servers on-premises or migrate your data over to the cloud.

Performance Tuning – More Than Just Optimization of SQL Queries

A majority of database professionals mostly focus on ensuring uptime by keeping tabs on the allocation of resources such as storage and memory. These formed a sizable portion of database management until companies started moving their databases to enormous cloud resources like AWS and Azure. 

This gave importance to other aspects like performance tuning in SQL, which DBAs work on once they ensure that things are running somewhat smoothly. However, a lot of database professionals may have to tweak a SQL Server that they aren’t familiar with, and there’s not much help available for such instances. 

Identifying Problem Areas During Performance Tuning

It can get intensive trying to figure out all the issues and resolving them one after another. At first, your problems may not even involve the optimization of SQL queries, and you may begin your search at the user/session-level. 

What some professionals do at this point is, they listen to the users and ask questions such as – 

  • Are there any specific times when the application slows down, for example, when they create an extra ticket or open an active one?
  • Which data takes excessive time to render?
  • How long does saving a record take? (a specific answer may be preferable)

Improve MySQL Database Performance in SQL Server with These Tools

Fortunately, SQL Server has multiple features that can help you improve MySQL database performance with greater convenience. Some of them are – 

Plan Guides

Plan guides enable you to adjust the way SQL Server executes a query, affecting performance. These are especially useful in case of queries written by another vendor, where some experts may not be willing to change them right away, as you can add a query hint to influence its operation. 

However, plan guides may get obsolete over time because the scenario may change after a while but they cannot. To overcome this, make a note of reviewing them from time to time. 

Query Store

This feature helps in the optimization of SQL queries too. It allows the user to determine which queries are taking up the most resources, and then tune them. Apparently, the Query Store feature is not enabled in some SQL databases because the user rarely needs it at first, but enabling it is easy.

Some DBAs are not aware of the Query Store, while others know of it but haven’t explored it enough, so the feature may as well be disabled. They can begin its use once they know how it works so that they can analyze various performance fluctuations caused because of modifications in the query plan.

Database Engine Tuning Advisor

This function examines workflows and suggests indexes or other strategies to improve MySQL database performance. On the other hand, don’t run this tool until your database has sufficient data stored, as the recommendations are more productive at that point. 

It won’t be as useful for a newer application that has, say, only a thousand rows in its tables, than after the app has expanded a bit.

Until now we have learned a lot about Oracle database and SQL performance tuning. Now, let’s discuss MySQL database performance tuning. Just like any other relational database, MySQL could also be a nightmare to many. It can crawl to a halt at a moment’s notice and in the next moment, you will find it leaving your apps in the lurch and your business on the line. The fact is, regular errors underlie most MySQL performance issues. 

To ensure your MySQL server bustles along at great speed, providing consistent and stable performance, it’s imperative to eradicate such mistakes that are often confused by some subtlety in your configuration trap or workload. Just like any other performance tuning tips for Oracle database and SQL, MySQL too has some of its own performance tuning techniques that we have described in this blog. Let’s get started without wasting more words-

Oracle Database and SQL- Know How to Tune MySQL Database Performance

Fortunately, many MySQL performance problems have similar solutions which make both tuning MySQL and troubleshooting a manageable task. 

Here are a few tips for getting great performance out of MySQL.

Tip 1# Profile Your Workload

The best way you can understand how your server invests its time is to profile its workload. By doing so you could expose the most expensive queries for further tuning. When you issue a query against the server, you don’t have to care much about anything else except how quickly it completes. Therefore, time is the most crucial metric to consider here. 

The most significant way of profiling your workload is using a tool such as MySQL Enterprise Monitor’s query analyzer. Such a tool is meant to capture queries that are executed by the server. Furthermore, it returns a table of tasks sorted by decreasing order of response time, quickly bubbling up the most time-consuming and expensive jobs to the top so that you can find where you need to put your efforts. Workload-profiling tools group identical queries together that allow you to find slow queries as well as the queries that are fast but executed multiple times. 

Tip 2# Recognize the Four Basic Resources

For functioning, a database server requires four basic resources- Network, CPU, Disk, and memory. If any of these resources are weak, overloaded, or erratic, then the database server is most likely to perform badly. Recognizing these fundamental resources is crucial in two specific areas: selecting troubleshooting and hardware issues. 

While you choose hardware for MySQL, you must ensure the components perform well all around. Just as essential, ensure to balance them reasonably well against each other. Often, businesses choose servers having fast CPUs and disks but that are starved for memory. In some cases, adding memory is a cheap way of enhancing performance by order of magnitude, specifically on workloads that are disk-bound. This might seem counterintuitive, but in various cases, disks are overused as there isn’t sufficient memory to hold the server’s working set of data. 

Another good example of this perspective relates to CPUs. In most conditions, MySQL performs well with fast CPUs as every query runs in a single thread and can’t be parallelized across CPUs. 

During troubleshooting, examine the performance and utilization of all the four resources with much care and determine whether they are performing badly or are simply being asked to execute excessive work. This knowledge can help solve issues instantly. 

Tip 3# Don’t use MySQL as a Queue

Queues and queue-like access patterns can sneak into your application without even letting you know. For instance, if you set the status of an item so that a specific work process can claim it before working on it, then you are ignorantly formulating a queue. Marking emails as unsent, sending them and then remarking them as sent in a common example. 

Queues create problems for two big reasons: they serialize your workload which prevents tasks from being done in parallel, and they mostly result in a table that includes work in process as well as earlier data from jobs that were executed long ago. Both add concealment to the application and load to MySQL. 

Tip 4# Firstly Filter Results by the Cheapest 

The best way of optimizing MySQL is to do imprecise, cheap work first, then the precise and hard work on the smaller that will result in a set of data. 

For instance, consider you are searching for something within an assigned radius of a geographical point. The initial tool in many programmers’ toolbox is the great-circle (Haversine) formula. This will compute the distance along the sphere’s surface. The trouble with this technique is that the formula needs a variety of operations related to trigonometry, which are very intensive toward the CPU. Great-circle calculations tend to perform slowly and make the machine’s CPU utilization skyrocket. 

Before you implement the great-circle formula, cut down your records to a small subset of the total, and scrape the resulting set to a precise circle. A square that comprises the circle either precisely or precisely is a simple way of doing this. That way, the world outside the square never gets hit with all those expensive trig functions. 

Tip 5# Understand the Two Scalability Death Traps

Scalability isn’t as fuzzy as you may think. In fact, there are explicit mathematical definitions of scalability that are defined as equations. Such equations emphasize why systems don’t scale and why they should. Consider the Universal Scalability Law, a definition that is convenient in expressing and quantifying a system’s scalability qualities. It explains scaling issues in terms of two elementary costs: crosstalk and serialization. 

Parallel processes that must hold for something serialized to take place are inherently limited in their scalability. Similarly, if such processes are required to chat with each other all the time for coordinating their work, they restrict each other. If you avoid crosstalk and serialization, your application could scale much better.

You are welcome to submit your support requests / bug reports to us by sending emails to support@tosska.com or by filling in the Support Request Form. However, before submitting such request, we recommend you to do the followings :

  • Make sure to first read the FAQ page and the documentations included with the product.
  • Make sure you can reproduce the problem with the latest version of the Product.

When submitting a support request / bug report :

  • Please select the correct product version and fill in the correct license no.
  • Use ‘Short Description’ to highlight the issue you are reporting.
  • If you are reporting a bug, please include the description of the problem, the steps to reproduce the problem and what was the expected result in the ‘Details’ section. If possible, please also include any data creation script and SQLs used. Or else, describe the services that you need.
  • If you get an unexpected exception error when using the product, the exception windows will be shown automatically. You can click the ‘Create Support Bundle’ option to generate the Support Bundle. Or alternatively, you can go to the menu on the top right hand corner and select ‘Support Bundle’ anytime to generate the Support Bundle. Then send the Support Bundle to us as an attachment via email to support@tosska.com.





 

Your Details

Let us know how to get back to you.

Product Details

Let us know what product version you are using.


How can we help?

Feel free to describe your issue and/or questions below. If needed, you can send the Support Bundle via email to support@tosska.com for our investigation.

* mandatory field

I acknowledge that I have read and agree to the Tosska Privacy Policy and Copyrights statement