distinct values from very large table
lazarus / 63-Database

----------------------------------------------------------------------
Joanna · 2024-09-05 02:18

Hi everyone
I have an sqlite  table with over 73 million rows that has 8 numerical
columns. I want to gather the distinct values from the 8 columns and
put them into a combobox.

The process of getting distinct values for even one column seems to be
very slow and consumes a lot of cpu cycles.

Is there any other way to do this ?

----------------------------------------------------------------------
cdbc · 2024-09-05 04:53

Hi
I would say SQL, but the material you're providing is a bit on the
skinny side 
Regards Benny

----------------------------------------------------------------------
Joanna · 2024-09-05 05:14

Maybe I'm missing an index for this?

----------------------------------------------------------------------
cdbc · 2024-09-05 05:48

Hi
Hmmm, that depends... Does this table of yours come with chairs?
...And what is it made of? ...old apple-crates?!?
More info, please 
Regards Benny

----------------------------------------------------------------------
Zvoni · 2024-09-05 06:18

As Benny said: SQL is (usually) the way to go, though deduplicating is
always expensive

To get a better idea:
of those 73 Million rows, per each column of those 8 columns: What's
the ratio between distinct values and the count of rows?
e.g. of 73 million Values in Column 4, there are 20 distinct values
and so on.

I have an idea which might just work

OH, AND WHICH DBMS?

----------------------------------------------------------------------
Joanna · 2024-09-05 06:37

Quote from: Zvoni on September 05, 2024, 08:18:03 amTo get a better
idea:
of those 73 Million rows, per each column of those 8 columns: What's
the ratio between distinct values and the count of rows?
e.g. of 73 million Values in Column 4, there are 20 distinct values
and so on.

I have an idea which might just work

OH, AND WHICH DBMS?

I’m using sqlite3 and the values in the table are mostly in the range
of -6 to 7 and 102 to 105 so far as I know. I suppose I was wanting to
be thorough by getting all distinct values from table to see what is
in it

----------------------------------------------------------------------
Zvoni · 2024-09-05 06:56

Quote from: Joanna on September 05, 2024, 08:37:44 amQuote from: Zvoni
on September 05, 2024, 08:18:03 amTo get a better idea:
of those 73 Million rows, per each column of those 8 columns: What's
the ratio between distinct values and the count of rows?
e.g. of 73 million Values in Column 4, there are 20 distinct values
and so on.

I have an idea which might just work

OH, AND WHICH DBMS?

I’m using sqlite3 and the values in the table are mostly in the range
of -6 to 7 and 102 to 105 so far as I know. I suppose I was wanting to
be thorough by getting all distinct values from table to see what is
in it

OK, next....
Do you want the Distinct Values per each Column or across all 8
Columns?
Distinct per each Column: You need 8 separate Queries
Distinct across all columns: You need a UNION Select

This is the "regular" way. Since we're talking 73M rows, Performance
will probably take a massive hit.
So depending on your scenario i might have a workaround for future
performance

----------------------------------------------------------------------
Joanna · 2024-09-05 14:04

I want distinct values across all 8 columns. I have already tried
using union on the columns and it was a disaster. It stalls program
and maxes out my cpu. Someone in chat also gave me a cte whereby the
distinct values of coikmns wertr selected first and then fed to the
part that does unions. Too slow.

----------------------------------------------------------------------
Zvoni · 2024-09-05 14:29

Quote from: Joanna on September 05, 2024, 04:04:45 pmI want distinct
values across all 8 columns. I have already tried using 7nion on the
columns and it was a disaster. It stalls program and maxes out my cpu.
Someone in chat also gave me a cte whereby the distinct values of
coikmns wertr selected first and then fed to the part that does
unions. Too slow.
Thought so.

Question: Are you using any Triggers in your SQLite-Database?
Are there any Detail-records in other tables (Primary/ForeignKey)
relying on this "73M-Rows"-Table?

because i have a ready "Proof-of-Concept" for my idea.
It involves (17?) triggers for your "73M"-Rows-Table, keeping track of
the distinct values in a separate table.

