Performance Improvement of Periodic Reports with Materialized Views on Oracle Database System

Raden Soepriadi, Gahara Dijerja, Samidi Samidi

Abstract


Periodic reports in Management Information Systems (MIS) play a crucial role in organizational decision-making. However, rapid data growth can degrade query performance. This study aims to enhance the performance of periodic reports in the State Revenue Collection Module (SIM MPN) of the Ministry of Finance by utilizing Materialized Views (MVs) in the Oracle database system. A dataset comprising 294,503,898 rows from 2021 to 2023 was used as a sample. An analysis was conducted on eight types of periodic report queries that traditionally relied on standard views. These queries were then converted into MVs to reduce execution time. Testing was performed by comparing execution times between views and MVs across 24 queries executed in two different environments. The results showed that the average execution time using views was 2,047,417 ms, whereas with MVs, execution times were reduced to 41 ms in the first test, 17 ms in the second, and 12 ms in the third. These findings confirm that MVs significantly improve the performance of periodic reports by accelerating query execution. The practical implication of this study is the recommendation to implement MVs in systems with large data volumes to optimize report access speed. Future research can focus on optimizing MV refresh times and further analyzing factors affecting execution time under various usage scenarios.

Keywords


execution time, materialized views, oracle database, periodic reports, management information system

Full Text:

PDF

References


M. R. Islam, “Impacts of Management Information System on Decision Making of the Organization,” Int. J. Bus. Soc. Sci. Res, vol. 6, no. 2, pp. 56–61, Mar. 2018, doi: 10.18034/gdeb.v8i2.100.

S. Hahn, J. Reineke, and R. Wilhelm, “Towards Compositionality in Execution Time Analysis-Definition and Challenges,” Association for Computing Machinery, vol. 12, no. 1, p. 28, Mar. 2015, doi: 10.1145/2752801.2752805.

H. Zhang, G. Chen, B. Chin Ooi, W.-F. Wong, S. Wu, and Y. Xia, “‘Anti-Caching’-based Elastic Memory Management for Big Data,” 2015. doi: 10.1109/ICDE.2015.7113375.

K. Bok, S. Yoo, D. Choi, J. Lim, and J. Yoo, “In-Memory Caching for Enhancing Subgraph Accessibility,” Applied Sciences (Switzerland), vol. 10, no. 16, pp. 1–18, Aug. 2020, doi: 10.3390/app10165507.

S. D. Choudari and R. Agrawal, “Optimization Design of Query Processing Performance using Appropriate Materialized View Selection & Preservation,” International Journal of Advanced Research in Computer and Communication Engineering, vol. 3, no. 12, pp. 8893–8896, Dec. 2014, doi: 10.17148/ijarcce.2014.31249.

R. Lumbantoruan, L. Siringoringo, E. Y. Sinaga, and E. Sitanggang, “Penerapan Materialized View yang berindeks pada Basis Data Studi Kasus: Online Library Information System Politeknik Del,” in National Conference of Information System, Surabaya: National Conference of Information System, Dec. 2010. [Online]. Available: https://www.researchgate.net/publication/260339724

N. M. Khushairi, N. A. Emran, and A. K. Menon, “Database Tuning using Oracle Materialized View for Manufacturing Industry,” International Journal of Computer Information Systems and Industrial Management Applications, vol. 10, pp. 38–46, 2018, [Online]. Available: www.mirlabs.net/ijcisim/index.html

Y. Ziya Ayık and F. Kahveci, “Materialized View Effect on Query Performance,” International Journal of Computer and Information Engineering, vol. 11, no. 9, 2017.

E. Witono and Parno, “Perbandingan Response Time Penggunaan Index, Views, dan Materialized Views Database MySQL,” Jurnal Sains Komputer & Informatika (J-SAKTI, vol. 6, no. 1, pp. 499–506, 2022, doi: 10.30645/j-sakti.v6i1.463.

F. Surya Nugraha, F. H. Purwanto, and F. E. N. Saputro, “Optimasi Query pada Laporan Transaksi Penjualan menggunakan Materialized View (Studi Kasus : Moonly café),” CCIT (Creative Communication and Innovative Technology) Journal, vol. 11, no. 1, pp. 1–14, 2018.

T. V. V. Kumar and K. Devi, “An Architectural Framework for Constructing Materialized Views in a Data Warehouse,” International Journal of Innovation, Management and Technology, 2013, doi: 10.7763/ijimt.2013.v4.390.

M. Manavi, “Multi-Objective Genetic Algorithm for Materialized View Optimization in Data Warehouses,” Interdisciplinary Conference on Electrics and Computer (INTCEC 2024), Jun. 2024.

J. Prakash and T. V. V. Kumar, “A Multi-Objective Approach for Materialized View Selection,” International Journal of Operations Research and Information Systems, vol. 10, no. 2, pp. 1–19, 2019, doi: 10.4018/IJORIS.2019040101.

A. Gosain and K. Sachdeva, “Materialized View Selection for Query Performance Enhancement using Stochastic Ranking based Cuckoo Search Algorithm,” International Journal of Reliability, Quality and Safety Engineering, vol. 27, no. 3, Jun. 2020, doi: 10.1142/S0218539320500084.

N. Emran, “Database Performance Tuning Methods for Manufacturing Execution System,” World Appl SCI J, vol. 30, Jan. 2014, doi: 10.5829/idosi.wasj.2014.30.icmrp.14.

A. Kour, “Data Warehousing, Data Mining, OLAP and OLTP Technologies are Indispensable Elements to Support Decision-Making Process in Industrial World,” International Journal of Scientific and Research Publications, vol. 5, no. 5, May 2015, [Online]. Available: www.ijsrp.org

A. Turnip et al., “Backend Design of Web-based ECG Signal Monitoring System,” Scitepress, Mar. 2021, pp. 317–324. doi: 10.5220/0010371403170324.

M. H. Moghadam, M. Saadatmand, M. Borg, M. Bohlin, and B. Lisper, “Learning-based Response Time Analysis in Real-Time Embedded Systems: A Simulation-based Approach,” in Proceedings - International Conference on Software Engineering, IEEE Computer Society, May 2018, pp. 21–24. doi: 10.1145/3194095.3194097.

R. Elmasri and S. B. Navathe, Database Systems SEVENTH EDITION. 2016.

A. Mishra et al., “Accelerating Analytics with Dynamic In-Memory Expressions,” Sep. 2016. doi: 10.14778/3007263.3007280.

S. Shoyab and V. Sriharsha, “Real-Time Fault-Tolerant Analytics using Distributed Database in-Memory,” International Journal of Research, vol. 5, no. 17, Jul. 2018, doi: 10.1109/ICDE.2016.7498333.

N. Mukherjee et al., “Distributed Architecture of Oracle Database in-memory,” Proceedings of the VLDB Endowment, vol. 8, no. 12, Aug. 2015, doi: 10.14778/2824032.2824061.




DOI: https://doi.org/10.32520/stmsi.v14i2.4910

Article Metrics

Abstract view : 160 times
PDF - 38 times

Refbacks

  • There are currently no refbacks.


Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.