We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.
- Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName AS SET NOCOUNT ON; --Procedure code here SELECT column1 FROM dbo.TblTable1 -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF; GO
- Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method -- Instead of SELECT * FROM MyTable -- Avoid this method --And finally call the stored procedure with qualified name like: EXEC dbo.MyProc -- Preferred method --Instead of EXEC MyProc -- Avoid this method
- Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
- Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'MyTable' AND type = 'U')
- Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100) DECLARE @Age INT SET @Age = 25 SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age) EXEC (@Query)
If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
DECLARE @Query NVARCHAR(100) SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age' EXECUTE sp_executesql @Query, N'@Age int', @Age = 25
the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.
- Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
- Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
- Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY --Your t-sql code goes here END TRY BEGIN CATCH --Your error handling code goes here END CATCH
Reference: Pinal Dave (https://blog.sqlauthority.com)
181 Comments. Leave new
I have seen in one of the blogs to use EXISTS like
IF EXISTS(Select null from table)
Will it optimize the perfomance better than
IF EXISTS(Select 1 from table)
?
@Divya
It makes no difference what is put there.
Brad Schulz has an interest article on it:
Even 1/0 is allowed! Obviously, it is not evaluated.
Technically, however, * does get expanded, adding some minuscule amount of time:
So, anything other than * takes the same amount of time. * takes an iota longer. Personally, i use *, to show that i do not care what the results are.
Hello Divya,
I not think so because there is no significant difference in transferring of 1 byte and a null value.
Other than that there is no difference between these two queries.
Regards,
Pinal Dave
“I not think so because there is no significant difference in transferring of 1 byte and a null value.”
While that is true in this exact case I like to be pedantic about this and elaborate this a bit and probably confuse everybody up :)
There is no concept NULL value if you think about C code or even the CPU. Pointers in C can have NULL values but that just means that the pointer is pointing to memory in the address 0x00000000 (in 32-bit machine). The actual pointer is still taking sizeof(VOID*) amount of memory even when it points to NULL.
Now if you think about functions in C or any other language they always reserve space in the function stack for the return value. You can say, again in C, that you don’t care about the return value and declare the function to return “void” but still it takes 4 bytes of memory in 32-bit machine for return value.
And actually, of you think about the CPU, there is 32 bit register reserved exactly for this.
And now considering that it makes no difference to return one byte or four bytes since there is space for four bytes anyway. And in fact, many times (all the times?) when you deal with C data types CHAR (1 byte) or SHORT (2 bytes) you end up taking 4 bytes because of the padding to keep memory aligned.
Uh! I almost felt like going back in time some ten years when I was writing kernel drivers for Windows :)
Thank you Marko for providing the details about memory allocation of NULL value.
Regards,
Pinal Dave
Good stuff! Would love a follow up on table/scalar functions performance.
Very good tips, useful for sql developers.
@Brian,
Nice blog posts
But none of them proved that SELECT 1 will outperform SELECT *. They only guess it
@Madhivanan
The Conner article does explain about the extra action being done. Whether this is noticeable or not is another question. But it is an extra action over a non-* query.
after testing several queries using both IF EXISTS(SELECT * AND SELECT 1, in none of my tests did the SELECT 1 statement outperform the SELECT * statement. In all tests the IF EXISTS statement always only returned 1 row, the I/O Cost and CPU Cost were identical whichever statement was used, however my tables were relatively small with less than 10000 records in each.
Thanks for another great post! You continue to provide great posts to the SQL-community.
Sir, This is very useful tips for my site. But also have one problem.
I am searching for a stored procedure which will eliminate all the STOP words like “in”, “the” in my query and accordingly search the result.
Is it correct :-
//This is my stored procedure
CREATE PROCEDURE sp_GetInventory
@location varchar(50) AS
select column1, column2 from table1
where column1 like ‘%SearchString%’ or column2 like ‘%SearchString%’
EXECUTE sp_GetInventory ‘SearchString’
Dear pinal,
very useful tips for Optimization for stored procedures.
Yes it is really good performance not using SP_ProcName.
We are using instead as dbo.USP_ProcName means UserStoredProcedure
Hello Pinal Sir,
These tips are very useful for me…..
Thanks….
It is very helpful article, but I have one comment about the point
Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
I created two stored procedures with the same name in “Master” and “Another new DataBase”, and I executed it from the new one.
The result returned from the new one.
This mean that, it didn’t execute the one in the Master, although there’s a SP in the Mater with the same name.
Can you please elaborate this.
This is a killer post since optimizing database code or SQL statements is a killer job.
However the hints provided here are very useful. Simple optimization hints, that anyone can use, and more advanced but still easy to understand.
About the SELECT 1 versus the SELECT * i think that the first should be preferred over the last.
A few years ago i came across a few optimization tips for Oracle (my previous professional background) and this tip was already mentioned. But later i read that Oracle internally optimizes EXIST (SELECT * FROM …). Perhaps the same approach is used inside SQL Server and that’s why the difference isn’t noticed.
The reason stated for using SELECT ‘ANY_CONSTANT’ over SELECT * is that * would read every column, so it would be more efficient to return a constant. The gains would be obtained in the reduced reading of data.
Regards.
Great article!
Use schema name with object name:
I executed the both queries with schema name and without schema name and checked the execution plan of both the queries.I didn’t find any differrence.
Does anyone have solution how to prove this tip?
Hello Swati,
The difference will be considerable when the queries involves too many tables,views and processes large amount of data.
Regards
Sheju
Pinal,
If we execute the Stored procedure starting with “sp_”
by this way
Exec DBname.dbo.SPname
SQL engine directly go on specified database instead of master DB to search that sp.
Is it right? Let me know your comments.
Thanks
Darshan Shah
Yes. That is correct
i have confused about store procedure becuase query is good to procedure plz expain full how to store procedure and what is use of bussiness inudstires
Hi Pinal,
Could you please tell some what in detail about performance tuning, may be with an example like – If a query takes more time what are all the causes for that or if a stored procedure takes more time what are all causes for that so that many DBA’s as like me who are all in intermediate stage would gain a lot . Thanks!
good afternoon all the query is used to table but what is used of trigges and stored procedure because query is profitable in table but what is used of procedure in table
i dont know what is used of procedure,triggers
plz define with example
what is used of table and database
thankyou
Pinal:
How would one discover / prove via a trace “Do not use the prefix “sp_” in the stored procedure name….first searches in the master database and then in the current session database”
awsome article sir
Truly amazing article…..Really thankful to you Mr.Pinal..