Analysis of the possibilities of optimizing SQL queries

Piotr Rymarski

piotr.rymarski@pollub.edu.pl
student (Poland)

Grzegorz Kozieł


Lublin University of Technology (Poland)

Abstract

Most of today's web applications run on relational database systems. Communication with them is possible through statements written in Structured Query Language (SQL). This paper presents the most popular relational database management systems and describes common ways to optimize SQL queries. Using the research environment based on fragment of the imdb.com database, implementing OracleDb, MySQL, Microsoft SQL Server and PostgreSQL engines, a number of test scenarios were performed. The aim was to check the performance changes of SQL queries resulting from syntax modication while maintaining the result, the impact of database organization, indexing and advanced mechanisms aimed at increasing the eciency of operations performed, delivered in the systems used. The tests were carried out using a proprietary application written in Java using the Hibernate framework.


Keywords:

query;, optimization;, analysis

R. Greenwald, R. Stackowiak, J. Stern, Oracle Essentials, Fifth Edition, O’Reilly Media, Sebastopol, 2013.
  Google Scholar

P. Muryjas, M. Skublewska-Paszkowska, D. Gutek, Współczesne Technologie Informatyczne. Eksploatacja baz danych, Politechnika Lubelska, 2011.
  Google Scholar

Editions and supported features of SQL Server, https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15, [29.12.2020].
  Google Scholar

What is PostgreSQL, https://www.postgresql.org/about/, [29.12.2020].
  Google Scholar

What is SQL, https://www.infoworld.com/article/3219795/what-is-sql-the-lingua-franca-of-data-analysis.html, [02.01.2021].
  Google Scholar

DB-Engines Ranking, https://db-engines.com/en/ranking, [02.12.2020].
  Google Scholar

Oracle Database Features, https://docs.oracle.com/cd/B19306˙01/server.102/b14220/intro.html, [29.12.2020].
  Google Scholar

Why Do Databases Crash, https://www.zmanda.com/blog/why-do-databases-crash-and-what-to-do-about-it/, [21.01.2021].
  Google Scholar

IMDb Datasets, https://www.imdb.com/interfaces/, [02.12.2020]
  Google Scholar

Download


Published
2021-06-30

Cited by

Rymarski, P., & Kozieł, G. (2021). Analysis of the possibilities of optimizing SQL queries. Journal of Computer Sciences Institute, 19, 151–158. https://doi.org/10.35784/jcsi.2641

Authors

Piotr Rymarski 
piotr.rymarski@pollub.edu.pl
student Poland

Authors

Grzegorz Kozieł 

Lublin University of Technology Poland

Statistics

Abstract views: 461
PDF downloads: 404