Performance Tuning

Tối ưu workbook Excel khi dùng DVDAddin với data lớn — > 10,000 hàng, nhiều UDF, hàng trăm sheet.

Đo lường trước khi tối ưu

Tools

Tool	Mục đích
Excel Task Manager	View → Task Pane → Performance — show calc time per formula
Process Explorer	sysinternals.com — xem memory + CPU của EXCEL.EXE
Sysinternals Process Monitor	Track file I/O nếu nghi vấn HDD bottleneck
DVDAddin Batch Print phase report	Built-in — show từng giai đoạn (Calculate / AutoFilter / Export) lâu nhất

Baseline đo

Trước khi optimize → đo baseline:

1. Đóng tất cả app khác.
2. Reset Excel: File → Options → Resources → Set to default.
3. Mở workbook → ghi thời gian load.
4. Trigger recalc (F9) → ghi thời gian.
5. Save → ghi thời gian.

Sau optimize → đo lại → so sánh.

Top 10 nguyên nhân workbook chậm

#1 — Quá nhiều UDF cell-level

UDF chạy qua COM bridge → ~3-5x chậm hơn formula built-in.

Detect: Workbook có 1000+ cell =DvdXxx(...) → recalc lâu.

Fix:

• Convert UDF result → static values (Ctrl+C → Paste Special → Values).
• Hoặc dùng dialog thay UDF (vd: Translate dialog thay 1000 cell =DvdTranslate*).

#2 — Volatile functions

NOW(), TODAY(), RAND(), OFFSET(), INDIRECT(), DvdAutoHideRows() → recalc mỗi lần ANY cell đổi.

Detect: Edit 1 cell trong sheet khác → status bar hiện "Calculating (XX%)" lâu.

Fix:

• Thay OFFSET(A1,1,0,10,1) → A2:A11 (cứng).
• Thay INDIRECT("Sheet1!"&A1) → CHOOSE / IF (nhiều if).
• NOW() chỉ 1 cell duy nhất, các cell khác tham chiếu cell đó.

#3 — VLOOKUP toàn cột

=VLOOKUP(A1, BangGia!A:Z, 8, FALSE) — Excel quét full column A của BangGia mỗi cell.

Fix:

• Đổi sang range cố định: =VLOOKUP(A1, BangGia!$A$2:$Z$1000, 8, FALSE).
• Hoặc dùng INDEX/MATCH — nhanh hơn 5-30% với data lớn.
• Hoặc convert BangGia thành Excel Table (Ctrl+T) → reference theo column name (Excel optimize internal).

#4 — Conditional Formatting với formula

Mỗi CF rule với formula chạy cho mỗi cell trong range mỗi lần recalc.

Detect: Workbook có 50+ CF rule + ranges lớn.

Fix:

• Gom range — thay 100 rules trên 100 cell riêng → 1 rule trên range tổng.
• Đơn giản hóa formula — dùng cell value check thay formula phức tạp.
• Disable CF tạm: Home → Conditional Formatting → Clear Rules (chỉ tạm thời để debug).

#5 — Data Validation phức tạp

Cell có Data Validation =COUNTIF(...) = 0 → recalc mỗi lần value đổi.

Fix: Đơn giản hóa list. Dùng Named Range tĩnh thay vì dynamic.

#6 — Image / Shape quá nhiều

Workbook nhiều ảnh nhúng → memory tốn nhiều, render lag.

Detect: File .xlsx > 50MB nhưng cell text không nhiều.

Fix:

• Resize ảnh trước khi nhúng.
• Dùng Picture Comment thay vì nhúng cell.
• Save as .xlsb (Binary Excel) → nhỏ hơn 50-70%.

#7 — Pivot Table quá lớn

Pivot từ source 100k+ row → recalc lâu.

Fix:

• Source dùng Excel Table (faster than range).
• Pivot → Options → Data → "Refresh data when opening file" = OFF (manual refresh).
• Dùng Data Model + Power Pivot cho > 1M rows.

#8 — File chia sẻ qua mạng

File mở từ network drive (\\server\share\file.xlsx) → mỗi save trùng lag.

Fix:

• Copy về local → edit → upload lại.
• Hoặc dùng OneDrive sync (file local, sync ngầm).

#9 — Nhiều External Reference

='[OtherFile.xlsx]Sheet'!A1 → Excel cần read file kia khi recalc.

Detect: File mở chậm + có popup "Update External References".

