Optimize stored procedures in SQL Server

Good day, habrachelovek. Today I would like to discuss with you the topic of stored procedures in SQL Server 2000-2005. Recently, writing them has occupied the lion's share of my time at work, and why hide it - at the end of work with this case there is enough information left that I’d be happy to share with you% user%.
The knowledge that I am going to share, unfortunately, (or fortunately) is not empirically acquired by me, but is, to a greater extent, a free translation of some articles from the bourgeois Internet.
So, as the name implies, it will be about optimization. I’ll make a reservation right away that all the actions that I will now describe really give a significant (some more, some less) performance increase.
This article does not claim to fully disclose the topic of optimization; rather, it is a collection of practices that I apply in my work and can vouch for their effectiveness. Go!


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

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
--Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO


Well, I think it’s clear. This operation tells the server where to look for objects and instead of randomly rummaging through its bins, he will immediately know where he needs to go and what to take. With a large number of databases, tables, and stored procedures, it can significantly save our time and nerves. 3. Do not use the prefix “sp_” in the name of your stored procedures: If the name of our procedure begins with “sp_”, SQL Server will first search in its main database. The fact is that this prefix is ​​used for personal internal server stored procedures. Therefore, its use can lead to additional costs and even incorrect results if the procedure with the same name as yours is found in its database. 4. Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *):

SELECT * FROM dbo.MyTable --Вот так делать хорошо
-- Вместо
SELECT * FROM MyTable --А так делать плохо
--Вызов процедуры
EXEC dbo.MyProc --Опять же хорошо
--Вместо
EXEC MyProc --Плохо!



To check for a record in another table, we use the expression IF EXISTS. This expression returns true if at least one value is returned from the internal expression, it doesn’t matter “1”, all columns or the table. The returned data, in principle, is not used in any way. Thus, it is more logical to use “1” to compress traffic during data transfer, as shown below: 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. With 2005 SQL Server, a more correct and convenient way to solve this problem appeared: Conclusion

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




BEGIN TRY
--код
END TRY
BEGIN CATCH
--код отлова ошибки
END CATCH



In principle, for today I have everything. I repeat once again that 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.