INTELLIGENT ANALYSIS OF PERFORMANCE RESULTS BASED ON OBJECT-RELATIONAL MAPPING STRATEGIES AND FOREIGN KEY CONSTRAINTS IN SQL DATABASES

Oleksandra Rybaсk, Oleh Husak, Roman Mysiuk

Abstract


Background. The rapid expansion of data-driven applications has increased the importance of efficient query execution in relational database systems, where even minor inefficiencies can significantly affect overall performance. Although Object-Relational Mapping (ORM) frameworks simplify development and improve maintainability, their abstraction layer can introduce measurable overhead, and the impact of foreign key constraints on execution speed remains a practical concern, particularly in microservice architectures that follow the “Database per Service” principle.

Materials and Methods. An experimental information system is developed using a relational database and the SQLAlchemy ORM framework, with a schema that includes one-to-one, one-to-many, and many-to-many relationships tested both with and without foreign key constraints. Three representative queries retrieving booking details, aggregating related records, and calculating total payments are executed using raw SQL and ORM approaches, while an intelligent algorithm analyzed performance, detected potential N+1 query risks, and recommended optimal strategies such as explicit JOINs.

Results and Discussion. Raw SQL consistently demonstrated superior performance across all scenarios. The most significant disparity occurred in ORM implementations affected by the N+1 problem, where execution time exceeded that of equivalent SQL queries by more than an order of magnitude. Aggregation queries showed smaller yet consistent overhead. The presence or absence of foreign key constraints had a negligible influence on raw SQL performance, with differences remaining within experimental variance. Explicit JOIN usage in ORM substantially reduced overhead compared to implicit relationship navigation. The intelligent analysis accurately predicted high-risk queries and provided effective strategy recommendations, confirmed by empirical results.

Conclusion. ORM frameworks improve productivity and maintainability but introduce measurable overhead. Raw SQL remains preferable for performance-critical tasks, while foreign key constraints do not significantly degrade execution speed. Intelligent performance analysis supports balanced decisions between efficiency and maintainability in complex relational systems.

Keywords: relational databases, SQL performance, ORM, decision support system, intelligent analysis, database design


Full Text:

PDF

References


[1] Turcotte, A., Aldrich, M. W., & Tip, F. (2023). Reformulator: Automated refactoring of the N+1 problem in database-backed applications. Proceedings of the 37th IEEE/ACM International Conference on Automated Software Engineering (ASE '22), Article 84, 1–12. Association for Computing Machinery. https://doi.org/10.1145/3551349.3556911

[2] Yusmita, J. C., Arya, R., Wijaya, J. M., Suryaningrum, K. M., & Siswanto, R. R. (2025). Optimizing database access strategy: A performance analysis comparison of raw SQL and Prisma ORM. Procedia Computer Science, 269, 1201–1210. https://doi.org/10.1016/j.procs.2025.09.061

[3] Singh, L. K., Tarai, S. K., Sharma, R., Godiyal, A., & others. (2025). A comparison of developer performance across raw SQL, ORM-inspired interfaces, and a Cosmos-specific ODM. International Journal of Scientific Research in Engineering and Management, 9(11), 1–9. https://doi.org/10.55041/IJSREM54453

[4] Hule, K. (2023). Analysis of Different ORM Tools for Data Access Object Tier Generation: A Brief Study. International Journal of Membrane Science and Technology. https://doi.org/10.15379/IJMST.V10I1.2842

[5] Güvercin, A. E., & Avenoglu, B. (2022). Performance analysis of object-relational mapping (ORM) tools in .NET 6 environment. Bilişim Teknolojileri Dergisi, 15(4), 453–465. https://doi.org/10.17671/gazibtd.1059516

[6] Hule, K., & Ranawat, R. (2023). Analysis of different ORM tools for data access object tier generation: A brief study. International Journal of Membrane Science and Technology, 10(1), 1277–1291. https://doi.org/10.15379/IJMST.V10I1.2842

[7] Wiatrowski, T. (2024). Comparative Analysis of ORM Systems for the .NET Platform. Journal of Computer Sciences Institute, 31, 97–102. https://doi.org/10.35784/jcsi.6012

[8] Hermanto, B., Parabi, M. I., Sakethi, D., & Azhar, N. (2025). Performance Comparison of Object-Relational Mapping (ORM) and SQL Query in Developing the Booking Service API at PT Tunas Dwipa Matra. Jurnal Pepadun, 6(2), 113–119. https://doi.org/10.23960/pepadun.v6i2.263

[9] Schwab, P.K., Röckl, J., Langohr, M.S. et al. (2021) Performance Evaluation of Policy-Based SQL Query Classification for Data-Privacy Compliance. Datenbank Spektrum 21, 191–201. https://doi.org/10.1007/s13222-021-00385-9

[10] Colley, D., Stanier, C., & Asaduzzaman, M. (2018). The impact of object-relational mapping frameworks on relational query performance. In 2018 International Conference on Computing, Electronics & Communications Engineering (iCCECE) (pp. 47–52). IEEE. https://doi.org/10.1109/iCCECOME.2018.8659222

[11] coursework_trips_db (Version latest) [GitHub repository]. GitHub. https://github.com/SashaRyback007/coursework_trips_db/tree/master

[12] Dobson, R. (2018). One-Way Analysis of Variance Test Add-on for SQL Statistics Package. MSSQLTips. https://www.mssqltips.com/sqlservertip/5712/a-oneway-analysis-of-variance-test-addon-for-the-sql-statistics-package/

[13] Pangesa, D. M., Astriani, M. S., & Manuaba, I. (2024). Study on object-relational mapping (ORM) data model performance effects in the oil and gas industry. In Proceedings of the 2024 2nd International Conference on Technology Innovation and Its Applications (ICTIIA), 1–6. https://doi.org/10.1109/ICTIIA61827.2024.10761468

[14] Bonteanu, A. M., & Tudose, C. (2024). Performance analysis and improvement for CRUD operations in relational databases from Java programs using JPA, Hibernate, Spring Data JPA. Applied Sciences, 14(7), Article 2743. https://doi.org/10.3390/app14072743

[15] Zhadko-Bazilevych, S. (2025). Analysis of ORM framework approaches for Node.js. Journal of Computer Sciences Institute, 37, 426–430. https://doi.org/10.35784/jcsi.7951

[16] Güvercin, A. E., & Avenoglu, B. (2022). Performance Analysis of Object-Relational Mapping (ORM) Tools in .Net 6 Environment. Bilişim Teknolojileri Dergisi, 15(4), 453-465. https://doi.org/10.17671/gazibtd.1059516

[17] Marchuk, I., Dyyak, I., & Makar, I. (2023). Performance analysis of database access: Comparison of direct connection, ORM, REST API and GraphQL approaches. In 2023 IEEE 13th International Conference on Electronics and Information Technologies (ELIT) (pp. 174–176). IEEE. https://doi.org/10.1109/ELIT61488.2023.10310748




DOI: http://dx.doi.org/10.30970/eli.33.8

Refbacks

  • There are currently no refbacks.