Publicação
Comparison of Semi-structured Data on MSSQL and PostgreSQL
| Resumo: | 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. |
|---|---|
| Autores principais: | Alves, Leandro |
| Outros Autores: | Cardoso, Filipe; Oliveira, Pedro; Rocha, Júlio; Wanzeller, Cristina; Martins, Pedro; Abbasi, Maryam |
| Assunto: | Key-Value Database MSSQL PostgreSQL TPC-H Performance GIN Computed columns |
| Ano: | 2023 |
| País: | Portugal |
| Tipo de documento: | capítulo de livro |
| Tipo de acesso: | acesso aberto |
| Instituição associada: | Instituto Politécnico de Santarém |
| Idioma: | inglês |
| Origem: | Repositório Científico do Instituto Politécnico de Santarém |
| Resumo: | 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. |
|---|