113

1,450 千元

風險管控-風險管控

2024/07/23

2024/09/30

周延華

化學署評估管理組

簡佳裕

聯合通商電子商務股份有限公司

其他

資料庫、資料庫排程程式、資料轉置

Database, Scheduled Job, Data Transformation

楊榮興

賈萍萍

25462508

di.chia@ebizprise.com

本文件為「跨部會化學物質彙整系統架構優化計畫」之成果報告。本計畫主要是透過原始碼審查瞭解現有系統設計的方法以及程式語言的運用與設計方式，同時也經由檢視資料庫設計等方法找出「化學雲」效率可以在未來優化改善的瓶頸點。其中程式部份以定時啟動相關運作的排程與資料轉置程式，負責與外部其他系統交換資料或是產出快報應用程式介面 (Application Programming Interface, API) 程式，以及網頁程式裡讀取資料的結構化查詢語言 (Structured Query Language, SQL) 語法做審查。 這些程式主要的問題在： （一） 選用一些較佔記憶體的方式去設計程式。 （二） 大量運用同步的方式進行處理，導致系統同一時間只能執行特定的工作，也就是執行緒堵塞。 （三） 大量重複程式碼，導致系統運作資源的浪費。 資料庫的設計則有以下幾點主要的問題： （一） 許多資料表 (Table) 並沒有建立適當的索引 (Index) 或沒有建立索引。 （二） 沒有建立主鍵 (Primary Key, PK)。 本成果報告針對這些影響系統效能的瓶頸點一一提出個別的優化建議，並規劃出未來執行程式與資料庫優化的時程建議。而除了直接對於瓶頸點提出優化的方式外，也會提出「化學雲」系統在平台技術面、機制面、以及發展面三個面向在未來的優化策略的建議。

資料庫、資料庫排程程式、資料轉置

113DE002_成果報告(定稿版).pdf

1MB

113DE002_成果報告(定稿版)

2024/11/30 上午 12:00:00

This document is the results report of the "Cross-Departmental Chemical Substance Collection Computer System Architecture Optimization Plan". This project is mainly to understand the system design of the use and design of programming language through source code review and the design of the database to find out the execution efficiency of the "Chemical Cloud" computer software system and to locate the bottleneck for future optimization and improvement. The program part reviews the data transformation by scheduled operations, the API (Application Programming Interface) program responsible for exchanging data with external systems or generating express reports for external system, and the SQL (Structured Query Language) script syntax for accessing data in web programs. The main problems with these programs are: 1. Program design by using some methods that usually take up more memory. When system resources are not used effectively, execution efficiency will be affected. 2. Synchronous processing is used in multiple places, causing the system to only perform specific task at one time that would naturally affects the overall execution efficiency. 3. A large number of duplicate codes lead to a waste of system operation resources. The design of the database has the following main issues: 1. Many data tables are not properlly indexed or no index at all. 2. Failure to create a PK or too many primary keys would prevent the program from achieving better operating efficiency when it accessing the database. This final report will explain how these bottlenecks affect performance, and further provide suggestion for improving and optimizing the execution efficiency of the “Chemical Cloud” system.At the same time this report will propose suggestions for future oprimization strategies in terms of platform technology, mechanism, and development.

Database, Scheduled Job, Data Transformation