That said: You can't avoid populating that "Distinct" table at least
once (which should be fairly easy).

If my PoC works out, you would just have to grab those "distinct"
values from that separate table, instead of doing an 8-way UNION-
Select on your Monster-table

Proof of Concept (Only 3 Columns instead of 8 )
tbl_joanna_master (this is your "73M-Rows"-Table)
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE "tbl_joanna_master" (        "ID"    INTEGER,        "Col1" 
INTEGER,        "Col2"  INTEGER,        "Col3"  INTEGER,       
PRIMARY KEY("ID"))
the "distinct values"-table
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE "tbl_joanna_distinct" (        "DistValue"     INTEGER NOT NULL
UNIQUE,        "DistCount"     INTEGER DEFAULT 1)
The 7 triggers involved in this PoC (Note Column and Tablenames)
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TRIGGER trg_joanna_master_AI_Col1 AFTER INSERT ON tbl_joanna_masterFOR
EACH ROW WHEN NEW.Col1 IS NOT NULLBEGIN        INSERT INTO
tbl_joanna_distinct(DistValue) VALUES(NEW.Col1) ON CONFLICT(DistValue)
DO UPDATE SET DistCount=DistCount+1 WHERE DistValue=NEW.Col1;       
END; CREATE TRIGGER trg_joanna_master_AI_Col2 AFTER INSERT ON
tbl_joanna_masterFOR EACH ROW WHEN NEW.Col2 IS NOT NULLBEGIN       
INSERT INTO tbl_joanna_distinct(DistValue) VALUES(NEW.Col2) ON
CONFLICT(DistValue) DO UPDATE SET DistCount=DistCount+1 WHERE
DistValue=NEW.Col2;        END; CREATE TRIGGER
trg_joanna_master_AI_Col3 AFTER INSERT ON tbl_joanna_masterFOR EACH
ROW WHEN NEW.Col3 IS NOT NULLBEGIN        INSERT INTO
tbl_joanna_distinct(DistValue) VALUES(NEW.Col3) ON CONFLICT(DistValue)
DO UPDATE SET DistCount=DistCount+1 WHERE DistValue=NEW.Col3;       
END; CREATE TRIGGER trg_joanna_master_AU_Col1 AFTER UPDATE ON
tbl_joanna_masterFOR EACH ROWWHEN OLD.Col1<>NEW.Col1BEGIN        --
Reduce Count by One        UPDATE tbl_joanna_distinct               
SET DistCount=DistCount-1                WHERE DistValue=OLD.Col1;   
   -- Delete if Count reaches 0            DELETE FROM
tbl_joanna_distinct WHERE DistValue=OLD.Col1 AND DistCount<=0;       
-- INSERT New.Col1 if it doesn't exist, otherwise Count up by 1      
INSERT INTO tbl_joanna_distinct(DistValue) VALUES(NEW.Col1) ON
CONFLICT(DistValue) DO UPDATE SET DistCount=DistCount+1 WHERE
DistValue=NEW.Col1;END; CREATE TRIGGER trg_joanna_master_AU_Col2 AFTER
UPDATE ON tbl_joanna_masterFOR EACH ROWWHEN OLD.Col2<>NEW.Col2BEGIN  
    -- Reduce Count by One        UPDATE tbl_joanna_distinct         
     SET DistCount=DistCount-1                WHERE
DistValue=OLD.Col2;        -- Delete if Count reaches 0           
DELETE FROM tbl_joanna_distinct WHERE DistValue=OLD.Col2 AND
DistCount<=0;        -- INSERT New.Col1 if it doesn't exist, otherwise
Count up by 1        INSERT INTO tbl_joanna_distinct(DistValue)
VALUES(NEW.Col2) ON CONFLICT(DistValue) DO UPDATE SET
DistCount=DistCount+1 WHERE DistValue=NEW.Col2;END; CREATE TRIGGER
trg_joanna_master_AU_Col3 AFTER UPDATE ON tbl_joanna_masterFOR EACH
ROWWHEN OLD.Col3<>NEW.Col3BEGIN        -- Reduce Count by One       
UPDATE tbl_joanna_distinct                SET DistCount=DistCount-1  
            WHERE DistValue=OLD.Col3;        -- Delete if Count
reaches 0            DELETE FROM tbl_joanna_distinct WHERE
DistValue=OLD.Col3 AND DistCount<=0;        -- INSERT New.Col1 if it
doesn't exist, otherwise Count up by 1        INSERT INTO
tbl_joanna_distinct(DistValue) VALUES(NEW.Col3) ON CONFLICT(DistValue)
DO UPDATE SET DistCount=DistCount+1 WHERE
DistValue=NEW.Col3;END; CREATE TRIGGER trg_joanna_master_BD BEFORE
DELETE ON tbl_joanna_masterFOR EACH ROWBEGIN        -- Reduce Count by
One        UPDATE tbl_joanna_distinct SET DistCount=DistCount-1 WHERE
DistValue=OLD.Col1;        UPDATE tbl_joanna_distinct SET
DistCount=DistCount-1 WHERE DistValue=OLD.Col2;        UPDATE
tbl_joanna_distinct SET DistCount=DistCount-1 WHERE
DistValue=OLD.Col3;        -- Delete if Count reaches 0           
DELETE FROM tbl_joanna_distinct WHERE DistValue=OLD.Col1 AND
DistCount<=0;        DELETE FROM tbl_joanna_distinct WHERE
DistValue=OLD.Col2 AND DistCount<=0;        DELETE FROM
tbl_joanna_distinct WHERE DistValue=OLD.Col3 AND DistCount<=0;END;
Run this INSERT, and you will see, how it the "distinct"-table counts
up. Change/Delete Values in your Master, and see how the count changes
in "distinct"
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbl_joanna_master(Col1,Col2,Col3) VALUES(1,2,3),(4,5,6),(1,3,6);

----------------------------------------------------------------------
Joanna · 2024-09-05 23:01

Thanks Zvoni, my sql skills are not so great I’ve never used triggers
before.
The table for distinct also stores the occurrences of each distinct
value and that is something I’d like to know.

I will add this to my schema. I do have another table that references
the big table but not the columns I want unique values for.

One thing I wonder about though is how much will the triggers slow
down things when I recreate the table and make new data?

----------------------------------------------------------------------
egsuh · 2024-09-06 02:59

I believe Zvoni's approach is the right solution, but I'd like to
suggest a simpler solution.

1) First, create tables that contain only distinct values for each 8
fields (and number of rows of each distinct value for current state if
necessary). That would be similar to :

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     CREATE
TABLE "tbl_joanna_field1_distinct" (            "DistValue"    
INTEGER NOT NULL UNIQUE,            "DistCount"     INTEGER DEFAULT 1 
  )     CREATE TABLE "tbl_joanna_field2_distinct" (           
"DistValue"     INTEGER NOT NULL UNIQUE,            "DistCount"    
INTEGER DEFAULT 1    )    // one TABLE FOR each FIELD 
2) And define triggers for your own table.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TRIGGER trg_joanna_master_AI_Col1 AFTER INSERT ON
tbl_joanna_masterASBEGIN     IF  (NEW.field1 IS NOT NULL) THEN  BEGIN
        UPDATE tbl_joanna_field1_distinct SET  distcount = distcount +
1 WHERE DistValue=NEW.field1;          IF (rec_count <= 0) THEN      
      INSERT INTO tbl_joanna_field1_distinct (nenw.field1, 1);    
END;               IF  (NEW.field2 IS NOT NULL) THEN  BEGIN        
 UPDATE tbl_joanna_field2_distinct SET  distcount = distcount + 1
WHERE DistValue=NEW.field2;          IF (rec_count <= 0) THEN        
    INSERT INTO tbl_joanna_field2_distinct (nenw.field2, 1);     END;
   -- and so on for other fields END; CREATE TRIGGER