Fix:

• Convert external link → value (Edit Links → Break Link).
• Hoặc dùng Connect Server thay external file references.

#10 — Add-ins khác conflict

PowerPivot + Solver + Analysis ToolPak + Antivirus add-in + DVDAddin → mỗi add-in slow Excel start ~1-3s.

Fix: Tắt add-in không dùng (File → Options → Add-ins → Manage → Go → uncheck).

DVDAddin-specific tuning

Auto-calc trong workbook UDF nặng

Mode mặc định: Auto — Excel recalc mỗi cell change.

Workbook có nhiều UDF async → switch sang Manual:

• File → Options → Formulas → Calculation options → Manual.
• Manually F9 để recalc khi cần.

→ Edit data không trigger 100 API call.

Tắt Telemetry

DVDAddin gửi anonymous telemetry mỗi 4 giờ (nếu user opt-in). Tắt:

• Preferences → General → Telemetry — bỏ tick.

Tiết kiệm ~50ms mỗi 4 giờ. Không nhiều nhưng cho user paranoid.

Disable check-update auto

DVDAddin tự check update mỗi lần Excel khởi động (~200ms network call).

Tắt: Preferences → General → Auto-check update → uncheck.

→ Excel start nhanh hơn 200ms. Update manual qua menu.

Reduce concurrent API calls

DvdTranslate* mặc định 10 concurrent. Nếu network chậm + workbook nhiều UDF translate → giảm:

(Tính năng config chưa expose qua Preferences — sẽ thêm 2.8.x. Hiện tại advanced user có thể edit registry.)

Batch Print performance

Batch Print loop qua N vòng, mỗi vòng có 7 phase. Phase report cuối cho biết bottleneck:

⏱ Tổng: 45.12s
   • Ghi driver cell:       1.23s (3%)
   • Calculate:             8.45s (19%)
   • AutoFilter:            0.12s (0%)
   • Resolve+Select sheet:  2.34s (5%)
   • Auto-fit merge:        4.56s (10%)
   • Export PDF|In:         28.4s (63%)
   • Gộp PDF (PdfSharp):    0.5s (1%)

Calculate phase > 30% → bảng có công thức nặng → giảm UDF / volatile / array formulas.

Auto-fit merge phase > 20% → giảm range trong Mucluc!C.

Export phase ~70% → bình thường, không tối ưu được (Excel native).

Memory consumption

Detect leak

Process Explorer (sysinternals):

• Add column "Working Set" + "Private Bytes".
• Monitor EXCEL.EXE qua thời gian.

Memory tăng dần → có thể có COM object leak từ macro/add-in.

Fix

Tắt all add-in → mở file → đo memory → bật từng add-in → tìm cái leak.

DVDAddin được test memory leak free. Nếu thấy DVDAddin leak → bug report.

Multi-thread

Excel built-in threading

File → Options → Advanced → Formulas → "Enable multi-threaded calculation":

• Mặc định: number of processors.
• Set explicit cao hơn nếu CPU có nhiều core.

Lưu ý: chỉ formula built-in được parallelize. UDF tự nhiên single-threaded.

DVDAddin async UDF

DvdTranslate*, DvdAskGemini, DvdHttpGet chạy async ngầm — không block Excel UI.

→ Workbook có 100 UDF async → tất cả 100 chạy song song (giới hạn SemaphoreSlim).

File size optimization

Save as .xlsb

.xlsb (Excel Binary Workbook) = same features .xlsx nhưng nén tốt hơn:

• File size: ~30-50% nhỏ hơn.
• Mở/save nhanh hơn.
• Đôi khi compatible issue với 3rd-party tools.

→ Dùng .xlsb cho file > 20MB.

Clean unused cells

Workbook có cell trống ở row 1,000,000 (do paste accident) → Excel coi sheet to.

Fix:

1. Ctrl+End → jump tới "last cell" Excel nghĩ.
2. Nếu jump xa quá nội dung thật → có cell ẩn / empty format.
3. Select rows/columns thừa → Right-click → Delete.
4. Save → reload → Ctrl+End → giờ jump tới đúng vị trí.

Compress images

File → Compress Pictures → 96 ppi (email) hoặc 150 ppi (web).

→ File giảm 50-90% size nếu nhiều ảnh.

Liên quan

• Best Practices — general tips.
• Backup & Restore — strategy.
• Power User Tips — advanced.
• Troubleshooting #18 — workbook chậm.