Query Performance

Database

Query Performance

Hi All,

Below mentioned is my query. The record count is approx 1 million rows. It is running for hours :-( Can anybody tell me how to optimzie it . Would be of great help to me.

insert into abc.loss_and_recovery(facility_num, utilization_sequence_num, source_system_id, source_system_facility_id,
source_system_utilization_num, allocator_facility_num, credit_system_id, loan_system_id,
customer_SOR_bank_num, orig_transaction_code, transaction_code, transaction_amt, booking_unit,
uca_line_num, obligor_num, obligation_id, credit_taker_gci, post_date, effective_date, period_date,
business_date, as_of_date, last_update_event_ts, last_update_user_id, pims_facility_key, currency_code,
old_bac_posting_unit, old_bac_uca_line)

select
case when s.facilityNum is null then 0 end,
case when s.utilizationSequenceNum is null then 0 end,
'CORE', /* source_system_id */
case when s.sourceSystemFacilityID is null then' ' end,
case when s.sourceSystemUtilizationNum is null then(case when i.sourceSystemUtilizationNum is null then ' ' end) end,
0, /* allocator facility num */
case when s.sourceSystemID is null then ' ' end, /* source_system_id */
case when s.loanSystem is null then ' ' end,
case when s.postingBank is null then ' ' end,
case when s.nativeTransactionCode is null then ' ' end,
case when s.transactionCode is null then ' ' end,
case when s.coreAmount is null then 0.00 end,
case when (s.postingBank + s.postingCostCenter) is null then ' ' end,
case when s.glAccount is null then ' ' end,
case when s.obligorNumber is null then ' 'end,
case when s.obligationNumber is null then ' ' end,
case when i.borrowerGCI is null then (case when s.borrowerGCI is null then ' ' end) end,
case when s.postingDate is null then s.closeDate end, /* post_date */
case when s.transactionDate is null then s.closeDate end, /* effective_date */
s.closeDate, /* period_date */
'2006-06-12', /* business_date */
'2006-06-12', /* as_of_date */
s.lastUpdateEventTS,
'LOAD',
s.pimsFacilityKey,
s.currencyCode,
s.oldBACpostingUnit,
s.oldBACuca
from database.ind i left outer join database.report s on
s.linkIndicatives = i.linkIndicatives
and s.dateInactive is null
and s.closeDate >= '1999-12-31'
and i.dateInactive is null
and i.closeDate is not null /* monthend indicatives */
3 REPLIES
Junior Contributor

Re: Query Performance

Hi Mahek,
most of your CASEs are wrong, there's no else, so they will result in just two values: 0/blank and NULL.

The target table probably has a PI on one of those columns and is a SET table.
All rows hash to the same AMP, which is doing duplicate row checks and therefore there's 1 AMP running on 100% CPU.

So cancel the query and rewrite the CASEs:
case when s.facilityNum is null then 0 end
-> COALESCE(s.facilityNum, 0)

Btw, it is recommended to check the result set of the select before you run an update.

Dieter

Re: Query Performance

Hi Dieter,

Appreacite your response. I chnaged the query like this:
FIrst of all it is giving me an error: "The format or data contains the bad data"

select
COALESCE(s.facilityNum, 0),
COALESCE(s.utilizationSequenceNum,0),
'CORE', /* source_system_id */
COALESCE(s.sourceSystemFacilityID,' '),
COALESCE(s.sourceSystemUtilizationNum,COALESCE(i.sourceSystemUtilizationNum,' ')),
0, /* allocator facility num */
COALESCE(s.sourceSystemID,' '),
COALESCE(s.loanSystem,' '),
COALESCE(s.postingBank,' '),
COALESCE(s.nativeTransactionCode,' '),
COALESCE(s.transactionCode,' '),
COALESCE(s.coreAmount,0.00),
COALESCE((s.postingBank + s.postingCostCenter),' '),
COALESCE(s.glAccount,' '),
COALESCE(s.obligorNumber,' '),
COALESCE(s.obligationNumber,' '),
COALESCE( i.borrowerGCI,COALESCE(s.borrowerGCI,' ')),
COALESCE(s.postingDate ,s.closeDate), /* post_date */
COALESCE(s.transactionDate, s.closeDate), /* effective_date */
s.closeDate, /* period_date */
'2006-06-12', /* business_date */
'2006-06-12', /* as_of_date */
s.lastUpdateEventTS,
'LOAD',
s.pimsFacilityKey,
s.currencyCode,
s.oldBACpostingUnit,
s.oldBACuca
from Database.ind i left outer join Database.rep s on
s.linkIndicatives = i.linkIndicatives
and s.dateInactive is null
and s.closeDate >= '1999-12-31'
and i.dateInactive is null
and i.closeDate is not null /* monthend indicatives */
Junior Contributor

Re: Query Performance

Hi Mahek,
it's a typecast error, probably casting a varchar to a numeric value.

Check the column definitions...

Btw, there's no need to nest coalesce:
COALESCE( i.borrowerGCI,COALESCE(s.borrowerGCI,' '))
-> COALESCE( i.borrowerGCI, s.borrowerGCI, ' ')

Dieter