Optimasi Performa Query Subsidi Debitur dengan Index and Table Partition, Subquery and Indexing, dan Parallel Query Execution
DOI:
https://doi.org/10.52436/1.jpti.824Keywords:
index and table partition, optimasi query, oracle database, parallel query execution, pengolahan data besar, subquery and indexingAbstract
Pertumbuhan data yang pesat menyebabkan turunnya performa perhitungan subsidi debitur pada Sistem Informasi Kredit Program (SIKP). Waktu proses selama 8 jam membuat layanan SIKP terganggu karena pemangku kebijakan tidak dapat mencetak laporan. Penelitian ini bertujuan untuk mengoptimalkan perhitungan subsidi debitur dengan metode optimasi query. Metode yang akan digunakan mencakup Index and Table Partition Subquery and Indexing, dan Parallel Query Execution. Pengujian dilakukan secara eksperimen menggunakan 300 juta sampel data subsidi debitur Bank Rakyat Indonesia (BRI) tahun 2022. Hasil penelitian menunjukan bahwa sebelum menggunakan metode optimasi, sistem membutuhkan waktu rata-rata 7.992,34 detik untuk menyelesaikan 1 siklus perhitungan data sampel. Index and Table Partition menghasilkan waktu rata-rata 7.097,01 detik. Subquery and Indexing menghasilkan waktu rata-rata 3.270,33 detik. Parallel Query Execution menghasilkan waktu rata-rata 5.923,67 detik. Hasil optimal diperoleh ketika ketiga metode dikombinasikan, menghasilkan waktu rata-rata 3.038 detik, yaitu 61,98% lebih efisien dibandingkan metode yang ada. Penerapan metode optimasi query ini secara langsung dapat meningkatkan efisiensi SIKP dan operasional pemangku kebijakan tidak terganggu. Hasil penelitian ini memberikan solusi optimasi query kombinasi untuk pengolahan data besar.
Downloads
References
Kementerian Keuangan, “Peraturan Menteri Keuangan Nomor 12 Tahun 2024 tentang Pedoman Penggunaan Sistem Informasi Kredit Program,” Jakarta, Feb. 2024. Accessed: Mar. 19, 2025. [Online]. Available: https://peraturan.bpk.go.id/Download/338733/2024pmkeuangan012.pdf.pdf
Mb. Hartanto, T. Muhammad Fawa, and D. P. Eko Hendro, “ANALISA KINERJA DATABASE DAN IMPLEMENTASI CACHE REDIS PADA WEB SERVICE LUMEN,” Bandar Lampung, Oct. 2023. Accessed: Mar. 19, 2025. [Online]. Available: https://jurnal.umitra.ac.id/index.php/altek/article/view/1429/pdf
M. Sughaturu Krishnappa, B. Mohan Harve, V. Jayaram, A. Nagpal, K. Kumar Ganeeb, and B. Shesharao Ingole, “ORACLE 19C SHARDING: A COMPREHENSIVE GUIDE TO MODERN DATA DISTRIBUTION,” International Journal of Computer Engineering and Technology (IJCET), vol. 15, no. 5, pp. 15–20, Sep. 2024, doi: 10.5281/zenodo.13880818.
M. M. Rahman, S. Islam, M. Kamruzzaman, and Z. H. Joy, “ADVANCED QUERY OPTIMIZATION IN SQL DATABASES FOR REAL-TIME BIG DATA ANALYTICS,” ACADEMIC JOURNAL ON BUSINESS ADMINISTRATION, INNOVATION & SUSTAINABILITY, vol. 4, no. 3, pp. 1-1–14, Jun. 2024, doi: 10.69593/ajbais.v4i3.77.
S. Maesaroh, H. Gunawan, A. Lestari, M. S. A. Tsaurie, and M. Fauji, “Query Optimization In MySQL Database Using Index,” International Journal of Cyber and IT Service Management, vol. 2, no. 2, pp. 104–110, Mar. 2022, doi: 10.34306/ijcitsm.v2i2.84.
E. Witono, “Perbandingan Response Time Penggunaan Index, Views, dan Materialized Views Database Mysql,” Jakarta, Mar. 2022. doi: http://dx.doi.org/10.30645/j-sakti.v6i1.463.
Samidi, Fadly, Y. Virmansyah, R. Yulyanto Suladi, and A. Bambang Lesmana, “Optimasi Database dengan Metode Index dan Partisi Tabel Database Postgresql pada Aplikasi E-Commerce. Studi pada Aplikasi Tokopintar,” Jakarta, Apr. 2022. doi: https://doi.org/10.31004/jptam.v6i1.3257.
M. Samsul Anwar and N. F. Rozi, “Optimisasi Performa Akses Data dalam Grafana Menggunakan Indeks B-Tree MySQL,” Prosiding Seminar Implementasi Teknologi Informasi dan Komunikasi, vol. 3, no. 2, pp. 286–292, Jul. 2024, doi: 10.31284/p.semtik.2024-2.6211.
M. Kumar, T. K. Gupta, and D. U. Sarwe, “An Optimization of Bitmap Index Compression Technique in Bulk Data Movement Infrastructure,” IOP Conf Ser Mater Sci Eng, vol. 1099, no. 1, p. 012074, Mar. 2021, doi: 10.1088/1757-899X/1099/1/012074.
W. Grivin Mokodaser and M. Dwijayanti, “Implementasi Metode Indexing dan Penggunaan Subquery untuk Optimalisasi Database Rawat Jalan Rumah Sakit Menggunakan Mysql,” Cogito Smart Journal, vol. 8, no. 2, pp. 335–345, Dec. 2022, doi: 10.31154/cogito.v8i2.415.335-345.
J. Zhao, H. Zhang, and Y. Gao, “Efficient Query Re-optimization with Judicious Subquery Selections,” Proceedings of the ACM on Management of Data, vol. 1, no. 2, pp. 1–26, Jun. 2023, doi: 10.1145/3589330.
S. A. Rismawati, N. Zahira Ramadhani, E. T. Aswuri, and S. Mukaromah, “Prosiding Seminar Nasional Teknologi dan Sistem Informasi (SITASI) 2023 Surabaya,” Surabaya, Sep. 2023. doi: https://doi.org/10.33005/sitasi.v3i1.395.
R. Bagus, B. Sumantri, G. Subari, F. Mahardika, and H. Jayusman, “PERBANDINGAN EFISIENSI WAKTU PROSES PENGAKSESAN DATA ANTARA QUERY BERBENTUK JOIN DENGAN SUBSELECT,” Jurnal Manajemen Informatika & Komputerisasi Akuntansi, vol. 7, no. 1, pp. 25–33, Apr. 2023, doi: 10.46880/jmika.Vol7No1.pp25-33.
M. Nuriev, R. Zaripova, A. Potapov, and M. Kuznetsov, “Achieving new SQL query performance levels through parallel execution in SQL Server,” in E3S Web of Conferences, Kazan: EDP Sciences, Dec. 2023, pp. 7–14. doi: 10.1051/e3sconf/202346004005.
M. Ilba, “Parallel algorithm for improving the performance of spatial queries in SQL: The use cases of SQLite/SpatiaLite and PostgreSQL/PostGIS databases,” Science Direct, vol. 155, Oct. 2021, doi: https://doi.org/10.1016/j.cageo.2021.104840.
H. Zhang, D. G. Andersen, A. Pavlo, M. Kaminsky, L. Ma, and R. Shen, “Reducing the storage overhead of main-memory OLTP databases with hybrid indexes,” in Proceedings of the ACM SIGMOD International Conference on Management of Data, San Francisco: Association for Computing Machinery, Jun. 2016, pp. 1567–1581. doi: 10.1145/2882903.2915222.
Kementerian Koordinator Bidang Perekonomian, “Peraturan Menteri Koordinator Bidang Perekonomian Nomor 1 Tahun 2022 Tentang Pedoman Pelaksanaan Kredit Usaha Rakyat,” 2023.
A. Silberschatz, H. F. Korth, and S. Sudarshan, Database System Concepts, 6th ed. New York: McGraw-Hill Education, 2010.
Q. Zhang, X. Gong, H. A. Khan, J. Wei, and Y. Ren, “A Novel Index-Organized Storage Model for Hybrid DRAM-PM Main Memory Database Systems,” Nov. 18, 2024, Shanghai. doi: 10.21203/rs.3.rs-5286510/v1.
A. Murugan and J. Vijayalakshmi, “Detecting Multi-Block Double Spent Transaction Based On B-Tree Indexing,” INTERNATIONAL JOURNAL OF SCIENTIFIC & TECHNOLOGY RESEARCH, vol. 9, p. 2, Feb. 2020, [Online]. Available: www.ijstr.org
H. Roh, W.-C. Kim, S. Kim, and S. Park, “A B-Tree index extension to enhance response time and the life cycle of flash memory,” sciencedirect, vol. 179, no. 18, pp. 3136–3161, Aug. 2009.
R. Bayer, “The universal B-tree for multidimensional indexing: General concepts,” Springer, vol. 1274, Jul. 2005.
Z. Jalilibal, A. Amiri, P. Castagliola, and M. B.C. Khoo, “Monitoring the coefficient of variation: A literature review,” Science Direct, vol. 161, Nov. 2021.
Microsoft Learn, “Subqueries (SQL Server),” 2024. Accessed: Mar. 21, 2025. [Online]. Available: https://learn.microsoft.com/id-id/sql/relational-databases/performance/subqueries?view=sql-server-ver16
G. Fritchey, “SQL Query Performance Tuning,” Research Gate, pp. 1–15, Aug. 2014.
T. Taipalus, “The effects of database complexity on SQL query formulation,” Science Direct, vol. 165, Jul. 2020.
Oracle Corporation, “Parallel Execution Fundamentals,” 2009, Oracle Corporation.
Y. Wang, M. Li, H. Dai, K. B.Kent, K. Ye, and C. Xu, “Deadlock Avoidance Algorithms for Recursion-Tree Modeled Requests in Parallel Executions,” EEE Transactions on Computers, vol. 71, no. 9, pp. 2073–2087, Sep. 2022.
