Large record sets and insert is slow. How to best optimize?

[Scriberguy]

Hello JsStore and @ujjwalguptaofficial. I have two questions about dealing with large datasets (20,000+ records) and inserts. With WebSQL this was not an issue. Now with our port from WebSql to JsStore (for IndexedDB) some of our functions that took less than a second are taking almost 30 seconds. On a PAD restoring 80,000 records can take an hour or more. Also, we commonly do about 100 inserts into a 20,000+ record set and then run some clean up to remove 100 old records on the same table.

We have not added any indexes yet. That will be our next try.
Will skipDataCheck and validation options regain the speed and what is the down side?
We are using JsStore 4.3.5.

Thank you.

[ujjwalgupta94]

Yes skipDataCheck will make things faster as it will insert data without cheking data's type and all.

[ujjwalgupta94]

I hope you are using web worker in your project.

[Scriberguy]

I'm still having speed issues on large data. If I have 80,000 records and I want to find 100 records that match one field to delete and re-add. Is it normal for this to take a long time. It takes over 30 seconds on a Samsung Pad.
How do I set an index on one field? I mostly search by "zcode".

Here is one record of 80,000 in a SQL format:
CREATE TABLE UPDATE_TBL;INSERT INTO UPDATE_TBL(b_id,zcode,b_fname,b_text,b_qcode,b_order,b_screen,b_selected,b_tab,b_skip,b_section,note_accepted,date_added,time_added,date_updated,time_updated,express_note,id) VALUES ("QH_-T01-0000","Z 0475","AARon, Able","Complaints","/DCA","120000","Complaints","1","0","yes","Subjective","yes","2022-04-23","10:24:09","","","no:","7");

My table:
var tblUpdate = {
name: 'UPDATE_TBL',
columns: {
id: {
notNull: true,
dataType: 'number',
primaryKey: true,
autoIncrement: true
},
zcode: {
notNull: true,
dataType: 'string'
},
b_id: {
notNull: true,
dataType: 'string'
},
b_text: {
notNull: true,
dataType: 'string'
},
b_qcode: {
notNull: true,
dataType: 'string'
},
b_fname: {
notNull: true,
dataType: 'string'
},
b_order: {
notNull: true,
dataType: 'number'
},
b_screen: {
notNull: true,
dataType: 'string'
},
b_selected: {
notNull: true,
dataType: 'number'
},
b_tab: {
notNull: true,
dataType: 'number'
},
b_skip: {
notNull: true,
dataType: 'string'
},
b_section: {
notNull: true,
dataType: 'string'
},
note_accepted: {
notNull: true,
dataType: 'string'
},
date_added: {
notNull: true,
dataType: 'string'
},
time_added: {
notNull: true,
dataType: 'string'
},
date_updated: {
dataType: 'string'
},
time_updated: {
dataType: 'string'
},
express_note: {
dataType: 'string'
}
}
}

Thank you.

[Scriberguy]

When we delete (.remove) is one slow point:
updateUpdateTableNoteDateAndAcceptedStatus(zc, accepted).then(async function () {
var q2_1 = await jsstoreCon.remove({
from: 'UPDATE_TBL',
where: {
zcode: zc,
b_id: { like: 'MH-%' },
note_accepted: 'yes'
}
});

[ujjwalguptaofficial]

like query are expensive, but since you are having three filter - it's better to use first two filter which is directly equating. So like query will run on less data.

var q2_1 = await jsstoreCon.remove({
from: 'UPDATE_TBL',
where: {
zcode: zc,
note_accepted: 'yes'
b_id: { like: 'MH-%' },
}
});

[Scriberguy]

We added an index and changed the code to:
The index added to the table struct above: update_idx: { keyPath: ['zcode'] }

            console.time('.remove');
	var q2_1 = await jsstoreCon.remove({
                from: 'UPDATE_TBL',
                where: {
                    update_idx: [zc] //,
                    //note_accepted: 'yes',
		//b_id: { like: 'MH-%' }
                }
            });
	console.timeEnd('.remove');

It is still very slow. There was no improvement. I'm testing with 74,000 records.
It is taking 2.5 seconds to remove 5 records.

Is ".remove" using the index?
Can I mix and match native indexedDB functions to try for more speed?

Thank you.

[ujjwalguptaofficial]

is select query taking same time ? also i will recommend you to install idbstudio and then check the time.

It might be that - some other queries are in queue and remove is being called after some time. Idbstudio will help you to evaluate the benchmark.

[ujjwalguptaofficial]

https://github.com/ujjwalguptaofficial/idbstudio