ＣＪ軟體工程日誌

從零開始的大數據 SQL 優化： 用北風資料庫打造「零壓力分批轉檔」神級架構 在日常開發中，我們常常面臨需要從舊表撈取資料、經過一連串運算與 JOIN 後，再將結果生成一張實體報表提供給前端或長官檢視的需求。然而，當資料量突破百萬、甚至千萬等級，且資料庫充滿了歷史包袱與非正規化結構時，傳統的作法往往會引發嚴重的 「磁碟 I/O 暴走」 或 「共用 tempdb 撐爆」 的慘劇，進而收到資料庫管理員（DBA）的奪命連環叩。 今天這篇文章，我們將結合 SELECT TOP 0 INTO 的複製神技，搭配 WHILE 迴圈與「事後補建索引」的進階心法，利用經典的 北風資料庫 (Northwind) 當作戰場，手把手帶你建構出一套兼具 高吞吐量、零 tempdb 負擔、且具備斷線容錯能力 的終極大數據處理方案！ 一、 傳統作法與大數據瓶頸 面對大數據轉檔，初學者最常使用 SELECT INTO 或大型 CTE (Common Table Expression) 一口氣把資料全部灌進去。這種「一條 SQL 戰到底」的作法在小表運作良好，但遇到百萬級資料時，就會產生巨大的效能分水嶺： 暫存表 (#TempTable) 法 ：直接整批塞進暫存表，會將幾百萬筆資料塞滿全系統共用的 tempdb 空間，導致其他線上即時交易跟著集體卡死報錯。 純 CTE / 子查詢法 ：雖然避開了實體硬碟空間的消耗，但大量的資料串流在記憶體中反覆被多個 JOIN 呼叫時，會導致資料庫反覆重算，CPU 瞬間飆高至 100%。 【圖解：大量資料一次性寫入 vs 分批寫入的資料庫壓力對比】 [Image of Database table ingestion comparison showing monolithic query vs batching loops with tempdb lifecycle] 為了克服這些代價，高手工程師在實務上會採用 「分而治之 (Divide and Conquer)」 的策略：把一個會讓資料庫休克的大手術，拆解成數十個毫無負擔的微整形。這就是「分批處理...

📦 什麼是「產單號」？ 在資訊系統開發中，我們經常會聽到「產單號」這個術語。簡單來說，就是 批次產生序號 的過程。當企業與物流公司、金融機構或其他合作夥伴對接時，對方通常會配置一段序號區間，例如從 A 號到 B 號，而我們系統需要依照特定規則將這段區間內的所有序號都產生出來並存入資料庫，以供後續業務使用。 這些序號通常不是單純的連續數字，而是包含了 驗證機制 （如檢查碼、確認碼），以確保序號的正確性和防偽性。 🎯 實際業務場景 假設我們與某知名宅配公司合作，對方提供了以下序號配置規則： 📮 已為您配置單號區間：5,000 組 🟢 起始單號： 922117866191 🔴 終止單號： 922117916182 🔐 單號規則解析 這個單號系統採用了 12 碼 的結構設計： ▸ 前 11 碼： 主流水號，使用數字遞增方式產生 ▸ 第 12 碼： 確認碼（檢查碼），用於驗證單號正確性 💡 確認碼計算公式 確認碼 = 前11碼流水號 % 3 例如：92211786619 % 3 = 1 ，所以完整單號為 922117866191 這種設計方式很常見於物流、金融等需要高度資料正確性的產業。透過簡單的數學運算（取餘數），可以在資料傳輸或人工輸入時快速驗證單號是否正確，降低錯誤率。 💾 資料表結構設計 首先，我們需要建立一個資料表來儲存這些單號。以下是 SQL Server 的資料表定義...

    如題，近期公司導入 Oracle，導致程式碼需要調整，卻遇到當前環境查無資料！ 但窗口卻說權限有開了，所以紀錄下解決問題的方式。 ● 情境: 1. 窗口提供同義詞(Synonyms) 2. 因公司別不同，窗口也提供一個方法切換公司別，假設為 SET_COMPANYID_CONTEXT ● 解決思路: 1. 先詢問窗口他們使用了哪些NLS_PARAMETERS，通常會變的是 NLS_LANGUAGE，因為中文版更新頻率低，所以常使用美國版。 2. 再來就是更新當前環境的NLS_PARAMETERS，確認看看有無資料。 3. 最後就是在程式中提前設定好NLS_PARAMETERS。 Step1: 查看當前環境的 NLS_PARAMETERS -- 查詢出當前環境的 NLS_PARAMETERS SELECT * FROM V$NLS_PARAMETERS; -- 查詢出可使用的 VLS VALUE SELECT * FROM V$NLS_VALID_VALUES Step2: 當前環境執行語法，確認是否有資料 ALTER SESSION SET NLS_LANGUAGE='AMERICAN' Step3: 程式提前設定  NLS_PARAMETERS 重點是要在每個 connection 連接後，提前設定即可。 //... using (var sessionCommand = connection.CreateCommand()) { // 修改 NLS_LANGUAGE sessionCommand.CommandText = "ALTER SESSION SET NLS_LANGUAGE='AMERICAN'"; sessionCommand.ExecuteNonQuery(); // 修改 NLS_DATE_LANGUAGE (假設比對結果有差異就要加上) sessionCommand.CommandText = "ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'"; sessionCommand.ExecuteNo...

  Sql Server 常常會遇到 Lock 的問題，重點是要查出哪邊卡住，並且看如何處理，以下查詢方式。 SELECT r.session_id, r.status AS [指令狀態], r.command AS [指令類型], r.wait_time/1000.0 AS [等待時間(秒)], s.client_interface_name AS [連線資料庫的驅動程式], s.host_name AS [電腦名稱], s.program_name AS [執行程式名稱], t.text AS [執行的SQL語法], r.blocking_session_id AS [被鎖定卡住的session_id] FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE s.is_user_process = 1; 上圖可以看到被 session_id 79 卡住了，但重點是時間非常就，狀態是 KILLED/ROLLBACK，表示已經執行KILL了，還是卡住。這就需要重啟SQL Server了。 若是一般的執行，應該Kill就可以了。 參考資料: https://www.uuu.com.tw/Public/content/article/20/20201207.htm

如題，近期專案中的測試資料庫放在Azure，在資料轉置的途中，使用過幾種方式，其中最基本的就是使用 [匯出資料] 功能，較為麻煩一點的就是建立 DBLink，以下說明。 1.  [匯出資料]功能     1.1  對資料庫點擊右鍵選擇 [匯出資料]，並點擊下一步                    1.2   [選擇資料來源] ，就是你當前資料的存放位置，如下圖所示:     1.3  [選擇目的地] ，就是你要轉置過去的雲端DB，如下圖所示:       1.4   [選擇資料表] ，就是你要轉置過去的資料表，再按一直下一步即可，如下圖所示: ...

 如題，近期因為寫 Store Procedure 產生了一個錯誤 /*EXECUTE 之後的交易計數顯示遺漏了 COMMIT 或 ROLLBACK TRANSACTION 陳述式。前次計數 = 0，目前的計數 = 1。*/ 後續就發現特定 Table 無法搜尋，直覺就是應該被 Lock 住了，但是沒有特別處理過這問題，這次特別紀錄下。 以下開始 DB LOCK 的說明及解釋:      1.     模擬 Db Lock，只要有開啟 Transactoin 但是沒有 Commit 或 Rollback 就會產生 Dead Lock。               以下使用 北風資料庫為範例: BEGIN TRAN UPDATE dbo.Employees SET Country = 'JAPAN'      2.       此時 dbo.Employees 已經被 Lock 住了，可以用以下 SQL 查詢 SELECT request_session_id AS spid, resource_type AS rt, resource_databASe_id AS rdb, (CASE resource_type WHEN 'OBJECT' then object_name(resource_ASsociated_entity_id) WHEN 'DATABASE' then ' ' ELSE (SELECT object_name(object_id) FROM sys.partitions WHERE hobt_id = resource_ASsociated_entity_id) END) AS objname, resource_description AS rd, request_mode AS rm, request_status AS rs FROM sys.dm_tran_lock...

 如標題，在使用 C# 對 SQL Server 進行CRUD 時，發生此錯誤。 不廢話，直接附上我的情境及解法。 因為我在 IEnumerable 使用 ForEach() 中，進行 CRUD，此時推測內部的迭代器運作上對初始化交易的步驟，不如正常預期。 所以改為 for(...) { // CRUD }，在 for 中執行CRUD，就沒有所謂迭代器的問題，收工。

 如題所示，會遇到資料表中存起、迄時間，並且搜尋時要搜尋特定的區間，只要資料中有任一時間包含區間內就要找到，不廢話直接附上SQL。 ( StartDate BETWEEN @StartDate AND @EndDate OR EndDate BETWEEN @StartDate AND @EndDate OR @StartDate BETWEEN StartDate AND EndDate OR @EndDate BETWEEN StartDate AND EndDate )

之前專案的資料庫是放在 Azure 雲端，後來有個調整是要將下拉選單的選項依照名稱排序，那麼此時會遇到奇怪的情況： 基操的 ORDER BY Name 取得的資料竟然沒有照中文筆畫大小排序！！！ 這邊就直接附上解法： １）　什麼是定序： 決定資料庫所使用的字元集、排序的方式 ２）　先查詢目前資料庫的定序是什麼？ SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'))               我取得的定序是　 SQL_Latin1_General_CP1_CI_AS ，而若使用這個定序，就無法依照中文筆畫排序。                ＊ 可以參考 微軟的定序頁面 ３）　若是要照中文筆畫排序，則使用該定序　 chinese_taiwan_stroke_ci_as SELECT * FROM User ORDER BY Name chinese_taiwan_stroke_ci_as ４）　若是要照ㄅㄆㄇㄈ排序，則使用該定序　 Chinese_Taiwan_BOPOMOFO_CI_AI SELECT * FROM User ORDER BY Name Chinese_Taiwan_BOPOMOFO_CI_AI

●修改資料表的 Schema 從 MySchema 回復為 dbo ： ALTER SCHEMA MySchema TRANSFER dbo.MyTable ●修改資料表的 Schema 從 dbo 改為 MySchema ： ALTER SCHEMA dbo TRANSFER MySchema.MyTable 參考資料:  sql - How do I change db schema to dbo - Stack Overflow