Abstract— In manufacturing industry where data are produced and shared every day, data volumes could be large enough for the database performance to become an issue. Manufacturing Execution System (MES) is such a system that cannot tolerate with poor database performance as the system relies heavily on real-time reporting that requires instance query responses. Manufacturing products’ quality and production targets can be affected as the result of delayed queries. Therefore, the need to maintain the acceptable level of database performance in this domain is crucial. One task in maintaining database performance is identification and diagnosis of the root causes that may cause delayed queries. Poor query design has been identified as one major cause of delayed queries that affect real-time reporting. Nevertheless, as various methods available to deal with poor query design, it is important for a database administrator to decide the method or combination of methods that work best. In this paper, we present a case study on the methods used by a real manufacturing industry company called as Silterra and the methods proposed in the literature that deal with poor query design. For each method, we elicit its strength and weaknesses and analyse the practical implementation of it.
In modernization of technological era, the amount of data has been exploding in many application domains such as healthcare, public sector, retail and manufacturing. For example, in healthcare, electronic medical records consist of patient’s family history, illnesses and treatment, which able to improve preventive care and disease treatment. In some cases, doctors take images with machines like a computerized axial tomography (CAT) or magnetic resonance imaging (MRI). These images can help map out patient’s problems and help save lives. The needs to retain long-term active data or even permanent are increasing . Data are accumulated and transformed to big dataset before they can be stored in a database. Big datasets can cause overhead to Database Management System (DBMS) and lead to database performance issues . Database performance is a crucial issue, which can decrease the ability of the DBMS to respond to queries quickly.
Poor database performance cause negative consequences such as in financial, productivity and quality of the businesses in many application domains. In this research, we will focus on database performance issue in manufacturing domain. This research will base on a real case study in a semiconductor fabrication factory, Silterra Malaysia Sdn Bhd.
Silterra, like many other semiconductor manufacturing companies, is known to be the one of the most complex manufacturing operations. Most of the processes are delicate, for example the process of dicing the wafers must be carefully monitored, as the wafers are thin and fragile. Even a tiny scratch may scrap the wafer. These delicate processes require close monitoring, which is beyond human’s capability. In addition, processing of 40,000 to 50,000 work-in-progresses (WIP) usually takes 50 to 70 days, 300 to 400 equipments and 300 to 900 steps to complete [3,4]. Manufacturing Execution System (MES) is used to manage WIP, equipment automation, material control system (MCS) routing and material transfer within an automated material handling systems (AHMS). Huge amount of data are recorded automatically or semi automatically in multiple databases during fabrication process where the data become the input to monitor lot movements in the semiconductor fabrication plant, or shortly FAB. These data will be retrieved in timely manner to produce meaningful reports.
MES database composed of a collection of subsystems, each with a specific task that will interact with the user application programs and the database as shown in Fig.1. Complexities of the processes in FAB industry contribute to huge database because every single transaction needs to be recorded. Wafer transactions are characterized by a diverse product mix, re-entrant process flow, different processes types and different disruption . As a result, processing overhead that must be dealt by Database Management System (DBMS) lead to database performance issues .