Skip to content

Instantly share code, notes, and snippets.

@zo0m
Last active April 7, 2020 09:42
Show Gist options
  • Save zo0m/f3aeecc00beebce16c4a5df63a9304d8 to your computer and use it in GitHub Desktop.
Save zo0m/f3aeecc00beebce16c4a5df63a9304d8 to your computer and use it in GitHub Desktop.
Description bridge issue in SQLite implementation https://jira.appcelerator.org/browse/TIMOB-12291
// https://jira.appcelerator.org/browse/TIMOB-12291
/*
[DEBUG] sqliteBridgeBottleneckTest.compareQueries() start
[DEBUG] testCommonWay() start
[DEBUG] testCommonWay() loop:100 complete in 5087 ms
[DEBUG] testGroupConcat() start
[DEBUG] testGroupConcat() loop:100 complete in 844 ms
[DEBUG] testRs2JSON() start
[DEBUG] testRs2JSON() loop:100 complete in 3253 ms
[DEBUG] testGroupConcatRs2JSON() start
[DEBUG] testGroupConcatRs2JSON() loop:100 complete in 379 ms
[DEBUG] sqliteBridgeBottleneckTest.compareQueries() end
*/
const rs2json = require('ti.rs2json');
exports.compareQueries = function () {
testCommonWay();
testGroupConcat();
testRs2JSON();
testGroupConcatRs2JSON();
};
const TEST_QUERY_EXEC_COUNT = 100;
function testCommonWay() {
const startTime = new Date();
Ti.API.debug(`testCommonWay() start`);
for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
selectCommonWay();
}
const endTime = new Date();
Ti.API.debug(`testCommonWay() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};
function testGroupConcat() {
const startTime = new Date();
Ti.API.debug(`testGroupConcat() start`);
for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
selectGroupConcat();
}
const endTime = new Date();
Ti.API.debug(`testGroupConcat() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};
function testGroupConcatRs2JSON() {
const startTime = new Date();
Ti.API.debug(`testGroupConcatRs2JSON() start`);
for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
selectGroupConcatRs2JSON();
}
const endTime = new Date();
Ti.API.debug(`testGroupConcatRs2JSON() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};
function testRs2JSON() {
const startTime = new Date();
Ti.API.debug(`testRs2JSON() start`);
for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
selectRs2JSONWay();
}
const endTime = new Date();
Ti.API.debug(`testRs2JSON() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};
function selectCommonWay() {
const dbName = 'storage';
const db = Ti.Database.open(dbName);
const commonSelectQuery = `
SELECT _id, EquipmentType, DeviceType, ModelNumber, SerialNumber, InstallationDate, DeviceLocation, ExpirationType
FROM devices
LIMIT 1000
`;
let devices = [];
const resultSet = db.execute(commonSelectQuery, [])
if (resultSet) {
while (resultSet.isValidRow()) {
// just process result
const device = {
_id: resultSet.fieldByName('_id'),
EquipmentType: resultSet.fieldByName('EquipmentType'),
DeviceType: resultSet.fieldByName('DeviceType'),
ModelNumber: resultSet.fieldByName('ModelNumber'),
SerialNumber: resultSet.fieldByName('SerialNumber'),
InstallationDate: resultSet.fieldByName('InstallationDate'),
DeviceLocation: resultSet.fieldByName('DeviceLocation'),
ExpirationType: resultSet.fieldByName('ExpirationType')
};
devices.push(device);
resultSet.next();
}
resultSet.close();
}
db.close();
// Ti.API.debug(`deviceCounter = ${devices.length}`);
}
function selectRs2JSONWay() {
const dbName = 'storage';
const db = Ti.Database.open(dbName);
const commonSelectQuery = `
SELECT _id, EquipmentType, DeviceType, ModelNumber, SerialNumber, InstallationDate, DeviceLocation, ExpirationType
FROM devices
LIMIT 1000
`;
let devices = [];
const resultSet = db.execute(commonSelectQuery, []);
if (resultSet) {
const results = rs2json.asJSON(resultSet);
resultSet.close();
for (let device in results) {
devices.push(device);
}
}
db.close();
// Ti.API.debug(`selectRs2JSONWay deviceCounter = ${devices.length}`);
}
function selectGroupConcat() {
const dbName = 'storage';
const db = Ti.Database.open(dbName);
const groupConcatQuery = `
SELECT GROUP_CONCAT(JSON_OBJECT(
'EquipmentType', EquipmentType,
'DeviceType', DeviceType,
'ModelNumber', ModelNumber,
'SerialNumber', SerialNumber,
'InstallationDate', InstallationDate,
'DeviceLocation', DeviceLocation,
'ExpirationType', ExpirationType
)) as conc
FROM (
SELECT *
FROM devices
LIMIT 1000
)
`;
let devices = [];
const resultSet = db.execute(groupConcatQuery, []);
if (resultSet) {
while (resultSet.isValidRow()) {
const concatenated = resultSet.fieldByName('conc');
if (concatenated) {
devices = JSON.parse(`[${concatenated.split(',')}]`);
}
resultSet.next();
}
resultSet.close();
}
db.close();
// Ti.API.debug(`selectGroupConcat deviceCounter = ${devices.length}`);
// Ti.API.debug(`selectGroupConcat deviceCounter = ${JSON.stringify(devices.slice(0, 3))}`);
}
function selectGroupConcatRs2JSON() {
const dbName = 'storage';
const db = Ti.Database.open(dbName);
const groupConcatQuery = `
SELECT GROUP_CONCAT(JSON_OBJECT(
'EquipmentType', EquipmentType,
'DeviceType', DeviceType,
'ModelNumber', ModelNumber,
'SerialNumber', SerialNumber,
'InstallationDate', InstallationDate,
'DeviceLocation', DeviceLocation,
'ExpirationType', ExpirationType
)) as conc
FROM (
SELECT *
FROM devices
LIMIT 1000
)
`;
// without JSON_OBJECT works x2 faster
// const groupConcatQuery = `
// SELECT GROUP_CONCAT(
// _id ||','||
// COALESCE(EquipmentType, 0) ||','||
// COALESCE(DeviceType, 0) ||','||
// COALESCE(ModelNumber, 0) ||','||
// COALESCE(SerialNumber, 0) ||','||
// COALESCE(InstallationDate, 0) ||','||
// COALESCE(DeviceLocation, 0) ||','||
// COALESCE(ExpirationType, 0)
// ) as conc
// FROM (
// SELECT *
// FROM devices
// LIMIT 1000
// )
// `;
let devices = [];
const resultSet = db.execute(groupConcatQuery, []);
if (resultSet) {
const [results] = rs2json.asJSON(resultSet);
resultSet.close();
const concatenated = results['conc'];
if (concatenated) {
devices = JSON.parse(`[${concatenated.split(',')}]`);
}
// if (concatenated) {
// const concatenatedArray = concatenated.split(',');
// for (let j = 0; j < concatenatedArray.length; j = j + 8) {
// const device = {
// _id: concatenatedArray[j + 0],
// EquipmentType: concatenatedArray[j + 1],
// DeviceType: concatenatedArray[j + 2],
// ModelNumber: concatenatedArray[j + 3],
// SerialNumber: concatenatedArray[j + 4],
// InstallationDate: concatenatedArray[j + 5],
// DeviceLocation: concatenatedArray[j + 6],
// ExpirationType: concatenatedArray[j + 7]
// };
// devices.push(device);
// }
// }
}
db.close();
// Ti.API.debug(`selectGroupConcatRs2JSON deviceCounter = ${devices.length}`);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment