Tips and Tricks Optimization Techniques in SQL Server that You Should Know

It may take hours (or days) of investigation and testing to resolve and troubleshoot performance issues with stuck queries. You can shorten this time quickly by defining common design patterns that indicate poor TSQL performance.

By developing this easily recognizable stilt pattern recognition recognition, you can quickly focus on what is most likely to be the problem. Performance tuning events lead to lengthy work on execution plans and statistics are often involved, but if you quickly identify potential pitfalls, all of these businesses are likely to run into trouble.

Table of Contents on this article below:

You will need to be careful to prove that any changes you make are optimal, but it will save you some time knowing where to start.

Tips and tricks

SQL Server can use indexes to effectively filter data sets via WHERE clauses or a combination of filters separated by the AND operator. This process is proprietary, so the data is broken down into smaller chunks until only the result set remains.

OR is a different story. Because SQL Server expires, it cannot be treated as a single transaction. Instead, each OR component must be evaluated independently. After doing this expensive process, you will be able to combine the results and return them as usual.

The worst case scenario for OR is when multiple columns or tables are involved. In addition to evaluating each component in the OR section, you should trace this path through the filters and other tables in the query. Even if it contains only a few tables or columns, performance may be affected.

Here’s a very simple example where OR drastically reduces performance than you can imagine.

SELECT DISTINCT
PRODUCT.ProductID,
PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.ProductID = DETAIL.ProductID
OR PRODUCT.rowguid = DETAIL.rowguid;

The question is simple. I have two tables of productID and rowguid combination for verification, no columns to index, but I want to see the products table and the sales orders table. Expensive, but at least we can find out. The results of this problem:

I checked both tables, but OR links are very computational. 1.2 million calls were made. The product has only 504 rows and SalesOrderDetail121317, so read the records for each complete table. Also, it took me 2 seconds to ask about the SSD desktop.

The only way to get rid of this annoying screen is to run SQL Server or run it across multiple columns. The best way to deal with OR is to leave it as is, if possible, or break it down into smaller questions. It can be difficult to break short, simple questions into long, long questions, but when it comes to surgery it is usually a good choice.

SELECT
PRODUCT.ProductID,
PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.ProductID = DETAIL.ProductID
UNION
SELECT
PRODUCT.ProductID,
PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.rowguid = DETAIL.rowguid

This transcript identifies each OR section and translates it into a SELECT statement. UNION combines the results and removes the copies. This is a new approach.

Implementation is even more difficult. Now you ask each table twice instead of others so you don’t have to press a button with a series of results like you did before. Readings increased from 1.2 million to 750 and questions were answered in seconds, not 2 seconds.

This app has tons of features that require you to test cards four times to meet the requirements. However, the performance is much better than before.

Be careful when writing questions with an OR clause. Make sure it works fine and that you don’t accidentally attach the same operating bomb as you see above. If you are looking for platform malfunctions and performance across multiple columns or tables, consider the possible causes. This simple question pattern often leads to poor performance.

Wildcard String Searches

Finding a good rope can be difficult, but there are many ways to do it more efficiently with a rope that isn’t working properly, it’s best to check the following:

  • Indexes are present on searched columns.
  • Those indexes can be used.
  • If not, can we use full-text indexes?
  • If not, can we use hashes, n-grams, or some other solution?

SQL Server can perform dubious scene searches without additional performance and design concepts. However, it is difficult to access data if you want to check if a column has rows.

SELECT
Person.BusinessEntityID,
Person.FirstName,
Person.LastName,
Person.MiddleName
FROM Person.Person
WHERE Person.LastName LIKE ‘%For%’;

This string search checks the last name whenever “For” appears anywhere in the string. If “%” is at the beginning of the string, you cannot use an ascending index. If “%” is at the end of the string, you cannot use a descending index. The query above provides the following performance:

As expected, the query generates analysis for Person.Person. The only way to know if there is a substring in the text box is to search for each character on each line and find the occurrence of that string. This may be acceptable for small tables, but for large amounts of data, the wait can be slow and painful.

There are several ways to attack this situation, including:

  • Check the program. Should we really be? Are users actually looking for different rows in each of these sections of the column? Otherwise, removing this feature will fix the problem.
  • Can I add more filters to the question before reducing the data size comparison row? Filtering by date, time, status, or the most commonly used criteria can reduce data volume, analyze small amounts of data, and improve searches. He must succeed.
  • Can you develop a general search instead of a job search? Is it possible instead of “%For%” and not “For%”?
  • Can I see the full text? Can I set up and use it?
  • Can you solve the hash or n-gram questions?

One of the last available options would be a good solution for short row columns. The N-Gram is a line segment that can be separated from the information you are looking for and allows you to search for substrates without going through large tables. Before discussing this topic, it is important to understand the search terms used by the application.

Leave a Reply

Your email address will not be published. Required fields are marked *