Re: Help with insert query

[Michel Pelletier <pelletier.michel@xxxxxxxxx>]

On Mon, Apr 1, 2019 at 10:37 AM Glenn Schultz <glenn@xxxxxxxxxx> wrote:

All,

The query below is designed to insert into a table.  This works when I have a single loan which I insert.  However, if remove the part of the where clause of a single loan the insert does not work.  The table fnmloan is a large table with 500mm + rows and the query runs for about 4 hours.  Any idea of how to get this to work?  I am a little stumped since the query works with one loan.

Inserting one row is fast, inserting 500 million rows is going to take quite a bit longer.  I suggest your break your query up into batches, and insert, say, 1 million rows at a time.  Also it might be a good idea to drop your indexes on the target table and re-create them after you do the bulk insert, and also do an 'ANALYZE' on the target table after you have inserted all the records.

-Michel

 

Glenn

SET max_parallel_workers_per_gather = 8;

SET random_page_cost = 1;

truncate fnmloan_balance;

insert into fnmloan_balance (

fctrdt, loanseqnum, secmnem, beginbal, scheduled,endbal,smm

)

select

fnmloan.fctrdt

,fnmloan.loanseqnum

,fnmloan.secmnem

--,fnmloan.orignoterate

--,fnmloan.loanage

--,fnmloan.origloanamt

,fnmloan.currrpb as beginbal

,round(nullif(schedprin(cast(fnmloan.orignoterate as numeric), 

  cast(fnmloan.remterm - 1 as numeric), 

  cast(fnmloan.currrpb as numeric)),4)) as scheduled

,coalesce(endbal.currrpb,0) as endbal

,abs(round(

  cast((fnmloan.currrpb - coalesce(endbal.currrpb,0) - round(nullif(schedprin(cast(fnmloan.orignoterate as numeric), 

  cast(fnmloan.remterm - 1 as numeric), 

  cast(fnmloan.currrpb as numeric)),4)) )/(fnmloan.currrpb - round(nullif(schedprin(cast(fnmloan.orignoterate as numeric), 

  cast(fnmloan.remterm - 1 as numeric), 

  cast(fnmloan.currrpb as numeric)),4)) ) as numeric)

  ,4)) as SMM

from

(

 select * from fnmloan 

 where 

 fctrdt < '03-01-2019'

 and 

 loanseqnum = '5991017042'

) as fnmloan

left outer join

(select

fctrdt - interval '1 month' as fctrdt

,loanseqnum

,orignoterate

,loanage

,origloanamt

,currrpb

from fnmloan

) as endbal

on fnmloan.loanseqnum = endbal.loanseqnum

and fnmloan.fctrdt = endbal.fctrdt