Improving Speed Performance of Select Random Query in SQL Database
DOI:
https://doi.org/10.31963/intek.v7i1.1536Keywords:
SQL database, select random, SQL query speed, SQL optimization, relational database, data manipulation languageAbstract
Select random is a query in a SQL database that can retrieve data randomly from a table. Select random is often used to present data in various applications such as websites, data mining and others. Unfortunately, ordinary select random query is inefficient in terms of processing time if used in large table. This paper, tries to solve this problem by proposing two optimized methods of select random query, namely the Small Percentage Order by Rand (SPO-Rand) and the Filtered Column Order by Rand (FCO-Rand). The two proposed methods are then compared in terms of processing speed with a standard Select Random query or Normal Order by Rand (NO-Rand). The scenario of the experiment is to collect five random data from several data sets, ranging from 10.000 to 200.000 data. Based on the results of experiments that have been conducted, the proposed FCO-Rand method obtained the best process speed with 0.074 seconds at 200.000 data, followed by SPO-Rand with 0.265 seconds. These result are much faster than the standard random select method (NO-Rand) which takes up to 7,035 seconds for the same task.References
N. Sangeeth and R. Rejimoan, “An intelligent system for information extraction from relational database using HMM,†International Conference on Soft Computing Techniques and Implementations, ICSCTI 2015, pp. 14–17, 2015.
V. K. Myalapalli and P. R. Savarapu, “High Performance SQL Finesse for Lucrative Programming,†Annual IEEE India Conference (INDICON), 2014.
J. L. Viescas, D. S. Steele, and B. G. Clothier, Effective SQL: 61 Specific Ways to Write Better SQL. Addison-Wesley, 2017.
D. Saisanguansat and P. Jeatrakul, “Improving optimization performance on PL/SQL,†International Conference on ICT and Knowledge Engineering, pp. 1–6, 2017.
M. N. Y. Utomo, A. E. Permanasari, E. Tungadi, and I. Syamsuddin, “Determining Single Tuition Fee of Higher Education in Indonesia: A Comparative Analysis of Data Mining Classification Algorithms,†in Proceedings of 2017 4th International Conference on New Media Studies, CONMEDIA 2017, pp. 113–117, 2017.
S. Minukhin, V. Fedko, and D. Sitnikov, “SQL-On-Hadoop Systems: Evaluting Performance of Polybase for Big Data Processing,†International Scientific-Practical Conference Problems of Infocommunications. Science and Technology (PIC S&T), no. 1, pp. 591–594, 2018.
J. Habimana, “Query Optimization Techniques - Tips For Writing Efficient And Faster SQL Queries,†International Journal of Scientific & Technology Research, vol. 4, no. 10, pp. 22–26, 2015.
H. Halimi and I. Jound, “Comparison of performance between Raw SQL and Eloquent ORM in Laravel,†Blekinge Institute of Technology, 2016.
N. Sangeeth and M. Tech, “An Intelligent System For Information Extraction From Relational Database Using HMM,†International Conference on Soft Computing Techniques and Implementations, pp. 14–17, 2015.
V. Leis, K. Kundhikanjana, A. Kemper, and T. Neumann, “Efficient Processing of Window Functions in Analytical SQL Queries,†Proceedings of the VLDB Endowment, vol. 8, no. 10, pp. 1058–1069, 2015.
F. Mithani, S. Machchhar, and F. Jasdanwala, “A Novel Approach for SQL Query Optimization,†International Conference on Computational Intelligence and Computing Research (ICCIC), 2016.
