Optimization of stored procedure performance

1. Use Set NOCOUNT (after setting "message" less affected rows, reduce network traffic)

SET NOCOUNT on

Go

SQL statements

SET NOCOUNT OFF

Go

2. Using a defined Schema

Example: SELECT * from Dbo.table is better than select * FROM table performance

If a table is my.table, then the direct query color SELECT * FROM table will be error-

3. The stored procedure should not start with "sp_" because this is the beginning of the system default stored procedure, and the system will filter more

4. Replace EXEC with sp_executesql

sp_executesql can use parameters, exec is a direct spelling SQL string

5. Use fewer cursors (SQL is set-oriented, cursor queries are row-oriented)

6. The transaction should not be too long (too long will cause concurrent operation blocking, resulting in very slow query, very low CPU utilization)

7. Using Try-catch to handle exceptions

Optimization of stored procedure performance