SQL Server Stored Procedure Optimization

Good day, habrachelovek. Today I would like to discuss with you the topic of stored procedures in SQL Server 2000-2005. Recently, their writing took the lion’s share of my time at work and what’s there to hide - at the end of the work with this case there is enough information that I’ll be happy to share with you% user%.
The knowledge that I am going to share, unfortunately, (or fortunately) is not empirically gained by me, but is, to a greater extent, a free translation of some articles from the bourgeois Internet.
So, as you can see from the title, it’s about optimization. At once I will make a reservation that all the actions that I will describe now really give a significant (some bigger, some smaller) performance gain.
This article does not pretend to fully disclose the topic of optimization; rather, it is a collection of practices that I use in my work and can vouch for their effectiveness. Go!


1. Include a line in your procedures - SET NOCOUNT ON: With each DML expression, the SQL server carefully returns to us a message containing the number of processed records. This information may be useful to us during the debugging of the code, but after it will be completely useless. By setting the SET NOCOUNT ON, we disable this feature. For stored procedures containing several expressions or \ and cycles, this action can give a significant performance boost, because the amount of traffic will be significantly reduced.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--
SELECT column1 FROM dbo.TblTable1
-- SET NOCOUNT
SET NOCOUNT OFF;
GO

')
2. Use the name of the schema with the name of the object: Well, then I think it is clear. This operation tells the server where to search for objects, and instead of randomly rummaging through its bins, it will immediately know where it needs to go and what to take. With a large number of databases, tables and stored procedures can significantly save our time and nerves.

SELECT * FROM dbo.MyTable --
--
SELECT * FROM MyTable --
--
EXEC dbo.MyProc --
--
EXEC MyProc --!

3. Do not use the “sp_” prefix in the name of your stored procedures: If the name of our procedure starts with “sp_”, SQL Server will first look for it in its main database. The fact is that this prefix is ​​used for personal internal stored procedures of the server. Therefore, its use can lead to additional costs and even wrong results if the procedure with the same name as you will be found in its database.

4. Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *): To check for an entry in another table, we use the expression IF EXISTS. This expression returns true if at least one source is returned from the inner expression, it doesn’t matter "1", all columns or table. Returned data is not used in principle. Thus, it is more logical to use “1” to compress traffic during data transfer, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')


5. Use TRY-Catch to catch errors: Before the 2005 server, after each request, a huge number of error checks were written in the procedure. More code always consumes more resources and more time. Since 2005, SQL Server has had a more correct and convenient way to solve this problem:

BEGIN TRY
--
END TRY
BEGIN CATCH
--
END CATCH


Conclusion
In principle, today I have everything. Once again, here are only those techniques that I personally used in my practice, and I can vouch for their effectiveness.

PS
My first post, do not judge strictly.