query cloud db raw data need longtime : 100 gps track's raw data(about 11Mb) need 16sencods
why so slow?
guess 1 : data size is too large , slow is caused by network transport.
guess 2 : query is not good, db find the track need a longtime.
use the explain analyze to show the time of query
plantime : sql choose which method to excute the query
guess 1 failed. download time is 7.61ms.
so guess 2 is good , but how to optimize query.
that my query:
function searchByRaceRecordId(raceRecordId) { var sql = 'select racerecordid,utc,fix,latitude,longitude,cloudracetext.realdistance,gpsheight,gpsspeed,direction from cloudracetext where racerecordid = $1'; var params = [raceRecordId]; return new Promise(function (resolve, reject) { pg_client.query(sql, params, (err,res) => { if(err) { reject(err); } else { resolve(model_make_race_track_rawdata(res['rows'])); // resolve(res['rows']); } }); });};function searchByRaceRecordIdList(raceRecordId_list) { console.log('start prepare raw data'); var starttime = Date.now(); var functionList = []; for(var i=0;i<raceRecordId_list.length;i++) { functionList.push(searchByRaceRecordId(raceRecordId_list[i])); } return new Promise(function(resolve,reject){ Promise.all(functionList) .then(values=>{ var endtime = Date.now(); console.log('data prepared',endtime.toString() - starttime.toString()); resolve(values); }) .catch(errs=>{ reject(errs); }); })};
optimize method : use the IN (list) to send a single query to cloudb.
function searchByRaceRecordIdList_optimize(raceRecordId_list) { console.log('start prepare raw data'); var starttime = Date.now(); console.log(raceRecordId_list.length); var params = []; for(var i = 1; i <= raceRecordId_list.length; i++) { params.push('$' + i); } console.log(params); var sql = 'select racerecordid,utc,fix,latitude,longitude,cloudracetext.realdistance,gpsheight,gpsspeed,direction from cloudracetext where racerecordid IN (' + params.join(',') + ')'; console.log(sql); return new Promise(function (resolve, reject) { pg_client.query(sql, raceRecordId_list, (err,res) => { if(err) { reject(err); } else { var endtime = Date.now(); console.log('data prepared',endtime.toString() - starttime.toString()); // resolve(model_make_race_track_rawdata(res['rows'])); resolve(model_make_race_track_rawdata(res['rows'])); } }); });};
that's the result