trg_joanna_master_AI_Col1 AFTER DELETE ON tbl_joanna_masterASBEGIN   
 IF  (OLD.field1 IS NOT NULL) THEN  BEGIN          UPDATE
tbl_joanna_field1_distinct SET  distcount = distcount - 1 WHERE
DistValue=OLD.field1;     END;               IF  (OLD.field2 IS NOT
NULL) THEN  BEGIN          UPDATE tbl_joanna_field2_distinct SET
 distcount = distcount - 1 WHERE DistValue=OLD.field2;     END;     --
and so on for other fieldsEND; CREATE TRIGGER
trg_joanna_master_AI_Col1 AFTER UPDATE ON tbl_joanna_masterASBEGIN   
 IF  (OLD.field1 <> NEW.field1) THEN  BEGIN         -- procedure for
insert trigger for new.field1         -- procedure for delete trigger
for old.fied1     END;     -- and so on for other fields END;  
Not sure syntaxes are right.

----------------------------------------------------------------------
Zvoni · 2024-09-06 05:59

Quote from: egsuh on September 06, 2024, 04:59:37 amNot sure syntaxes
are right.
They are not. SQLite doesn't support "IF-THEN-ELSE" within a Trigger-
Body.
You can't even do a "SELECT CASE WHEN XXXX THEN INSERT INTO
blablbalba"
Believe me, i tried.
It's the reason, why i had to make a trigger per Column for AFTER
UPDATE.

@Joanna: Question for those 8 Columns: Are NULLs allowed in those 8
Columns?
Because if NOT (there always must be an Integer-Value), then the
AFTER-INSERT-Triggers can be reduced to one Trigger

Quote from: Joanna on September 06, 2024, 01:01:17 amThanks Zvoni, my
sql skills are not so great I’ve never used triggers before.Think of
Triggers like "Events" you're used to from Lazarus
an AFTER INSERT-trigger would be something like an
"OnAfterInsert"-Event you might know from Lazarus


QuoteThe table for distinct also stores the occurrences of each
distinct value and that is something I’d like to know.
To store the count is the only way i've found to keep track, WHEN to
delete a former "distinct" value.
You could argue, that it's not necessary to delete that value from
"Distinct", when you keep the count = 0.
Then to Grab "What distinct" Values are currently in my 73M rows?"
it would be a simple "SELECT DistValue FROM tbl_joanna_distinct WHERE
DistCount>0 ORDER BY DistValue"
(which you should do anyway that way)

QuoteI will add this to my schema. I do have another table that
references the big table but not the columns I want unique values for.
My question, if you have another table, which references the big
table, doesn't have to do if it references the values.
It has to do with, that for the first creation of the distinct table,
you would have to drop any and all ForeignKey-constraints between
them, because you would have to do a dump of the big table, drop/empty
out the big table, create the triggers and distinct table, then
reimport the big table (to get the Values and counts starting in
"Distinct")
This would be a one-time thing

