----- Reply message -----
Od: "Kjell Rilbe kjell.rilbe@... [firebird-support]" <firebird-support@yahoogroups.com>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Performance diff between insert...select and for select ... do?
Data: wt., kwi 14, 2015 07:08

Kjell Rilbe kjell.rilbe@... [firebird-support] skrev:

>
> Hi,
>
> I'm writing a utility that will need to do two things for each record in
> an external table and for this purpose I use a for select ... do
> construct in an execute block. I do it this way because external tables
> can't be indexed and I will scan the entire external table anyway.
>
> The two operations are:
> 1. Update one existing record in the target table.
> 2. Insert new record in the same target table.
>
> In "steady state" the target table will contain about 20 million records
> and the external table will contain about 10 thousand records.
>
> But the first time I run this, the target table will be empty and the
> external table will contain about 18 million records. The update will
> never find a record to update during this first execution.
>
> Would I lose a lot of hours if I use the same execute block/for select
> construct the first time? The alternative would be to do a regular
> insert into target table select from externaltable the first time.
>

As a follow-up to this question, my tests with real data showed that the
execute block was *very* much slower than a simple insert from the
external table for the initial import into an empty target table. I
think it was something like 10 minutes vs. 10 hours, give or take...

I also noted that in steady state, the Firebird solution as a whole was
very slow. The thing is that for each run of this utility, I would need
to visit close to 100 % of the records. I ended up tossing Firebird
altogether and implemented a simple text file format instead, and a
load-modify-write pattern, making good use of abundant RAM. Went from
5-10 hours to about 5 minutes.

SQL databases are good for many things, but in this case, they suck (I'm
assuming Firebird is not significantly worse than any other brand in
this case).

Regards,
Kjell