I work for a company whose clients are other companies. The company recordset in the DB has 40+ fields with ten thousand plus companies (including one field that holds in most cases a ridiculously long string.) The JSON Data payload that comes back via express and a wildcard SQL call is north of 4 million lines in Postman.
Background:
We had this set up using ASMX Web Services in VB.NET but the response to the client was averaging close to thirty seconds when having to serialize the JSON. It was cumbersome and quite a pain to work with.
Going back to the previous version in .NET MVC it would still sometimes take over ten to fifteen seconds to populate a simple select input with the company name much less than render the UI.
The New Environment:
I have currently constructed the system in its 4th generation using a SERN environment with webpack 3+. I have enabled the API to run with CORS on a separate port(5000) from my app's port(8080) and have tested the payload. It is still 8 seconds. Better but not optimal.
The Question:
How can I speed up the payload? I have heard of React being able to split payloads from Node API's but haven't a clue on how to get that done. Can anyone take the code below and either tell me where I can improve or point me to some libraries or something that can speed up the performance of the SQL call or data transfer?
Current POSTMAN Performance stats: SQL: 2.679 secs Express: 1.331 secs API to Client: 3.976 secs
package.json:
{
"name": "project",
"version": "1.0.0",
"description": "desc",
"main": "index.js",
"scripts": {
"build": "webpack",
"start": "concurrently \"webpack-dev-server\" \" node ./server/core/server.js\"",
"dev": "webpack-dev-server",
"server": "node ./server/core/server.js"
},
"repository": {
"type": "git",
"url": "git+https://github.com/djErock/EMR4.git"
},
"author": "Erik Grosskurth",
"license": "MIT",
"bugs": {
"url": "https://github.com/djErock/EMR4/issues"
},
"homepage": "https://github.com/djErock/EMR4#readme",
"dependencies": {
"axios": "^0.16.2",
"cors": "^2.8.4",
"jquery": "^3.2.1",
"react": "^15.6.1",
"react-dom": "^15.6.1",
"react-router-dom": "^4.1.1"
},
"devDependencies": {
"babel-core": "^6.25.0",
"babel-loader": "^7.1.1",
"babel-preset-latest": "^6.24.1",
"babel-preset-react": "^6.24.1",
"concurrently": "^3.5.0",
"css-loader": "^0.28.4",
"express": "^4.16.0",
"file-loader": "^0.11.2",
"mssql": "^4.1.0",
"react-hot-loader": "^3.0.0-beta.7",
"style-loader": "^0.18.2",
"webpack": "^3.6.0",
"webpack-dev-middleware": "^1.12.0",
"webpack-dev-server": "^2.9.1"
}
}
Webpack.config:
var webpack = require('webpack');
var path = require('path');
module.exports = {
entry: [
'react-hot-loader/patch',
'webpack-dev-server/client?http://localhost:8080',
'webpack/hot/only-dev-server',
'./src/index.js'
],
output: {
filename: 'bundle.js',
path: path.resolve(__dirname, 'dist'),
publicPath: '/'
},
module: {
rules: [
{
test: /\.js$/,
use: 'babel-loader',
exclude: /node_modules/
},
{
test: /\.css$/,
use: ['style-loader', 'css-loader']
},
{
test: /\.(png|svg|jpg|gif)$/,
use: [
'file-loader'
]
}
],
},
devServer: {
historyApiFallback: true,
hot: true
},
plugins: [
new webpack.HotModuleReplacementPlugin(),
new webpack.NamedModulesPlugin(),
]
};
Server.js
const webpackDevServer = require('webpack-dev-server');
const webpack = require('webpack');
const config = require('../../webpack.config.js');
const options = {
contentBase: './dist',
hot: true
};
var app = require('express')();
var cors = require('cors');
app.use(function (req, res, next) {
res.setHeader('Access-Control-Allow-Origin', '*');
res.setHeader('Access-Control-Allow-Methods', 'GET, POST');
res.setHeader('Access-Control-Allow-Headers', 'X-Requested-With,content-type');
res.setHeader('Access-Control-Allow-Credentials', true);
// Pass to next layer of middleware
next();
});
const companies = require("../controllers/company.js");
app.get('/api/companies', function (req, res) {
companies.getCompanyList(req, res);
});
webpackDevServer.addDevServerEntrypoints(config, options);
const compiler = webpack(config);
const server = new webpackDevServer(compiler, options);
app.listen(5000, () => {
console.log('dev server listening on port 5000');
});
comapny.js
var db = require("../core/db");
exports.getCompanyList = function(req, resp) {
db.execSQL("SELECT * FROM tblCompany", function(data, err) {
if (err) {
resp.writeHead(500, "Internal Error Occurred",{"Content-Type": "application/json"});
resp.write(JSON.stringify(err));
}else {
resp.writeHead(200, {"Content-Type": "application/json"});
resp.write(JSON.stringify(data));
}
resp.end();
});
}
DB.js
var sqlDB = require("mssql");
var settings = require("../settings.js");
exports.execSQL = function(sql, callback) {
var conn = new sqlDB.ConnectionPool(settings.dbConfig);
conn.connect().then(function() {
var req = new sqlDB.Request(conn);
req.query(sql).then(function(recordset) {
callback(recordset);
}).catch(function(err) {
console.log(err);
callback(null, err);
});
}).catch(function(err) {
console.log(err);
callback(null, err);
});
};
React with axios ajax call in component:
handleOnChange(e) {
e.preventDefault();
//Let's call the caduceus server
axios.get(Constants.WS_PREFIX+'companies')
.then(function (response) {
console.log(response);
})
.catch(function (error) {
console.log(error);
});
}
