Speed Comparison — Retrieving data with Sockets and API requests

Photo by Veri Ivanova on Unsplash

ABOUT

This article compares various methods of accessing database information from the client side.

The code for the following tutorial can be found in the speed comparison github repository.

Step by step instructions on how to create your own tests using the github repository code is shown below as well as in the repository’s read me file.

The three methods explored are:

Method 1: socket

using web socket connection to query the database directly

Method 2: socket + API

using web socket connection to request data through an API

Method 3: API

accessing data through an API

INTRODUCTION

For the comparison using the above 3 methods, I used a common set of data and query that will be retrieved from a PostgreSQL database.

The data consists of 1000 rows of user information from a table which has been generated using an online data generator.

The transaction total time was the end timestamp — the start timestamp. The start timestamp was when the client first initiated the action (click of the button) and the end timestamp was when the data was received from the server.

The code was set up in an extremely simplistic manner. 4buttons: 1 button for each method that would send a request to get the data and a result button that will generate an object in the console that will show all the data (start time(ms), end time(ms), total time(ms)). Click the method button for each transaction you wish to execute and the result button once you are satisfied with the number of transactions you have executed.

Simple layout for the 3 methods and result

TECH STACK

Front End

socket.IO + JQuery + AJAX

Back End

socket.IO + Node.js + Express + axios + PostgreSQL

INSTRUCTIONS

1. clone the github repository found here
2. create postgresql database named ‘speedtest’
3. modify .env file to reflect your database information

DB_HOST=localhost
DB_USER=yourusername
DB_PASS=yourpassword
DB_NAME=speedtest
DB_PORT=5432

1. run npm install to get the required packages.
2. run npm run db:reset to create the table (users) for the database, and create the seeded data
3. run npm start to start up the server

RESULTS

Note: each of the 3 methods were executed 20 times. Results were generated and then the statistical analysis was generated using Microsoft Excel.

Method # 1 — Socket

Method 1: socket — statistics

Method # 2 — Socket + API

Method 2: socket + API — statistics

Method # 3 — API

Method 3: API — statistics

TRANSACTION TIMES (MILLISECONDS)

transaction times for all 3 methods (in milliseconds)

SUMMARY

In this article, we have performed simplistic data retrieval from a PostgreSQL database 3 different ways.

Method 1: Socket connection directly retrieves data using SQL.

Method 2: Socket connection performs an API request (axios) to retrieve data.

Method 3: Client directly performs an API request (AJAX) to retrieve data.

Method 1 had the smallest average time to retrieve all data, while method 2 (which was a combination of method 1 and method 3) had the largest average time.

I hope you are able to leverage the information taken from this article and are able to apply it to more complex problems.