Cách viết Stored Procedure SQL Server tối ưu tốc độ cho hệ thống ERP

 Trong hầu hết các hệ thống ERP, Stored Procedure (SP) là “trái tim” xử lý dữ liệu:

• Báo cáo
• Tổng hợp
• Tính lương
• Kết chuyển
• Đồng bộ dữ liệu

Nhưng cũng chính Stored Procedure là nguyên nhân hàng đầu gây chậm hệ thống, nếu viết sai cách.

Mình từng gặp nhiều hệ thống:

• SP chạy vài chục giây
• CPU SQL Server luôn >80%
• Giờ cao điểm thì ERP gần như “đóng băng”

👉 Vấn đề không nằm ở SQL Server, mà nằm ở cách viết Stored Procedure.

Bài viết này tổng hợp kinh nghiệm thực chiến, tập trung vào:

• Tối ưu tốc độ
• Xử lý bảng lớn
• Index đúng cách
• Viết SP có thể chạy ổn định nhiều năm

---

🔹 Nguyên tắc vàng khi viết Stored Procedure cho ERP

Stored Procedure không phải để viết cho “chạy được”, mà để “chạy lâu dài”.

Bạn cần nhớ 4 nguyên tắc cốt lõi:

1. Giảm IO
2. Tránh scan bảng lớn
3. Tận dụng index
4. Tính toán càng gần dữ liệu càng tốt

---

🔹 Tránh viết Stored Procedure kiểu “select trước, tính sau”

❌ Sai lầm phổ biến

SELECT *
FROM CCGioCong
WHERE NgayCong BETWEEN @TuNgay AND @DenNgay

Sau đó xử lý:

• SUM
• IF
• CASE
• GROUP BY ở tầng ứng dụng

👉 Với bảng vài triệu dòng, cách này phá hiệu năng.

---

✅ Viết SP đúng cách

SELECT 
    MaNhanVien,
    SUM(SoGio) AS TongGio
FROM CCGioCong
WHERE NgayCong BETWEEN @TuNgay AND @DenNgay
GROUP BY MaNhanVien

👉 Đưa tính toán xuống SQL Server, nơi dữ liệu đang nằm.

---

🔹 SELECT bảng lớn nhiều lần – lỗi cực kỳ nguy hiểm

❌ Ví dụ thường gặp

SELECT ...
FROM CCGioCong
WHERE NgayCong BETWEEN @TuNgay AND @DenNgay

SELECT ...
FROM CCGioCong
WHERE NgayCong BETWEEN @TuNgay AND @DenNgay

👉 Mỗi lần gọi là 1 lần scan bảng lớn.

---

✅ Giải pháp đúng: dùng bảng tạm

SELECT *
INTO #TmpGioCong
FROM CCGioCong
WHERE NgayCong BETWEEN @TuNgay AND @DenNgay

Sau đó:

SELECT ... FROM #TmpGioCong
SELECT ... FROM #TmpGioCong

👉 Scan 1 lần, dùng nhiều lần.

---

🔹 Temporary Table hay Table Variable?

❌ Lầm tưởng phổ biến

“Table variable nhanh hơn temp table”

👉 Sai với bảng lớn.

So sánh thực tế

Trường hợp	Nên dùng
< 1.000 dòng	Table variable
> 10.000 dòng	Temp table
Có JOIN, GROUP	Temp table

👉 ERP gần như luôn nên dùng temp table.

---

🔹 Đánh index cho bảng tạm – rất nhiều người bỏ qua

❌ Sai lầm

Tạo #Temp nhưng không có index.

✅ Cách làm đúng

CREATE TABLE #TmpGioCong (
    MaNhanVien INT,
    NgayCong DATE,
    SoGio DECIMAL(5,2)
)

CREATE NONCLUSTERED INDEX IX_TmpGioCong
ON #TmpGioCong (MaNhanVien, NgayCong)

👉 Điều này giảm cực mạnh IO khi JOIN hoặc GROUP BY.

Tối ưu viết SQL Store Procedure

---

🔹 Index cho Stored Procedure – hiểu đúng bản chất

❌ Sai lầm phổ biến

• Tạo index theo cảm tính

• Tạo index cho mọi cột

• Không kiểm tra Execution Plan

✅ Index phục vụ Stored Procedure

Nguyên tắc:

• Index theo WHERE

• Sau đó đến JOIN

• Cuối cùng là SELECT

Ví dụ:

CREATE NONCLUSTERED INDEX IX_CCGioCong_Ngay_MaNV
ON CCGioCong (NgayCong, MaNhanVien)
INCLUDE (SoGio, TangCa)

👉 SP lọc theo ngày + nhân viên → index này ăn ngay.

---

🔹 Tránh hàm trong WHERE của Stored Procedure

❌ Ví dụ phá index

WHERE YEAR(NgayCong) = @Nam

👉 SQL Server không dùng index.

✅ Viết đúng

WHERE NgayCong >= DATEFROMPARTS(@Nam,1,1)
  AND NgayCong < DATEFROMPARTS(@Nam+1,1,1)

---

🔹 Parameter Sniffing – kẻ giết hiệu năng âm thầm

Stored Procedure có thể:

• Chạy nhanh với dữ liệu nhỏ
• Nhưng cực chậm với dữ liệu lớn

👉 Nguyên nhân: Parameter Sniffing.

Cách xử lý phổ biến

OPTION (RECOMPILE)

Hoặc:

DECLARE @TuNgayLocal DATE = @TuNgay

👉 Dùng khi SP phục vụ nhiều kịch bản dữ liệu khác nhau.

---

🔹 Không dùng CURSOR nếu chưa hết cách

❌ CURSOR trong ERP

• Chậm
• Khó mở rộng
• Tốn CPU

✅ Thay bằng set-based

UPDATE A
SET A.TongGio = B.TongGio
FROM NhanVien A
JOIN (
    SELECT MaNhanVien, SUM(SoGio) TongGio
    FROM CCGioCong
    GROUP BY MaNhanVien
) B ON A.MaNhanVien = B.MaNhanVien

👉 Nhanh hơn CURSOR nhiều lần.

---

🔹 Case thực tế: Stored Procedure chạy từ 60s xuống 3s

Trước tối ưu:

• SP báo cáo lương
• Bảng 15 triệu dòng
• Dùng CURSOR
• Không temp table
• Không index phù hợp

Sau tối ưu:

• Dùng temp table
• Đánh index đúng
• Set-based processing
• Tránh scan lặp

👉 Kết quả:
⏱ 60 giây → 3 giây

---

🔹 Checklist viết Stored Procedure chuẩn ERP

✅ Không SELECT *
✅ Không scan bảng lớn nhiều lần
✅ Có index cho WHERE/JOIN
✅ Dùng temp table khi cần
✅ Tránh CURSOR
✅ Xem Execution Plan trước khi deploy

---

🔹 Kết luận

Stored Procedure không khó, nhưng viết đúng rất khó.

Trong ERP:

1 Stored Procedure viết sai có thể kéo sập cả hệ thống.

Nếu bạn viết SP với tư duy:

• Giảm IO
• Dữ liệu lớn là mặc định
• Nghĩ đến 3–5 năm sau

👉 Hệ thống sẽ chạy ổn định, bền vững, không cần nâng server liên tục.

---

🔹 FAQ

❓ Stored Procedure hay View nhanh hơn?
👉 SP nhanh hơn vì có kế hoạch thực thi rõ ràng.

❓ Khi nào cần partition?
👉 Khi bảng > 50–100 triệu dòng.

❓ Có nên logic hết vào SP?
👉 Logic dữ liệu → SP, logic nghiệp vụ phức tạp → code.