Re: Odp: [firebird-support] Performance diff between insert...select and for select ... do?

[Kjell Rilbe kjell.ri...@datadia.se [firebird-support]]

'=?utf-8?B?bGl2aXVzbGl2aXVzQHBvY3p0YS5vbmV0LnBs?=' 
liviusliv...@poczta.onet.pl [firebird-support] skrev:
>
> Why not merge?

Yes, indeed. Might have been worth a shot, but I suspect it would have 
been no more than marginally better. Even merge does have to do an index 
lookup for each record to check if one exists or not, doesn't it? Or 
does it ignore the index lookup and simply add a new record version 
regardless?

Furthermore, even the straight insert (with PK and indices 
dropped/inactive) was orders of magnitude slower than the proprietary 
text file I resorted to (no surprise really).

Regards,
Kjell
-- 
--
Kjell Rilbe
DataDIA AB
E-post: kjell.ri...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Re: Odp: [firebird-support] Performance diff between insert...select and for select ... do?

[Kjell Rilbe kjell.ri...@datadia.se [firebird-support]]

'=?utf-8?B?bGl2aXVzbGl2aXVzQHBvY3p0YS5vbmV0LnBs?=' 
liviusliv...@poczta.onet.pl [firebird-support] skrev:
>
> Why not merge?
>
Yes, indeed. Might have been worth a shot, but I suspect it would have 
been no more than marginally better. Even merge does have to do an index 
lookup for each record to check if one exists or not, doesn't it? Or 
does it ignore the index lookup and simply add a new record version 
regardless?

Furthermore, even the straight insert (with PK and indices 
dropped/inactive) was orders of magnitude slower than the proprietary 
text file I resorted to (no surprise really).

Regards,
Kjell

Odp: [firebird-support] Performance diff between insert...select and for select ... do?

['liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

Why not merge?

Regards,
Karol Bieniaszewski

- Reply message -
Od: "Kjell Rilbe kjell.ri...@datadia.se [firebird-support]" 

Do: 
Temat: [firebird-support] Performance diff between insert...select and for 
select ... do?
Data: wt., kwi 14, 2015 07:08
Kjell Rilbe kjell.ri...@datadia.se [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

Re: [firebird-support] Performance diff between insert...select and for select ... do?

[Kjell Rilbe kjell.ri...@datadia.se [firebird-support]]

Kjell Rilbe kjell.ri...@datadia.se [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

Re: [firebird-support] Performance diff between insert...select and for select ... do?

[Kjell Rilbe kjell.ri...@datadia.se [firebird-support]]

Mark Rotteveel m...@lawinegevaar.nl [firebird-support] skrev:
>
> On 13-2-2015 14:55, Kjell Rilbe kjell.ri...@datadia.se
> [firebird-support] wrote:
> > 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.
>
> Maybe you can use MERGE, it uses a source, a target and can
> conditionally insert, update (and delete) record? I assume your target
> table isn't external and is indexed.
>

Thanks, that might be worth looking into. I suspect it won't work since 
I will need to do BOTH update AND insert for each external record, but I 
haven't checked what merge can do yet... :-)

Kjell
> -- 
> --
> Kjell Rilbe
> DataDIA AB
> E-post: kjell.ri...@datadia.se
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64

Re: [firebird-support] Performance diff between insert...select and for select ... do?

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 13-2-2015 14:55, Kjell Rilbe kjell.ri...@datadia.se 
[firebird-support] wrote:
> 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.

Maybe you can use MERGE, it uses a source, a target and can 
conditionally insert, update (and delete) record? I assume your target 
table isn't external and is indexed.

Mark

-- 
Mark Rotteveel

[firebird-support] Performance diff between insert...select and for select ... do?

[Kjell Rilbe kjell.ri...@datadia.se [firebird-support]]

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. My 
guess is that the unecessary update will take increasingly long to 
execute as the target table grows towards 18 milliion records, wasting a 
lot of time for me, even if a suitable index is present.

Just to give a measure of the system's performance as it is, a simple 
update on one column for 18 million records in the target table 
currently seems to take about 6 hours. But I could drop a couple of 
indices and perhaps I should bump up the page size from 4k to 8k or 16k.

Regards,
Kjell
-- 

Kjell Rilbe
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

DataDIA AB
Ulvsundavägen 106
168 67 Bromma
www.datadia.se 
08-514 905 90

Företagskontakt.se  - urval av företag 
och kontaktinformation
Personkontakt.se  - urval av hushållsadresser