Writing High-Performance Stored Procedures in SQL Server

Rajesh Gami
1d
1.6k
0
0

Article

Understanding Stored Procedures in SQL

When we talk about backend performance, stored procedures play a major role in SQL Server.
They are precompiled, reusable, and secure — but if not written properly, they can become slow and inefficient.

In this article, we’ll learn how to write high-performance stored procedures with simple explanations, tips, and an example flowchart.

🚀 What is a Stored Procedure?

A stored procedure is a group of SQL statements stored in the database. Instead of sending multiple queries from your application, you can just call one stored procedure — making the process faster and easier to maintain.

Example

CREATE PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END

You can execute it like:

EXEC usp_GetCustomerOrders @CustomerID = 1001;

🧠 Why Performance Matters

When you work with large data — millions of rows — even small inefficiencies can slow everything down.
Optimizing your stored procedures can:

Reduce CPU and memory load
Improve response time
Make the system more scalable
Lower the chance of deadlocks or timeouts

⚙️ Best Practices for Writing High-Performance Stored Procedures

Let’s go step by step.

1. Use Proper SET Options

Start every stored procedure with:

SET NOCOUNT ON;

This stops the “(X rows affected)” message from returning, improving speed slightly and reducing unnecessary output.

2. Avoid SELECT *

Don’t select everything — it wastes memory and network bandwidth.

❌ Bad

SELECT * FROM Orders;

✅ Good

SELECT OrderID, CustomerID, OrderDate FROM Orders;

3. Use Proper Indexing

Indexes are like shortcuts in a phone contact list. Without them, SQL Server must scan every row.

Example

CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);

Always check query performance using Execution Plans in SSMS.

4. Avoid Cursors (If Possible)

Cursors process rows one by one — very slow for large data.

❌ Bad

DECLARE cur CURSOR FOR SELECT OrderID FROM Orders;

✅ Good
Use set-based operations instead:

UPDATE Orders SET Status = 'Closed' WHERE OrderDate < '2024-01-01';

5. Use Temporary Tables or Table Variables Wisely

Temporary tables (#TempTable) can help when you have complex joins.

But don’t overuse them — they increase I/O on tempdb.

Use them only when needed:

SELECT * INTO #TempOrders FROM Orders WHERE Status = 'Pending';

6. Parameter Sniffing Control

Sometimes, SQL Server caches an execution plan based on the first parameter it sees, which may not work well for others.

To fix it

DECLARE @LocalCustomerID INT = @CustomerID;
SELECT * FROM Orders WHERE CustomerID = @LocalCustomerID;

This helps avoid performance issues due to parameter sniffing.

7. Use Transactions Carefully

Transactions ensure data consistency, but if they are too long, they can lock resources.

✅ Use

BEGIN TRANSACTION;

-- Your statements
COMMIT TRANSACTION;

❌ Don’t keep a transaction open for long-running logic or unnecessary steps.

8. Use TRY…CATCH for Error Handling

It’s important for clean rollback and error logging.

Example

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    EXEC spLogException ERROR_MESSAGE(), ERROR_LINE();
END CATCH;

9. Avoid Unnecessary Joins

Too many joins, especially on non-indexed columns, can slow performance drastically.

✅ Keep joins only where needed, and always join on indexed keys.

10. Use Execution Plans and Statistics

Before finalizing your procedure:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Then execute your procedure and check the output to understand how much time and I/O it consumes.

Also, view the execution plan (Ctrl + M in SSMS) to find table scans, missing indexes, or inefficient joins.

📊 Flowchart: Stored Procedure Execution Flow

Below is a simple flowchart to visualize how SQL Server executes a stored procedure:

          ┌────────────────────────┐
          │  Client/Application     │
          │  (calls procedure)      │
          └──────────┬──────────────┘
                     │
                     ▼
        ┌────────────────────────┐
        │  SQL Server Receives   │
        │  Stored Procedure Call │
        └──────────┬──────────────┘
                     │
                     ▼
        ┌────────────────────────┐
        │  Check Plan Cache       │
        │  (Existing Execution?)  │
        └──────────┬──────────────┘
           Yes ─────┤              ├──── No
                    ▼              ▼
         ┌────────────────┐  ┌────────────────┐
         │ Use Cached Plan│  │ Generate New    │
         │ (Fast)         │  │ Execution Plan  │
         └───────┬────────┘  └───────┬────────┘
                 │                   │
                 ▼                   ▼
        ┌────────────────────────┐
        │  Execute SQL Commands   │
        └──────────┬──────────────┘
                     │
                     ▼
        ┌────────────────────────┐
        │ Return Results/Errors   │
        │ to Application          │
        └────────────────────────┘

This flow shows how SQL Server optimizes execution by reusing cached plans whenever possible.

🧩 Example: Optimized Stored Procedure

Here’s a real-world optimized procedure example:

CREATE PROCEDURE usp_GetActiveOrders
    @StartDate DATE,
    @EndDate DATE,
    @CustomerID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        SELECT O.OrderID,
               O.OrderDate,
               O.TotalAmount,
               C.CustomerName
        FROM Orders O
        INNER JOIN Customers C ON O.CustomerID = C.ID
        WHERE O.OrderDate BETWEEN @StartDate AND @EndDate
          AND (@CustomerID IS NULL OR O.CustomerID = @CustomerID)
          AND O.Status = 'Active'
        ORDER BY O.OrderDate DESC;
    END TRY
    BEGIN CATCH
        EXEC spLogException ERROR_MESSAGE(), ERROR_LINE();
    END CATCH;
END

✅ Key Points

Used SET NOCOUNT ON
Controlled optional parameter
Filtered with indexes
Handled errors safely

🧠 Quick Summary

Best Practice	Description
SET NOCOUNT ON	Reduces overhead
Avoid SELECT *	Improves speed
Use Indexes	Faster lookups
Avoid Cursors	Use set-based logic
Handle NULLs	Prevent missing data
Use TRY/CATCH	Safe error handling
Manage Transactions	Keep short and clean
Review Execution Plan	Identify performance issues
Control Parameter Sniffing	Avoid bad caching
Avoid Unnecessary Joins	Keep it simple and fast

✅ Final Thoughts

Writing high-performance stored procedures isn’t about writing complex SQL — it’s about writing smart, clean, and efficient SQL.

Remember:

“A slow query written once can slow your application every single day.”

So, always think before you write:

Do I need all the columns?
Are my joins indexed?
Is my transaction minimal?

If you keep these questions in mind, your stored procedures will run fast and stay maintainable for years.