sql optimize query

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
http://img2.58codes.com/2024/201392127ktsgZxk6L.png
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
http://img2.58codes.com/2024/20139212U5LHVMRACr.png


关于作者: 网站小编

码农网专注IT技术教程资源分享平台,学习资源下载网站,58码农网包含计算机技术、网站程序源码下载、编程技术论坛、互联网资源下载等产品服务,提供原创、优质、完整内容的专业码农交流分享平台。

热门文章