Optimizing Business Logic Execution: The Role of Stored Procedures and Functions in SQL-Based Systems
Keywords:
SQL indexing methods, Runtime performance, Storage requirements, Clustered and Non-Clustered indexing, Integer vs. string data and SQL vs. Python data handlingAbstract
This study compares the storage needs and runtime performance of SQL-based systems with various indexing strategies, including None, Clustered, Non-Clustered, and Both. It focuses on integer and string data types. The results show that integer data typically has lower storage requirements and runs faster, whereas string data has a higher storage overhead. When it comes to string data in particular, the "Both" indexing strategy—which combines Clustered and Non-Clustered—offers efficient data retrieval but uses the most storage space. When compared to SQL indexing, Python-based systems may provide more versatile in-memory data management, which could optimize specific operations without using as much disk space. Nevertheless, SQL's indexing features make it superior for sophisticated queries on big datasets. Insights into the benefits and drawbacks of SQL-based indexing are offered by this analysis, which helps in making application-specific decisions about data storage and access.
Downloads
References
Aguilar-Savén, Ruth Sara. "Business process modeling: Review and framework." International Journal of Production Economics 90, no. 2 (2004): 129-149.
Fabra, Javier, Valeria De Castro, Pedro Álvarez, and Esperanza Marcos. "Automatic execution of business process models: Exploiting the benefits of model-driven engineering approaches." Journal of Systems and Software 85, no. 3 (2012): 607-625.
Ly, L. T., Rinderle-Ma, S., Knuplesch, D., & Dadam, P. (2011). Monitoring business process compliance using compliance rule graphs. In On the Move to Meaningful Internet Systems: OTM 2011: Confederated International Conferences: CoopIS, DOA-SVI, and ODBASE 2011, Hersonissos, Crete, Greece, October 17-21, 2011, Proceedings, Part I (pp. 82-99). Springer Berlin Heidelberg.
Van Der Aalst, Wil MP. Business process management demystified: A tutorial on models, systems and standards for workflow management. Springer Berlin Heidelberg, 2004.
Ter Hofstede, Arthur, Wil MP van der Aalst, Arthur HM ter Hofstede, and Mathias Weske. "Business process management: A survey." In Business Process Management: International Conference, BPM 2003 Eindhoven, The Netherlands, June 26–27, 2003 Proceedings 1, pp. 1-12. Springer Berlin Heidelberg, 2003.
Van Der Aalst, Wil, and Kees Max Van Hee. Workflow management: models, methods, and systems. MIT Press, 2004.
Babu, A. J. G., and Nalina Suresh. "Project management with time, cost, and quality considerations." European Journal of Operational Research 88, no. 2 (1996): 320-327.
Ghodsypour, Seyed Hassan, and Christopher O'Brien. "A decision support system for supplier selection using an integrated analytic hierarchy process and linear programming." International journal of production economics 56 (1998): 199-212.
Van Der Aalst, Wil MP, Hajo A. Reijers, Anton JMM Weijters, Boudewijn F. van Dongen, AK Alves De Medeiros, Minseok Song, and H. M. W. Verbeek. "Business process mining: An industrial application." Information Systems 32, no. 5 (2007): 713-732.
Engelberg, Gal, Moshe Hadad, and Pnina Soffer. "From network traffic data to business activities: a process mining driven conceptualization." In International Conference on Business Process Modeling, Development and Support, pp. 3-18. Cham: Springer International Publishing, 2021.
Alwan, Zainab S., and Manal F. Younis. "Detection and prevention of SQL injection attack: a survey." International Journal of Computer Science and Mobile Computing 6, no. 8 (2017): 5-17.
Sendiang, Maksy, Anritsu Polii, and Jusuf Mappadang. "Minimization of SQL injection in scheduling application development." In 2016 International conference on knowledge creation and intelligent computing (KCIC), pp. 14-20. IEEE, 2016.
Chapke, Dhavan, Kalyani Akant, and Pankaj Chandankhede. "Strategic Approaches to Modern Data Management Leveraging Relational Database Systems." In 2024 International Conference on Inventive Computation Technologies (ICICT), pp. 353-358. IEEE, 2024.
Olabanji, Samuel Oladiipo. "Advancing cloud technology security: Leveraging high-level coding languages like Python and SQL for strengthening security systems and automating top control processes." Journal of Scientific Research and Reports 29, no. 9 (2023): 42-54.
Karras, Aristeidis, Christos Karras, Antonios Pervanas, Spyros Sioutas, and Christos Zaroliagis. "SQL query optimization in distributed NoSQL databases for cloud-based applications." In International Symposium on Algorithmic Aspects of Cloud Computing, pp. 21-41. Cham: Springer International Publishing, 2022.
Torres-Jimenez, Jose, Nelson Rangel-Valdez, Miguel De-la-Torre, and Himer Avila-George. "An Approach to Aid Decision-Making by Solving Complex Optimization Problems Using SQL Queries." Applied Sciences 12, no. 9 (2022): 4569.
Shantharajah, S. P., and E. Maruthavani. "A survey on challenges in transforming No-SQL data to SQL data and storing in cloud storage based on user requirement." International Journal of Performability Engineering 17, no. 8 (2021): 703.
Schönig, Stefan, Claudio Di Ciccio, and Jan Mendling. "Configuring SQL-based process mining for performance and storage optimization." In Proceedings of the 34th ACM/SIGAPP Symposium on Applied Computing, pp. 94-97. 2019.
Begoli, Edmon, Tyler Akidau, Fabian Hueske, Julian Hyde, Kathryn Knight, and Kenneth Knowles. "One SQL to rule them all efficient and syntactically idiomatic approach to the management of streams and tables." In Proceedings of the 2019 International Conference on Management of Data, pp. 1757-1772. 2019.
Idhaim, Hasan Ali. "Selecting and tuning the optimal query form of different SQL commands." International Journal of Business Information Systems 30, no. 1 (2019): 1-12.
D'silva, Joseph Vinish, Florestan De Moor, and Bettina Kemme. "Keep your host language object and also query it: A case for SQL query support in RDBMS for host language objects." In Proceedings of the 31st International Conference on Scientific and Statistical Database Management, pp. 133-144. 2019.
Baldacci, Lorenzo, and Matteo Golfarelli. "A cost model for SPARK SQL." IEEE Transactions on Knowledge and Data Engineering 31, no. 5 (2018): 819-832.
Giannakouris, Victor, Nikolaos Papailiou, Dimitrios Tsoumakos, and Nectarios Koziris. "MuSQLE: Distributed SQL query execution over multiple engine environments." In 2016 IEEE International Conference on Big Data (Big Data), pp. 452-461. IEEE, 2016.
Kolev, Boyan, Patrick Valduriez, Carlyna Bondiombouy, Ricardo Jiménez-Peris, Raquel Pau, and José Pereira. "CloudMdsQL: querying heterogeneous cloud data stores with a common language." Distributed and parallel databases 34 (2016): 463-503.
Krause, Christian, Daniel Johannsen, Radwan Deeb, Kai-Uwe Sattler, David Knacker, and Anton Niadzelka. "An SQL-based query language and engine for graph pattern matching." In Graph Transformation: 9th International Conference, ICGT 2016, in Memory of Hartmut Ehrig, Held as Part of STAF 2016, Vienna, Austria, July 5-6, 2016, Proceedings 9, pp. 153-169. Springer International Publishing, 2016.
Braun, Lucas, Thomas Etter, Georgios Gasparis, Martin Kaufmann, Donald Kossmann, Daniel Widmer, Aharon Avitzur, Anthony Iliopoulos, Eliezer Levy, and Ning Liang. "Analytics in motion: High-performance event-processing and real-time analytics in the same database." In Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data, pp. 251-264. 2015.
Wang, Yue, Yingzhong Xu, Yue Liu, Jian Chen, and Songlin Hu. "QMapper for smart grid: Migrating SQL-based application to Hive." In Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data, pp. 647-658. 2015.
Woods, Louis, Zsolt István, and Gustavo Alonso. "Ibex: An intelligent storage engine with support for advanced SQL offloading." Proceedings of the VLDB Endowment 7, no. 11 (2014): 963-974.
Wang, Tieniu, Jianhua Hu, and Haihe Zhou. "Design and implementation of an ETL approach in business intelligence project." In Practical Applications of Intelligent Systems: Proceedings of the Sixth International Conference on Intelligent Systems and Knowledge Engineering, Shanghai, China, Dec 2011 (ISKE2011), pp. 281-286. Springer Berlin Heidelberg, 2012.
Sbaa, Ahmed, Rachid El Bejjet, and Hicham Medromi. "An SMS-SQL based On-board system to manage and query a database." International Journal of Advanced Computer Science and Applications 3, no. 6 (2012).
Mozafari, Barzan, Kai Zeng, and Carlo Zaniolo. "From regular expressions to nested words: Unifying languages and query execution for relational and XML sequences." Proceedings of the VLDB Endowment 3, no. 1-2 (2010): 150-161.
Chen, Qiming, and Meichun Hsu. "Cooperating SQL dataflow processes for In-DB analytics." In On the Move to Meaningful Internet Systems: OTM 2009: Confederated International Conferences, CoopIS, DOA, IS, and ODBASE 2009, Vilamoura, Portugal, November 1-6, 2009, Proceedings, Part I, pp. 389-397. Springer Berlin Heidelberg, 2009.
Katircioglu, Kaan, Timothy M. Brown, and Mateen Asghar. "An SQL-based cost-effective inventory optimization solution." IBM Journal of Research and Development 51, no. 3.4 (2007): 433-445.
Gunnulfsen, Michael. "Scalable and efficient web application architectures: Thin-clients and SQL vs. thick-clients and NoSQL." Master's thesis, 2013.
Epiphaniou, Gregory, Prashant Pillai, Mirko Bottarelli, Haider Al-Khateeb, Mohammad Hammoudesh, and Carsten Maple. "Electronic regulation of data sharing and processing using smart ledger technologies for supply-chain security." IEEE Transactions on Engineering Management 67, no. 4 (2020): 1059-1073.
Crnkovic, Ivica, Ulf Asklund, and Annita Persson Dahlqvist. Implementing and integrating product data management and software configuration management. Artech House, 2003.
Downloads
Published
How to Cite
Issue
Section
License
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
All papers should be submitted electronically. All submitted manuscripts must be original work that is not under submission at another journal or under consideration for publication in another form, such as a monograph or chapter of a book. Authors of submitted papers are obligated not to submit their paper for publication elsewhere until an editorial decision is rendered on their submission. Further, authors of accepted papers are prohibited from publishing the results in other publications that appear before the paper is published in the Journal unless they receive approval for doing so from the Editor-In-Chief.
IJISAE open access articles are licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. This license lets the audience to give appropriate credit, provide a link to the license, and indicate if changes were made and if they remix, transform, or build upon the material, they must distribute contributions under the same license as the original.