QuoteOne thing I wonder about though is how much will the triggers
slow down things when I recreate the table and make new data?The first
creation (see above how to) will take time, since in a nutshell you
execute 73M x 8 SQL-Instructions instead of just 73M
BUT AFTER THAT (running process), it's the difference between 1
Statement and max. 24 (depending what you do, INSERT ( 8 Statements),
UPDATE (24), DELETE ( 8 ) Statements, which nowadays is pretty much
not noticable

----------------------------------------------------------------------
egsuh · 2024-09-06 06:31

QuoteThey are not. SQLite doesn't support "IF-THEN-ELSE" within a
Trigger-Body.
You can't even do a "SELECT CASE WHEN XXXX THEN INSERT INTO
blablbalba"
Believe me, i tried.
It's the reason, why i had to make a trigger per Column for AFTER
UPDATE.
I see. My grammar is of Firebird. I never doubt you. Just thinking of
algorithm or approaches --- not specific syntax.

----------------------------------------------------------------------
Joanna · 2024-09-06 08:05

It’s a shame that sql doesn’t support passing different tables as a
parameter to a procedure. It seems painfully redundant compared to
pascal. I guess that is the big difference between compiled and
scripting languages?

Zvoni the colum values in this particular table are not allowed to be
null. Default is 0. Also I probably should have mentioned that this
table is a lookup table which is not supposed to be modified by users.

I’m glad you gave code for update and deleting though, I will save it
in case I ever need it.

If I want to populate the distinct table from existing table as a test
, how would I do it? I’m guessing something along the lines of
Insert into tbl_distinct select (columns I want) from bigtable?

----------------------------------------------------------------------
egsuh · 2024-09-06 08:13

insert into distinct_table select distinct field1 from master_table;

would work.

----------------------------------------------------------------------
Zvoni · 2024-09-06 08:16

Quote from: Joanna on September 06, 2024, 10:05:12 amIt’s a shame that
sql doesn’t support passing different tables as a parameter to a
procedure. It seems painfully redundant compared to pascal. I guess
that is the big difference between compiled and scripting
languages?That doesn't have to do with SQL.
Not a single DBMS i know allows passing of tablenames or Columnames as
a Parameter.
It is as it is


QuoteZvoni the colum values in this particular table are not allowed
to be null. Default is 0. Also I probably should have mentioned that
this table is a lookup table which is not supposed to be modified by
users.OK, the separate AFTER INSERT Triggers can be coalesced into one
single Trigger
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TRIGGER trg_joanna_master_AI AFTER INSERT ON tbl_joanna_masterFOR EACH
ROWBEGIN        INSERT INTO tbl_joanna_distinct(DistValue)
VALUES(NEW.Col1) ON CONFLICT(DistValue) DO UPDATE SET
DistCount=DistCount+1 WHERE DistValue=NEW.Col1;          INSERT INTO
tbl_joanna_distinct(DistValue) VALUES(NEW.Col2) ON CONFLICT(DistValue)
DO UPDATE SET DistCount=DistCount+1 WHERE DistValue=NEW.Col2;       
INSERT INTO tbl_joanna_distinct(DistValue) VALUES(NEW.Col3) ON
CONFLICT(DistValue) DO UPDATE SET DistCount=DistCount+1 WHERE
DistValue=NEW.Col3;             -- and so onEND;
QuoteI’m glad you gave code for update and deleting though, I will
save it in case I ever need it. I would use them in any case. Because
they still work if you do something from a different DB-Client
(DBBrowser, DBeaver etc.)

QuoteIf I want to populate the distinct table from existing table as a
test , how would I do it? I’m guessing something along the lines of
Insert into tbl_distinct select columns I want ?
Close.
it would be
1) Drop ForeignKey-Constraints between your Big table and any Child-
tables that have a reference to the primary key of your big table
2) rename your big table to "tbl_joanna_master_old"
3) create "tbl_joanna_master" anew with exactly the same columns and
attributes
4) create "tbl_joanna_distinct" and create the Triggers
5) execute
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbl_joanna_masterSELECT * FROM tbl_joanna_master_old
6) Go for Lunchbreak 

7) Create the ForeignKey-Constraints again
8 ) Check plausibilty in "tbl_joanna_distinct"
9) drop "tbl_joanna_master_old"

Of course: Adjust column- and tablenames to your scenario!

----------------------------------------------------------------------
Zvoni · 2024-09-06 08:17

Quote from: egsuh on September 06, 2024, 10:13:25 am  insert into
distinct_table select distinct field1 from master_table;

would work.
No it wouldn't.
It's missing the Counts, nevermind, that Joanna needs the Distinct
values across all 8 columns, meaning an 8-way UNION with
deduplication, which KABOOM's her Computer

----------------------------------------------------------------------
Zvoni · 2024-09-06 08:48

One interesting fact about Triggers:
https://sqlite.org/forum/info/2a650a70a6136537fa2d08789b63f1863ad28a55
b871a7e7e9564664c8c7a87e

