We are writing lots of different SQL statement to retrieve the same result from the database. But among the selection or writing the best or optimize SQL to retrieve the data to increase the performance. For that we need to tune or optimized the query to increase the performance while fetching the result from database.
When optimizing your database server, you need to tune the performance of individual queries. This is as important as – perhaps even more important than – tuning other aspects of your server installation that affect performance, such as hardware and software configurations.
Even if your database server runs on the most powerful hardware available, its performance can be negatively affected by a handful of misbehaving queries. In fact, even one bad query will cause serious performance issues for your database.
Overall performance of your database can be greatly improved by proper designing of table tuning and a set of most expensive or most often executed queries.In this blog, we will look at some of the techniques which we can implement to identify and tune the most expensive and worst performing queries.
SELECT * FROM OfferTable WHERE LOWER(UserName)='jshah' Instead of writing it like the below SELECT * FROM OfferTable WHERE UserName='jshah'
Infact both the queries does the same work but the 2nd one is better and retrieves rows more speedily than the first query. Because Sql Server Database collation is default case insensitive. But if the Database collation is set to case sensitive, then you need to use the first one.
= >,>=,<, <= LIKE <>
SELECT * FROM OfferTable WHERE Status = 1 AND OrderID IN (2,20,50) Takes more time than SELECT * FROM OfferTable (INDEX=IX_OrderID) WHERE Status = 1 AND OfferID IN (2,20,50)
SELECT * FROM SupplierTable WHERE SupplierName LIKE 'm%' SELECT * FROM SupplierTable WHERE SupplierName LIKE '%m'
In the first query the Query optimizer is having the ability to use an index to perform the query and thereby reducing the load on sql server. But in the second query, no suitable index can be created while running the query.
SELECT * FROM SupplierTable WHERE SupplierID BETWEEN (300 AND 303) Performs better than SELECT * FROM SupplierTable WHERE SupplierID IN (300,301,302,303)
SELECT * FROM SupplierTable WHERE SupplierName LIKE 'n%' Is much better than writing SELECT * FROM SupplierTable WHERE SUBSTRING(SupplierName, 1, 1)='n'.
SELECT SupplierID, FirstName, LastName FROM SupplierTable WHERE City = 'Vadodara' or ZIP = '380010 or State= 'Gujarat'
The above query to use and index, it is required to have indexes on all the 3 columns.
The same query can be written as
SELECT SupplierID, FirstName, LastName FROM SupplierTable WHERE City = 'Vadodara' UNION ALL SELECT SupplierID, FirstName, LastName FROM SupplierTable WHERE ZIP = 380010' UNION ALL SELECT SupplierID, FirstName, LastName FROM SupplierTable WHERE State= 'Gujarat'
Both the queries will provide same results but if there is only an index on City and no indexes on the zip or state, then the first query will not use the index and a table scan is performed. But the 2nd one will use the index as the part of the query.
SELECTcolumn_listFROMtableWHERE0< (SELECTcount(*) FROM table2 WHERE ..)
Instead, use:
SELECTcolumn_listFROMtableWHERE EXISTS (SELECT * FROM table2 WHERE ...)
When you use COUNT(), SQL Server does not know that you are doing an existence check. SQL Server will counts all matching values, instead of doing a table scan or by scanning the smallest non-clustered index.
When using EXISTS, SQL Server knows you are doing an existence check. On finding the first matching value, it returns TRUE and stops further looking. The same thing also applies to using COUNT() instead of IN or ANY.
SELECT * FROM SupplierTable WHERE City = Vadodara' OPTION(FAST n)
Where n = number of rows that we want to display as fast as possible. This hint helps to return the specified number of rows as fast as possible without bothering about the time taken by the overall query.
To Tuning of query, we usually start looking or analysing the execution plan of that query. The execution plan shows the sequences of operations performed, physical and logical the SQL server will perform to fulfil the query and produce the desired result set.The execution plan is produced by a database engine component called Query Optimizer during the optimization phase of processing query, this takes into account many factors, like the search predicates to used in the query, the number of tables which involves and join conditions between the tables, the list of columns returned and the indexes used in that.
As with most performance optimization techniques, there are tradeoffs. For example, with more indexes, SELECT queries will potentially run faster. However, DML (INSERT, UPDATE, and DELETE) operations will slow down significantly because more indexes must be maintained with each executed operations. So if queries are mostly SELECT statements, more indexes can be helpful. If your application performs many DML operations, you should be conservative with the number of indexes you create.
Whenever any of your query performs slowly, you can view the estimated (and, actual if required) execution plan and can identify the item that is taking the most amount of time (in terms of percentage) in the query. When you start reviewing or thinking about any TSQL for optimization, the very first thing we would like to do is view the execution plan. You will most likely quickly identify the area in the SQL that is creating the bottleneck in the overall SQL.
Keep watching for the following costly operators in the execution plan of your query. If you find one of these, you are likely to have problems in your TSQL and you need to re-factor the TSQL to improve performance.
The AI research landscape is currently one of the most dynamic and vibrant fields, showing no signs of slowing down…
In the dynamic landscape of technology, cloud computing emerges as the linchpin of innovation. Did you know the cloud computing…
Did you know thе sеcrеt bеhind Ubеr's ability to connеct drivеrs and ridеrs quickly and еfficiеntly? The answer is Michaеlangеlo!…
DevOps, the buzzword of yesteryears, is a concrete reality in forward-moving enterprises today. Organizations are actively adopting DevOps practices to…
Cloud computing and Artificial Intelligence (AI) are two fundamental pillars that are driving businesses forward in numerous ways beyond the…
Cloud computing has revolutionized the way businesses operate by providing a highly scalable, flexible, and cost-effective way to manage IT…
View Comments
For those who are looking for Oracle Performance Tuning
Thank you for your comment. Please stay tuned for more information on technology-related topics.
that's really good information on SQL quey optimization and performance tuning as well.
Due to query optimization we get a efficient way
to execute a given query.
Thank you for your comments. Glad to know you liked the read. You can follow us here: https://www.linkedin.com/company/helios-solutions for regular updates.