How to insert lots of data with a single message in sqlite

General
1

Hi everyone! I have a sqlite DB and i need to insert around 60000 new entrities, if i insert entry by entry, this takes lots of time (last time i do that, it took over 3 hours, don't know why) and freezes NR, how can i do this with a single message?

2

So you are saying you need to insert 60,000 new rows...right?
how many fields in each row?
How big are each of the fields?
How many rows are in the DB already?
How much activity will there be against the data per day?

I'm trying to get a feel for the volume of data you are dealing with.

3

exactly

the data comes from an excel file wich has 75 columns and between 2000 and 30000 rows, in one of the column there is a description of wich table has to go the row, but that row can go to 2 or more tables, so some times i have 60k new rows.

depend on the field, the largest is 100 chars, the lowest is 1 char

the DB is recently new, and some tables grow faster than others, the largest has 47000, but is also the most recent one.

around 10000 rows are updated every day, but it will reach 30k updates per day

every two or three days i upload an excel file to the system

4

Well, this isn't really a Node-RED question - but still.

You will need to understand prepared statements and batch updates.

In addition, with such a large batch of updates, you might consider dropping then rebuilding your indexes as this may be quicker.

However, I'd say that this is really beyond reasonable for SQLite. You are likely to be better off with a managed database like Postgres. Alternatively, if the data is time-based, I'd go with something like InfluxDB which is specifically tuned to timeseries data.

Oh, and if your Excel file is in the right format, you may find a native data upload tool that is more efficient at loading the whole file.

5

Is all the data going to be new or are you going to be updating some of it? i.e. on day 1 you might insert 60,000 rows, but on day 20 might some of the rows be replacing rows that already exist?

What platform are you running NR on?

6

any rate the formate is this:

INSERT INTO table1 (
 column1,
 column2 ,..)
VALUES
 (
 value1,
 value2 ,...),
 (
 value1,
 value2 ,...),
        ...
 (
 value1,
 value2 ,...);

although, you are still going to have to build the strings of data to insert. Have you thought about using something other than NR to load the data? try googling 'insert cvs into sqlite' and 'insert excel into sqlite'

7

sorry for the delay on the answer. Your solution was what i was looking for, Thank you!