Triggers are always executed within the context of the CALLING
Transaction.
Meaning: If you do an INSERT into your Big-Table, it fires all
necessary Triggers with all succeeding "changes" to wherever
But if you rollback your INSERT, it also rolls back all other changes
done by the triggers (Updates/Deletes)!!
It also means: If any Statement fails (your original statement, or a
Statement from within a Trigger), EVERYTHING fails/gets rolled back

That said: Especially if you use triggers, it's always good practice
to use explicit transactions

----------------------------------------------------------------------
Aruna · 2024-09-06 14:27

Quote from: Joanna on September 05, 2024, 04:18:34 amHi everyone
I have an sqlite  table with over 73 million rows that has 8 numerical
columns. I want to gather the distinct values from the 8 columns and
put them into a combobox. Hello @Joanna, What if you query a smaller
portion of the data-set at a time, say 1 million records at a time?
Limit Result Sets using LIMIT and OFFSET to fetch smaller chunks of
data. Optimize Queries to retrieve only the columns and rows you need.
Use Proper Query Filters to reduce the amount of data processed.

Quote from: Joanna on September 05, 2024, 04:18:34 amThe process of
getting distinct values for even one column seems to be very slow and
consumes a lot of cpu cycles.Returning very large result sets can be
slow, especially if all results need to be processed or transferred.
Slow disk I/O, limited memory, or insufficient CPU resources can
impact query performance. I am curious @Joanna would it be ok if I
request a small subset of your 73 million record whopper? The database
structure and any relations, please. I want to see how this is laid
out, if I may please.

Quote from: Joanna on September 05, 2024, 04:18:34 amIs there any
other way to do this ?
 I would use indexes, fetch only a subset of the 73 million records at
a time and paginate Results using LIMIT and OFFSET to fetch data in
smaller chunks. ( How much RAM do you have in your system and what
kind of processor is it? )

----------------------------------------------------------------------
Joanna · 2024-09-07 00:26

Zvoni, when generating data how often do you recommend doing the
commits ? I remember that permanently writing to database was more
time consuming than just inserting. I think I was doing it every
10,000 rows

----------------------------------------------------------------------
egsuh · 2024-09-07 00:55

I'm sorry that I didn't understand the program correctly at the
beginning.

I think extracting distinct values across over all 8 fields (and count
of each value) is the first target.
To get values only (not counts), SQL select distinct f1 from...  and
then selecting distinct values from them again would be the simplest
method. And this takes quite long time.

As the whole number of distinct values does not seem large for now, I
think we can count it in Lazarus.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i          
 valcounts:= TFPGMap<integer, integer>.Create;          
 valcounts.sorted := True;             with sqlquery do begin        
      sql.text := 'select * from table1';                 open;      
        first;                while not eof                   for (f
in fields) do begin                          v := f.asinteger;       
                 if valcounts.find(v, j)                            
then inc(valcounts.data[j])                            else
valcounts.add(v, 1);                    end;                    next;
              end;                close;          end; 
Conceptually this will give all the distinct values and counts.
           
But in reality, simply opening the sqlquery will stop PC (for several
hours at least).

So, I think of opening subsets of table several times, using limit and
offset, for example,

           select ... limit 10000 offset 10000


I hope this approach provides some manageable points.

Once you have the values here, then you may add a new table with these
values, and triggers Zvoni provided.

----------------------------------------------------------------------
silvercoder70 · 2024-09-07 07:38

Putting aside any code related ideas have you tried these
configuration settings...

https://phiresky.github.io/blog/2020/sqlite-performance-tuning/

----------------------------------------------------------------------
Joanna · 2024-09-07 14:53

Thanks for all the information, there is so much to know about sqlite
..

----------------------------------------------------------------------
Zvoni · 2024-09-07 16:55

Quote from: Joanna on September 07, 2024, 02:26:35 amZvoni, when
generating data how often do you recommend doing the commits ? I
remember that permanently writing to database was more time consuming
than just inserting. I think I was doing it every 10,000 rows
It depends, how often data comes in.
Of course, if you get a new insert every 0.1 seconds, it doesn’t make
sense to commit after each insert.
It’s something only you can know