Optimizing SQL Server Stored Procedures for Large-Scale Data Insertion

As the volume of data in your SQL Server database continues to grow, optimizing your stored procedures becomes increasingly crucial. In this article, we will explore ways to improve performance when inserting large amounts of data into multiple tables using stored procedures.

Understanding the Problem

In the given scenario, you are dealing with a large number of products from an Excel file and want to store them in two SQL Server tables: products and Inventory. You have implemented a stored procedure (AddProductsWithCat_Sp) to handle this process. The procedure takes various parameters, including product details and inventory information.

The query is currently working fine but is taking too much time to save all the data. This suggests that there are opportunities for optimization to improve performance.

Optimization Strategies

1. Using Indexing

One of the most effective ways to speed up queries in SQL Server is by creating indexes on columns used in WHERE, JOIN, and ORDER BY clauses. In your stored procedure, you can create indexes on the Barcode column in the Products table and the Category_Id column in the Categories table.

Create a clustered index on the Barcode column:

CREATE CLUSTERED INDEX IX_Products_Barcode ON Products (Barcode);

Create a non-clustered index on the Category_Id column:

CREATE NONCLUSTERED INDEX IX_Categories_Category_Id ON Categories (Category_Id);

2. Using Partitioning

If your table is extremely large, consider partitioning it to split data across multiple files. This approach can reduce the amount of memory required for queries and improve performance.

To partition a table in SQL Server, you need to create separate partitions using the CREATE PARTITION FUNCTION and CREATE PARTITION SCHEME statements:

CREATE PARTITION FUNCTION pf_Products (INT RANGE FIXED) AS RANGE RIGHT FOR VALUES (1000000);

Create a partition scheme for the Products table:

CREATE PARTITION SCHEME ps_Products (
    STATE = ONLINE,
    (PARTITION p_1 VALUES LESS THAN (1000000));

Insert data into the partitioned table using the INSERT INTO statement, specifying the partition number.

3. Using Full-Text Search

If your query involves full-text search on a column like Barcode, consider enabling full-text indexing for that column.

Enable full-text indexing on the Barcode column:

ALTER TABLE Products ADD FULLTEXT INDEX ON (Barcode);

Create a full-text catalog:

CREATE FULLTEXT CATALOG catalog_name;

4. Reducing Network Traffic

When executing a stored procedure, network traffic can be reduced by enabling batch mode.

Enable batch mode using the SET BATCH MODE ON statement.

5. Optimize Table Variables

When working with large amounts of data in SQL Server, table variables can help reduce memory usage.

Create a table variable to store temporary results:

DECLARE @CheckBarcode TABLE (ProductId INT);

Insert data into the table variable using an INSERT INTO statement.

INSERT INTO @CheckBarcode VALUES (@CheckBarcode)

Use the table variable in your stored procedure instead of selecting from the Products table.

6. Using Query Optimizer

SQL Server’s query optimizer is a powerful tool that can analyze and optimize queries to improve performance.

Run the following statement to enable query optimization:

SET SHOW ADVANCED OPTIONS ON;

Use the EXPLAIN statement to analyze and optimize your stored procedure:

EXECUTE sp_explain 'AddProductsWithCat_Sp';

7. Regular Maintenance

Regular maintenance tasks, such as running DBCC CHECKDB, can help identify and fix issues that may be slowing down your queries.

Run the following statements on a regular basis:

DBCC CHECKDB;
DBCC CHECKTABLE;
DBCC RUNSTATS;

By implementing these optimization strategies, you can significantly improve the performance of your stored procedures when dealing with large amounts of data in SQL Server.

Last modified on 2025-04-01