| Name: | Description: | Size: | Format: | |
|---|---|---|---|---|
| 321.36 KB | Adobe PDF |
Advisor(s)
Abstract(s)
The present study intends to compare the performance of two Data Base Management Systems, specifically Microsoft SQL Server and PostgreSQL, focusing on data insertion, queries execution, and indexation. To simulate how Microsoft SQL Server performs with key-value oriented datasets we use a converted TPC-H lineitem table. The data set is explored in two different ways, firsts using the key-value-like format
and second in JSON format. The same dataset is applied to PostgreSQL DBMS to analyse performance and compare both database engines. After testing the load process on both databases, performance metrics (execution times) are obtained and compared. Experimental results show that, in general, inserts are approximately twice times faster in Microsoft SQL Server because they are injected as plain text without any type of verification, while in PostgreSQL, loaded data includes a validating process, which delays the loading process. Moreover, we did additional indexation tests, from which we concluded that in general, data loading performance degrades. Regarding query performance in PostgreSQL, we conclude that with indexation, queries become three or four percent faster, and six times faster in Microsoft SQL Server.
Description
Keywords
Key-Value Database MSSQL PostgreSQL TPC-H Performance GIN Computed columns
Citation
Alves, L., Oliveira, P., Rocha, J., Wanzeller,C., Cardoso, F., Martins, P. & Abbasi, M. (2023). Comparison of Semi-structured Data on MSSQL and PostgreSQL. In J. L. Reis, M. K. Peter, J. A. Varela González, Z. Bogdanović, Z. (Eds), Marketing and Smart Technologies: Vol 337. Smart Innovation, Systems and Technologies (pp. 31-43). Springer, Singapore. https://doi.org/10.1007/978-981-19-9099-1_3