Hi, Why not merge?
Regards, Karol Bieniaszewski ----- Reply message ----- Od: "Kjell Rilbe [email protected] [firebird-support]" <[email protected]> Do: <[email protected]> Temat: [firebird-support] Performance diff between insert...select and for select ... do? Data: wt., kwi 14, 2015 07:08 Kjell Rilbe [email protected] [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
