Re: procedure using CURSOR to insert is extremely slow

Hi Zoltan,

is there any particular reason why you don't do a bulk insert as:
insert into target_table
select ... from source_table(s) (with joins etc)

Regards,
Milos

On Thu, Apr 8, 2021 at 1:24 PM Szalontai Zoltán <
szalontai(dot)zoltan(at)t-online(dot)hu> wrote:

> Hi,
>
>
>
> We have a Class db.t2.medium database on AWS.
>
> We use a procedure to transfer data records from the Source to the Target
> Schema.
>
> Transfers are identified by the log_id field in the target table.
>
>
>
> The procedure is:
>
> 1 all records are deleted from the Target table with the actual log_id
> value
>
> 2 a complicated SELECT (numerous tables are joined) is created on the
> Source system
>
> 3 a cursor is defined based on this SELECT
>
> 4 we go trough the CURSOR and insert new records into the Target table
> with this log_id
>
>
>
> (Actually we have about 100 tables in the Target schema and the size of
> the database backup file is about 1GByte. But we do the same for all the
> Target tables.)
>
>
>
> Our procedure is extremely slow for the first run: 3 days for the 100
> tables. For the second and all subsequent run it is fast enough (15
> minutes).
>
> The only difference between the first run and all the others is that in
> the first run there are no records in the Target schema with this log_id.
>
>
>
> It seems, that in the first step the DELETE operation makes free some
> “space”, and the INSET operation in the 4. step can reuse this space. But
> if no records are deleted in the first step, the procedure is extremely
> slow.
>
>
>
> To speed up the first run we found the following workaround:
>
> We inserted dummy records into the Target tables with the proper log_id,
> and really the first run became very fast again.
>
>
>
> Is there any “normal” way to speed up this procedure?
>
> In the production environment there will be only “first runs”, the same
> log_id will never be used again.
>
>
>
>
>
> thank
>
> Zoltán
>
>
>
>
>

--
Milos Babic
http://www.linkedin.com/in/milosbabic