From 1.5 Hours to 3 Seconds: How I Fell in Love with Performance Optimization

#2016 #Career Memoir

英文版 | English Version

There’s a moment in every engineer’s career when something clicks. For me, it happened staring at a SQL job that ran for 1.5 hours every single day.

I was working at a semiconductor packaging company, maintaining an HR data synchronization routine. The job pulled employee data, filtered it by department, and fed it into the ERP system — standard stuff. Except it wasn’t standard. It was painfully slow.

Most engineers would have shrugged and accepted it. “It runs at night anyway,” they’d say. But I couldn’t let it go.

The Diagnosis Phase

I spent an evening analyzing the code. What I found was a masterclass in how not to write SQL:

• DataView.RowFilter evaluating each row individually
• Zero database indexes
• Each department update triggering its own separate SQL query

The culprit wasn’t a single bug — it was thousands of tiny inefficiencies stacked on top of each other.

Phase 1: The Quick Wins

I swapped DataView.RowFilter for DataTable.Select and added proper indexes. A simple change, but the effect was dramatic: 1.5 hours → 7 minutes.

That 99% improvement might have satisfied most people. Not me.

Phase 2: Architecture Thinking

The code still hit the database one transaction at a time. I introduced SqlBulkCopy for batch inserts and wrapped everything in a single transaction. The improvements kept compounding: 7 minutes → 42 seconds.

Each optimization taught me something new: that performance isn’t about one heroic fix — it’s about systematic thinking. It’s about asking the right questions: What’s actually slow? Why? Can we restructure it differently?

Phase 3: Rethinking the Foundation

The real breakthrough came when I stopped optimizing the job and started redesigning its purpose.

Instead of syncing all employee data every night, I created a SYNC table that tracked only changed records since the last run. Now the job only touched the data that actually mattered. The result: 42 seconds → 3 seconds.

The Real Lesson

That final jump from 42 seconds to 3 seconds wasn’t faster coding. It was smarter thinking — understanding that sometimes the best performance optimization isn’t optimization at all. It’s rethinking what the system actually needs to do.

From 1.5 hours to 3 seconds. A 99.94% improvement.

But the real reward wasn’t the metric. It was the realization that I’d found something I genuinely loved: finding what’s broken and making it sing.

Years later, when I built the first performance testing team at Jabil, assembled a global infrastructure for testing optimization, and helped clients save millions in cloud costs — I was still chasing that same high.

Because that’s what happens when you fall in love with the work. You don’t stop.

從 1.5 小時到 3 秒：我如何愛上了效能優化

繁體中文版 | Traditional Chinese Version

每位工程師的職涯裡，都有個讓「事情突然說得通」的時刻。我的時刻，始於盯著一個每天要跑 1.5 小時的 SQL 排程。

當時我在一家半導體封測公司維護人力資源系統。工作很簡單：抓員工資料、按部門篩選、匯入 ERP。很標準的東西。除了它快不了。

大多數工程師會聳肩接受。「反正跑在夜間，」他們會說。但我放不下。

診斷階段

我花了一個晚上分析這份程式碼。發現的東西，根本是「如何寫爛 SQL」的教科書範例：

• DataView.RowFilter 逐筆判斷每一列
• 零索引
• 每次部門異動都觸發獨立的 SQL Query

元兇不是單一的 Bug，而是數千個微小的無效率疊在一起。

第一階段：低垂的果實

我把 DataView.RowFilter 換成 DataTable.Select，加上適當的索引。簡單改動，效果卻戲劇性：1.5 小時 → 7 分鐘。

99% 的改善足以讓大多數人滿足。我不是大多數人。

第二階段：架構思維

程式碼仍然逐筆交易。我引入 SqlBulkCopy 做批次寫入，整個流程包在單一 Transaction 裡。改善持續疊加：7 分鐘 → 42 秒。

每個優化都教我一些新東西：效能不是靠一個英雄式的修復。是系統性思考。是不斷問對的問題：什麼真的慢？為什麼？我們能用不同方式重新設計它嗎？

第三階段：重新思考基礎

真正的突破，來自於我停止「優化排程」、改為「重新思考排程的目的」。

與其每晚同步所有員工資料，我建了一個 SYNC 表，只追蹤自上次執行後有異動的紀錄。現在排程只碰觸真正重要的資料。結果：42 秒 → 3 秒。

真正的教訓

從 42 秒跳到 3 秒，不是更快的程式碼。是更聰明的思維 — — 理解有時最好的效能優化，根本不是「優化」。而是重新思考系統真正需要做什麼。

1.5 小時到 3 秒。99.94% 的改善。

但真正的收穫不是指標。而是那個瞬間的領悟：我找到了我真正喜歡的東西 — — 找出什麼壞了，然後讓它唱歌。

多年後，當我在 Jabil 建立了第一個效能測試團隊、組建全球測試基礎設施、幫助客戶節省百萬美元雲端成本時 — — 我依舊在追逐那股熱情。

因為這就是愛上一件工作的後果。你不會停下。