react fetch post takes too long

Question

I am converting web service from Angular to React. Angular version I worked with was 6.0.x and nodejs version was 10.18.x. React version that I am currently working with is 16.14.0 and nodejs is upgraded to 12.16.2.

In Angular, I used HttpClient module (for all CRUD), and it was simple and fast. I just had to write query and send request.

I thought it would be just as simple in React as well, but apparently not. I am using fetch for all CRUD, since it does not require third-party library. I want to keep number of third-party libraries as low as possible, since I have to run the service in the cloud (1 CPU and 1GB RAM with 50GB Disk size, which is not much to work with).

What I have so far is like below:

// page.jsx
class Page extends React.Component {
    toNext() {
        const postData = async() => {
            const postResponse = await fetch('/post-data', { 
                method: 'POST', 
                headers: { 'Content-Type': 'application/json', 'Accept': 'application/json' }, 
                body: JSON.stringify(this.state.data)
            });
            return await postResponse;
        }
        // after uploading successfully, move to anotherpage and load data including the one just loaded.
        postData().then(res => res.json()).then((data) => { if (!data.result) this.props.history.push('/anotherpage'); });
    }

    render() {
        return(
            <TouchableOpacity onPress={this.toNext}>
        )
    }
}

// index.js where fetch post is defined with url, query, etc
const express = require('express');
const mysql = require('mysql');

const host = 'host';
const user = 'user';
const password = 'password';

const conn = mysql.createPool({
    host: host,
    user: user,
    password: password,
    database: 'database'
});

app.post('/post-data', function (req, res) {
    let body = req.body;
    let isError = false;
    const checkError = () => {
        isError = true;
    }

    for (let row of body) {
        console.log('one loop started: ', new Date()) // check if the request itself takes too long
        conn.getConnection(function (err, connection) {
            connection.query(`INSERT INTO TABLE (COL1, COL2, COL3, ...) VALUES ("${row.COL1}", "${row.COL2}", "${row.COL3}", ...)`,
            function (error, results, fields) {
                if (error) {
                    console.log(row)
                    checkError();
                    throw error;
                };
            });
        });
        console.log('one loop ended: ', new Date()) // check if the request itself takes too long
    } 

    res.send({
        'result': isError
    });
})

Logs that I added shows that request itself takes only like 1 ms per row, but it takes about 4 minutes to upload all the data (44 rows, 17 columns, each cell with about 10 characters). In Angular, it only took seconds (2 seconds at most).

I tried to find what the actual post code looks like (app.post part in index.js), but all I got was the function code (jsx or tsx code). How should I go about posting data to DB? Does it take this long to post data via fetch?

UPDATE: As Phil suggested in the comment, Why can't we do multiple response.send in Express.js? explains that res.send() should be used only once, since it includes res.end(), and only the first res.send() is executed. So, I changed my code, and although at first, it looked like it fixed the original issue, the delay still exists in posting data to DB (about 4 minutes).

Answer 1

If there is anyone experiencing a similar problem, here is what I have found.

When I was working with Node 10.18.x, mysql package 2.17.1 and Angular, I inserted row by row in for-loop, and it worked without any problems.

But now I am working with Node 12.16.2, mysql package 2.18.1 and React, and it seems bulk-insertion is preferred to row-by-row insertion. Although row-by-row insertion works, it gives delay and data are inserted over a period of time (like few minutes). I still have to find out why, but in the meantime, I used bulk-insertion.

I followed this link on bulk-insertion.

My updated code looks like:

// index.js
app.post('/post-data', (req, res) => {
    let body = req.body;

    // define sql query
    let sql = `INSERT INTO TABLE (COL1, COL2, COL3, ...) VALUES ?`;

    // below two lines can be done in jsx(tsx)
    let values = new Array(body.length).fill(null).map(() => { return [] });
    for (let i = 0; i < body.length; i++) {
        for (let col of ['COL1', 'COL2', 'COL3', ...]) {
            values[i].push(body[i][col]);
        }
    }

    // it should be [values]. So, basically [[[values1, values2, values3, ...], [values1, values2, values3, ...]]]
    // bulk-insertion
    modConn.query(sql, [values], (error, results, fields) => {
        if (!error) {
            res.send(results)
        } else {
            res.send(error)
        }
    });
});

res.send() is inside if else, so I do not think there is a problem since only one of them is executed at any time. And, there is no delay in posting, so all my data (44 rows by 17 columns) are inserted under a second.