Commit in a long running inter into t1 select

[Rand Random]

Is it possible to commit periodically in a insert into t1 select?

I am currently trying to insert ~10million records and the long running transaction seems to be problematic.

Maybe with the help of a cursor? Or FOR SELECT?

Also I would like to know if there are any performance related settings I can tweek additionally to disableing indexes and setting TransactionBehaviour.NoAutoUndo.

I wouldn't care about any other connection reading/writing into any tables while I am running this insert into t1 select statement, so I wondered if there could be any setting to tell firebird ignore any possible conflict of data and just "brute force" the data in the table.

[Dimitry Sibiryakov]

Rand Random wrote 03.11.2021 17:59:
> I am currently trying to insert ~10million records and the long running
> transaction seems to be problematic.

What exactly problem do you observe? Which Firebird version?

> Also I would like to know if there are any performance related settings I can tweek additionally to disableing indexes and setting TransactionBehaviour.NoAutoUndo.

Do you disable indexes in the same transaction where the insert is performed?

--
WBR, SD.

[Karol Bieniaszewski]

First fix your select performance then do insert.

Did you checked how long this select is run without putting it into INSERT INTO ?

 

For plain table with 20 columns i have 100k rows per sec inserted.

So 10 million witll take only ~~1,5 minute.

 

But it is table with 2 indexes only and non triggers.

 

You have more options. Add to this select simple SELECT FIRST 10000 SKIP XXX statement and then split it into multiple inserts and do commit per part.

To catch conflicts maybe better is MERGE statement not INSERT INTO?

 

regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/384de286-1557-478f-924d-64b5d591a8e8n%40googlegroups.com.

 

[Dimitry Sibiryakov]

Rand Random wrote 03.11.2021 18:38:
> Firebird 4.0 embedded.
> Have a look at the attached file.
> I am currently running the statement and see the following usage of memory and
> diskspace.
> I believe that the memory usage is that high because it is still uncommited and
> only in the active transaction.

12 gigabytes of workspace? It looks like a bug in your application or
Firebird misconfiguration. Try to use separate Firebird server instead of
embedded mode to find out which is the case.

--
WBR, SD.

[Tomasz Tyrakowski]

On 03.11.2021 at 18:46, Karol Bieniaszewski wrote:
> First fix your select performance then do insert.
> Did you checked how long this select is run without putting it into INSERT INTO ?
>
> For plain table with 20 columns i have 100k rows per sec inserted.
> So 10 million witll take only ~~1,5 minute.
>
> But it is table with 2 indexes only and non triggers.
>
> You have more options. Add to this select simple SELECT FIRST 10000 SKIP XXX statement and then split it into multiple inserts and do commit per part.
> To catch conflicts maybe better is MERGE statement not INSERT INTO?
>

In addition to what Karol wrote, make sure splitting the insert into
several transactions in your scenario is a good idea. Suppose you commit
every 100k rows and it crashes for some reason during the 6th batch. You
end up with half the rows inserted and half not.
At first I thought about directing you to the manual on IN AUTONOMOUS
TRANSACTION DO ..., but after a bit of consideration I'd advice against
it as well (for the same reason).
Just like Karol wrote: check the performance of the select first (e.g.
select the count of what you plan to insert to make sure all rows are
processed) and optimize it if needed. If this is an isolated environment
and you can risk inserting in batches, Karol's advice with first-skip
should be sufficient.

regards
Tomasz

[Dimitry Sibiryakov]

Tomasz Tyrakowski wrote 03.11.2021 19:00:
>
> In addition to what Karol wrote, make sure splitting the insert into several
> transactions in your scenario is a good idea. Suppose you commit every 100k rows
> and it crashes for some reason during the 6th batch. You end up with half the
> rows inserted and half not.

For INSERT...SELECT it is surely bad idea.

--
WBR, SD.

[Tomasz Tyrakowski]

On 03.11.2021 at 19:02, Dimitry Sibiryakov wrote:
>> In addition to what Karol wrote, make sure splitting the insert into
>> several transactions in your scenario is a good idea. Suppose you
>> commit every 100k rows and it crashes for some reason during the 6th
>> batch. You end up with half the rows inserted and half not.
>
>   For INSERT...SELECT it is surely bad idea.

Agreed. Overal performance will be worse (plus the risk I mentioned
earlier). The only advantage is that you can show a nice colorful
progress bar with completion percentage ;)

BR
Tomasz

[Karol Bieniaszewski]

This is what for OP ask 😉 to commit per some part.

But first he must check if the SELECT itself is not bottleneck.

If SELECT is, then things will be slower x times

 

regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/ec655329-8703-1b9f-2703-1886c577f8f7%40ibphoenix.com.

 

[Rand Random]

Thanks, for the help.

The suggestion of @WBR, SD finally showed the real culprit of my 12gb of memory comsumption.

Though I must say I feel embarrassed that I have never thought about simply running it on a real firebird server to see that my own code is producing this much memory, so thx for this, I will keep it in mind if I face different issues.

Luckily it was an easy fix.

And as @Karol Bieniaszewski suggested I had a deeper look into my select statement, where I found some things I could tweak.