Lead Paragraph
This article provides best practices and guidelines for TSQL developers for improvement of the performance of SQL server database.
Introduction
Writing TSQL queries has become extremely easy for developers and that can be easily learned by anyone. But TSQL simplicity has also makes it easy to write poorly performing unstructured queries. In this article I have mentioned few important tips and guidelines for TSQL developers for improving the query performance for SQL server database.
Parameterized Queries
When you are executing series of TSQL queries from frontend application, use parameterized queries for better performance. Parameterized queries gives better performance by compiling the query once and executing the same compiled query multiple times. But you should also use the same command object to cache the query plan. Following is an example of parameterized query to return student information based on student roll number.
SELECT Student_Name, Class, Subjects FROM Students WHERE Roll_No =?
Creating Necessary Indexes
Create useful indexes to improve TSQL query performance. Before creating indexes, you must know how the data is used, the types of queries you would like to run on top of SQL server data so that the query processor can use indexes to find your data quickly. Index creation rules are simple and straight forward, if your queries are mostly SELECT statements; more indexes can be helpful for faster execution. But DML (INSERT, UPDATE, and DELETE) operations will get slow down if you have more indexes. Also try to avoid creating indexes on very small tables with just a few data pages. This saves the cost of loading and processing index pages.
Create indexes on primary keys and frequently used foreign keys because primary keys and foreign keys are mostly used in join conditions. If your query joins tables by using other columns, create indexes on those columns for the same reason.
To improve performance you can create Indexes on columns used in the WHERE clause of your TSQL statement. Performance of query depends on the selectivity of index. Selectivity is the ratio of qualifying rows to total rows. If the ratio is low, the index is highly selective. An index that is not selective is not as useful. All unique indexes are selective. You can evaluate the selectivity of an index by running the sp_show_statistics stored procedures on SQL Server.
Multiple-column indexes are useful for faster executing of filter expressions that match a prefix set of key columns. For example, the index CREATE INDEX Idx_Student_Name ON Student ("Last Name" ASC, "First Name" ASC) helps faster execution of the following queries:
... WHERE "Last Name" = 'Pal'
... WHERE "Last Name" = 'Pal' AND "First Name" = 'Tapas'
... WHERE "First Name" = 'Tani' AND "Last Name" = 'Paul'
Replace Subqueries With Joins
Sometimes JOIN gives better performance compared to subquery. Using JOIN you can manually control the order that data is selected. You can also decrease query execution time by forcing SQL server to pre-filter data in the table. The following query displays student details with mark obtained in different subjects.
SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
(SELECT Markset.PassMark
FROM dbo.[Marks Details] AS Markset
WHERE stu.Subject= Markset.Subject) AS PassMark
FROM Students AS stu
The above nested query can be replaced by following INNER JOIN query for better performance.
SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
Markset.PassMark
FROM Students AS stu
INNER JOIN dbo.[Marks Details] as Markset
On stu.Subject= Markset.Subject
Using Temporary Tables
SQL Server DBAs and TSQL developer’s loves to write SELECT INTO statement for data transfer from system tables, like this:
SELECT *
INTO #MyTempTable
FROM sysobjects
But the above query will create table locks when dealing with large number of records. As a result, other queries and procedures those need to use sysobjects object will have to wait until long-running query execution is complete. You can avoid system table locking by manually creating the temporary table with the CREATE TABLE command and load the data afterwards with another SQL query.
For example…
CREATE TABLE #MyTempTable
(All the Necessary Columns)
INSERT INTO # MyTempTable
SELECT *
FROM sys.objects
The above query will have same number of locks as the first one; but the time of the table lock period will be shorter for the second one compared to the previous query. This will allow other processes and objects to use system table.
Typically, when developing SQL code the development server has only a single user or few users. When working on SQL code, it's important to know when the code will impact sessions other than the current session. And unexpected interaction can cause major performance issues.
Stored Procedure Performance Improvement
Most of the time database developers follow standard naming convention while creating database objects. Like, while creating a stored procedure we start with prefix sp_. But this naming convention is not correct. All the system stored procedures begins with sp_. So when you write a custom stored procedure with prefix sp_ SQL server execution engine first searches definition of the stored procedure in master database before looking into the database it is called in. This may result slow performance.
Use the SET NOCOUNT ON statement on top of stored procedures to prevent SQL server from sending the message to the caller for each statement in a stored procedure. Each message contains the number of affected rows for the respective statement.
Conclusion
If you think a query is taking too much time to execute and not giving you expected performance use SQL server profiler for creating trace and viewing performance of your query in live environment. SQL developers usually can obtain best performance of TSQL queries by following correct development techniques.
