Fix slow schedule_a return for multiple is_active=False committees

[fec-jli]

Raising on committee profile page just hanging on waiting api call return for 'C00567057/2020'.
ex: https://www.fec.gov/data/committee/C00567057/?cycle=2020&tab=raising

api call:
https://api.open.fec.gov/v1/schedules/schedule_a/?api_key=DEMO_KEY&sort_hide_null=false&sort_nulls_last=true&sort=-contribution_receipt_date&per_page=10&committee_id=C00567057&two_year_transaction_period=2020&is_individual=true

Action items:

• identify the why this committee hang there
• fix the bug

Completion criteria:

• api call not freezing

[fec-jli]

There are 4992 rows is_active=false and cycle=2020, the Receipts page keeps spinning
example query:

select * from ofec_committee_history_mv
where is_active =false and cycle IN (2020)

======1)spinning: 2020 / is_active=false (schedule_a with data or without data)=======
C00000638 /2020/ data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00000638&two_year_transaction_period=2020

C00001768 /2020 / no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00001768&two_year_transaction_period=2020

C00003897 /2020 / no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00003897&two_year_transaction_period=2020

C00007666 /2020 / no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00007666&two_year_transaction_period=2020

C00008110 /2020 / data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00008110&two_year_transaction_period=2020

C00009795 /2020 / no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00009795&two_year_transaction_period=2020

C00011239 /2020 / no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00011239&two_year_transaction_period=2020

C00016444 /2020 / data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00016444&two_year_transaction_period=2020

C00033431 /2020 / no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00033431&two_year_transaction_period=2020

C00035683 /2020 / data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00035683&two_year_transaction_period=2020

C00441386 /2020 / no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00441386&two_year_transaction_period=2020

======2)all ok: 2018 / is_active=false (schedule_a with data or without data)=======
C00000018 /2018 (ok) no data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00000018&two_year_transaction_period=2018

C00001776 /2018 / data
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00001776&two_year_transaction_period=2018

======3)all ok: 2022 / is_active=false (schedule_a with data or without data)=======
C00039016 /2022 /data)
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00039016&two_year_transaction_period=2022

C00095059 /2022 /no data (ok)
https://www.fec.gov/data/receipts/?data_type=processed&committee_id=C00095059&two_year_transaction_period=2022

[fec-jli]

by David Z.:
The above query on Prod Aurora readers (inst-1 and inst2) are running fine now (from xx mins to 409 msecs. Execution plan are much better now. I did database pin during the nopeak time for the recent large data update.

[fec-jli]

The root cause for these schedule-a slow queries is the database is doing pin data.
close this issue.

[dzhang-fec]

@fec-jli could you please to generate the exact Query on this. Thanks.
https://api.open.fec.gov/v1/schedules/schedule_a/?api_key=DEMO_KEY&sort_hide_null=false&sort_nulls_last=true&sort=-contribution_receipt_date&per_page=10&committee_id=C00567057&two_year_transaction_period=2020&is_individual=true

[fec-jli]

Thanks @dzhang-fec @hcaofec
based on David’s testing, his suggestions:

1. check committee_id in scheduler-a with two_year_transaction_period=2020
   and is_active=false,
   if return data < 100 rows, set limit = 100 or remove limit

2. Whenever user complain about the slow scheduler-a query,
   db team will pin the corresponding data set.

[fec-jli]

We can replace the below committee_id to reproduce the issue:
C00000638 /2020/ data
C00001768 /2020 / no data
C00003897 /2020 / no data
C00007666 /2020 / no data
C00008110 /2020 / data
C00009795 /2020 / no data
C00011239 /2020 / no data
C00016444 /2020 / data
C00033431 /2020 / no data
C00035683 /2020 / data
C00441386 /2020 / no data

Query:
SELECT disclosure.fec_fitem_sched_a.cmte_id AS disclosure_fec_fitem_sched_a_cmte_id, disclosure.fec_fitem_sched_a.rpt_yr AS disclosure_fec_fitem_sched_a_rpt_yr, disclosure.fec_fitem_sched_a.rpt_tp AS disclosure_fec_fitem_sched_a_rpt_tp, disclosure.fec_fitem_sched_a.image_num AS disclosure_fec_fitem_sched_a_image_num, disclosure.fec_fitem_sched_a.line_num AS disclosure_fec_fitem_sched_a_line_num, disclosure.fec_fitem_sched_a.tran_id AS disclosure_fec_fitem_sched_a_tran_id, disclosure.fec_fitem_sched_a.file_num AS disclosure_fec_fitem_sched_a_file_num, disclosure.fec_fitem_sched_a.cmte_nm AS disclosure_fec_fitem_sched_a_cmte_nm, disclosure.fec_fitem_sched_a.entity_tp AS disclosure_fec_fitem_sched_a_entity_tp, disclosure.fec_fitem_sched_a.entity_tp_desc AS disclosure_fec_fitem_sched_a_entity_tp_desc, disclosure.fec_fitem_sched_a.contbr_id AS disclosure_fec_fitem_sched_a_contbr_id, disclosure.fec_fitem_sched_a.contbr_prefix AS disclosure_fec_fitem_sched_a_contbr_prefix, disclosure.fec_fitem_sched_a.contbr_nm AS disclosure_fec_fitem_sched_a_contbr_nm, disclosure.fec_fitem_sched_a.cmte_tp AS disclosure_fec_fitem_sched_a_cmte_tp, disclosure.fec_fitem_sched_a.org_tp AS disclosure_fec_fitem_sched_a_org_tp, disclosure.fec_fitem_sched_a.cmte_dsgn AS disclosure_fec_fitem_sched_a_cmte_dsgn, disclosure.fec_fitem_sched_a.contbr_nm_first AS disclosure_fec_fitem_sched_a_contbr_nm_first, disclosure.fec_fitem_sched_a.contbr_m_nm AS disclosure_fec_fitem_sched_a_contbr_m_nm, disclosure.fec_fitem_sched_a.contbr_nm_last AS disclosure_fec_fitem_sched_a_contbr_nm_last, disclosure.fec_fitem_sched_a.contbr_suffix AS disclosure_fec_fitem_sched_a_contbr_suffix, disclosure.fec_fitem_sched_a.contbr_st1 AS disclosure_fec_fitem_sched_a_contbr_st1, disclosure.fec_fitem_sched_a.contbr_st2 AS disclosure_fec_fitem_sched_a_contbr_st2, disclosure.fec_fitem_sched_a.contbr_city AS disclosure_fec_fitem_sched_a_contbr_city, disclosure.fec_fitem_sched_a.contbr_st AS disclosure_fec_fitem_sched_a_contbr_st, disclosure.fec_fitem_sched_a.contbr_zip AS disclosure_fec_fitem_sched_a_contbr_zip, disclosure.fec_fitem_sched_a.contbr_employer AS disclosure_fec_fitem_sched_a_contbr_employer, disclosure.fec_fitem_sched_a.contbr_occupation AS disclosure_fec_fitem_sched_a_contbr_occupation, disclosure.fec_fitem_sched_a.clean_contbr_id AS disclosure_fec_fitem_sched_a_clean_contbr_id, disclosure.fec_fitem_sched_a.receipt_tp AS disclosure_fec_fitem_sched_a_receipt_tp, disclosure.fec_fitem_sched_a.receipt_tp_desc AS disclosure_fec_fitem_sched_a_receipt_tp_desc, disclosure.fec_fitem_sched_a.receipt_desc AS disclosure_fec_fitem_sched_a_receipt_desc, disclosure.fec_fitem_sched_a.memo_cd AS disclosure_fec_fitem_sched_a_memo_cd, disclosure.fec_fitem_sched_a.memo_cd_desc AS disclosure_fec_fitem_sched_a_memo_cd_desc, disclosure.fec_fitem_sched_a.contb_receipt_dt AS disclosure_fec_fitem_sched_a_contb_receipt_dt, disclosure.fec_fitem_sched_a.contb_receipt_amt AS disclosure_fec_fitem_sched_a_contb_receipt_amt, disclosure.fec_fitem_sched_a.contb_aggregate_ytd AS disclosure_fec_fitem_sched_a_contb_aggregate_ytd, disclosure.fec_fitem_sched_a.cand_id AS disclosure_fec_fitem_sched_a_cand_id, disclosure.fec_fitem_sched_a.cand_nm AS disclosure_fec_fitem_sched_a_cand_nm, disclosure.fec_fitem_sched_a.cand_nm_first AS disclosure_fec_fitem_sched_a_cand_nm_first, disclosure.fec_fitem_sched_a.cand_nm_last AS disclosure_fec_fitem_sched_a_cand_nm_last, disclosure.fec_fitem_sched_a.cand_m_nm AS disclosure_fec_fitem_sched_a_cand_m_nm, disclosure.fec_fitem_sched_a.cand_prefix AS disclosure_fec_fitem_sched_a_cand_prefix, disclosure.fec_fitem_sched_a.cand_suffix AS disclosure_fec_fitem_sched_a_cand_suffix, disclosure.fec_fitem_sched_a.cand_office AS disclosure_fec_fitem_sched_a_cand_office, disclosure.fec_fitem_sched_a.cand_office_desc AS disclosure_fec_fitem_sched_a_cand_office_desc, disclosure.fec_fitem_sched_a.cand_office_st AS disclosure_fec_fitem_sched_a_cand_office_st, disclosure.fec_fitem_sched_a.cand_office_st_desc AS disclosure_fec_fitem_sched_a_cand_office_st_desc, disclosure.fec_fitem_sched_a.cand_office_district AS disclosure_fec_fitem_sched_a_cand_office_district, disclosure.fec_fitem_sched_a.conduit_cmte_id AS disclosure_fec_fitem_sched_a_conduit_cmte_id, disclosure.fec_fitem_sched_a.conduit_cmte_nm AS disclosure_fec_fitem_sched_a_conduit_cmte_nm, disclosure.fec_fitem_sched_a.conduit_cmte_st1 AS disclosure_fec_fitem_sched_a_conduit_cmte_st1, disclosure.fec_fitem_sched_a.conduit_cmte_st2 AS disclosure_fec_fitem_sched_a_conduit_cmte_st2, disclosure.fec_fitem_sched_a.conduit_cmte_city AS disclosure_fec_fitem_sched_a_conduit_cmte_city, disclosure.fec_fitem_sched_a.conduit_cmte_st AS disclosure_fec_fitem_sched_a_conduit_cmte_st, disclosure.fec_fitem_sched_a.conduit_cmte_zip AS disclosure_fec_fitem_sched_a_conduit_cmte_zip, disclosure.fec_fitem_sched_a.donor_cmte_nm AS disclosure_fec_fitem_sched_a_donor_cmte_nm, disclosure.fec_fitem_sched_a.national_cmte_nonfed_acct AS disclosure_fec_fitem_sched_a_national_cmte_nonfed_acct, disclosure.fec_fitem_sched_a.election_tp AS disclosure_fec_fitem_sched_a_election_tp, disclosure.fec_fitem_sched_a.election_tp_desc AS disclosure_fec_fitem_sched_a_election_tp_desc, disclosure.fec_fitem_sched_a.fec_election_tp_desc AS disclosure_fec_fitem_sched_a_fec_election_tp_desc, disclosure.fec_fitem_sched_a.fec_election_yr AS disclosure_fec_fitem_sched_a_fec_election_yr, disclosure.fec_fitem_sched_a.action_cd AS disclosure_fec_fitem_sched_a_action_cd, disclosure.fec_fitem_sched_a.action_cd_desc AS disclosure_fec_fitem_sched_a_action_cd_desc, disclosure.fec_fitem_sched_a.schedule_type_desc AS disclosure_fec_fitem_sched_a_schedule_type_desc, disclosure.fec_fitem_sched_a.pg_date AS disclosure_fec_fitem_sched_a_pg_date, disclosure.fec_fitem_sched_a.orig_sub_id AS disclosure_fec_fitem_sched_a_orig_sub_id, disclosure.fec_fitem_sched_a.back_ref_tran_id AS disclosure_fec_fitem_sched_a_back_ref_tran_id, disclosure.fec_fitem_sched_a.back_ref_sched_nm AS disclosure_fec_fitem_sched_a_back_ref_sched_nm, disclosure.fec_fitem_sched_a.filing_form AS disclosure_fec_fitem_sched_a_filing_form, disclosure.fec_fitem_sched_a.link_id AS disclosure_fec_fitem_sched_a_link_id, disclosure.fec_fitem_sched_a.contributor_name_text AS disclosure_fec_fitem_sched_a_contributor_name_text, disclosure.fec_fitem_sched_a.contributor_employer_text AS disclosure_fec_fitem_sched_a_contributor_employer_text, disclosure.fec_fitem_sched_a.contributor_occupation_text AS disclosure_fec_fitem_sched_a_contributor_occupation_text, disclosure.fec_fitem_sched_a.is_individual AS disclosure_fec_fitem_sched_a_is_individual, disclosure.fec_fitem_sched_a.memo_text AS disclosure_fec_fitem_sched_a_memo_text, disclosure.fec_fitem_sched_a.two_year_transaction_period AS disclosure_fec_fitem_sched_a_two_year_transaction_period, disclosure.fec_fitem_sched_a.schedule_type AS disclosure_fec_fitem_sched_a_schedule_type, disclosure.fec_fitem_sched_a.increased_limit AS disclosure_fec_fitem_sched_a_increased_limit, disclosure.fec_fitem_sched_a.sub_id AS disclosure_fec_fitem_sched_a_sub_id, disclosure.fec_fitem_sched_a.pdf_url AS disclosure_fec_fitem_sched_a_pdf_url, disclosure.fec_fitem_sched_a.line_number_label AS disclosure_fec_fitem_sched_a_line_number_label, ofec_committee_history_mv_1.idx AS ofec_committee_history_mv_1_idx, ofec_committee_history_mv_1.name AS ofec_committee_history_mv_1_name, ofec_committee_history_mv_1.committee_id AS ofec_committee_history_mv_1_committee_id, ofec_committee_history_mv_1.cycles AS ofec_committee_history_mv_1_cycles, ofec_committee_history_mv_1.treasurer_name AS ofec_committee_history_mv_1_treasurer_name, ofec_committee_history_mv_1.treasurer_text AS ofec_committee_history_mv_1_treasurer_text, ofec_committee_history_mv_1.committee_type AS ofec_committee_history_mv_1_committee_type, ofec_committee_history_mv_1.committee_type_full AS ofec_committee_history_mv_1_committee_type_full, ofec_committee_history_mv_1.filing_frequency AS ofec_committee_history_mv_1_filing_frequency, ofec_committee_history_mv_1.designation AS ofec_committee_history_mv_1_designation, ofec_committee_history_mv_1.designation_full AS ofec_committee_history_mv_1_designation_full, ofec_committee_history_mv_1.organization_type AS ofec_committee_history_mv_1_organization_type, ofec_committee_history_mv_1.organization_type_full AS ofec_committee_history_mv_1_organization_type_full, ofec_committee_history_mv_1.affiliated_committee_name AS ofec_committee_history_mv_1_affiliated_committee_name, ofec_committee_history_mv_1.party AS ofec_committee_history_mv_1_party, ofec_committee_history_mv_1.party_full AS ofec_committee_history_mv_1_party_full, ofec_committee_history_mv_1.state AS ofec_committee_history_mv_1_state, ofec_committee_history_mv_1.street_1 AS ofec_committee_history_mv_1_street_1, ofec_committee_history_mv_1.street_2 AS ofec_committee_history_mv_1_street_2, ofec_committee_history_mv_1.city AS ofec_committee_history_mv_1_city, ofec_committee_history_mv_1.state_full AS ofec_committee_history_mv_1_state_full, ofec_committee_history_mv_1.zip AS ofec_committee_history_mv_1_zip, ofec_committee_history_mv_1.candidate_ids AS ofec_committee_history_mv_1_candidate_ids, ofec_committee_history_mv_1.cycle AS ofec_committee_history_mv_1_cycle, ofec_committee_history_mv_1.cycles_has_financial AS ofec_committee_history_mv_1_cycles_has_financial, ofec_committee_history_mv_1.last_cycle_has_financial AS ofec_committee_history_mv_1_last_cycle_has_financial, ofec_committee_history_mv_1.cycles_has_activity AS ofec_committee_history_mv_1_cycles_has_activity, ofec_committee_history_mv_1.last_cycle_has_activity AS ofec_committee_history_mv_1_last_cycle_has_activity, ofec_committee_history_mv_1.is_active AS ofec_committee_history_mv_1_is_active, ofec_committee_history_mv_1.former_committee_name AS ofec_committee_history_mv_1_former_committee_name, ofec_committee_history_mv_1.former_candidate_id AS ofec_committee_history_mv_1_former_candidate_id, ofec_committee_history_mv_1.former_candidate_name AS ofec_committee_history_mv_1_former_candidate_name, ofec_committee_history_mv_1.former_candidate_election_year AS ofec_committee_history_mv_1_former_candidate_election_yea_1, ofec_committee_history_mv_1.convert_to_pac_flag AS ofec_committee_history_mv_1_convert_to_pac_flag, ofec_committee_history_mv_1.sponsor_candidate_ids AS ofec_committee_history_mv_1_sponsor_candidate_ids, ofec_committee_history_mv_2.idx AS ofec_committee_history_mv_2_idx, ofec_committee_history_mv_2.name AS ofec_committee_history_mv_2_name, ofec_committee_history_mv_2.committee_id AS ofec_committee_history_mv_2_committee_id, ofec_committee_history_mv_2.cycles AS ofec_committee_history_mv_2_cycles, ofec_committee_history_mv_2.treasurer_name AS ofec_committee_history_mv_2_treasurer_name, ofec_committee_history_mv_2.treasurer_text AS ofec_committee_history_mv_2_treasurer_text, ofec_committee_history_mv_2.committee_type AS ofec_committee_history_mv_2_committee_type, ofec_committee_history_mv_2.committee_type_full AS ofec_committee_history_mv_2_committee_type_full, ofec_committee_history_mv_2.filing_frequency AS ofec_committee_history_mv_2_filing_frequency, ofec_committee_history_mv_2.designation AS ofec_committee_history_mv_2_designation, ofec_committee_history_mv_2.designation_full AS ofec_committee_history_mv_2_designation_full, ofec_committee_history_mv_2.organization_type AS ofec_committee_history_mv_2_organization_type, ofec_committee_history_mv_2.organization_type_full AS ofec_committee_history_mv_2_organization_type_full, ofec_committee_history_mv_2.affiliated_committee_name AS ofec_committee_history_mv_2_affiliated_committee_name, ofec_committee_history_mv_2.party AS ofec_committee_history_mv_2_party, ofec_committee_history_mv_2.party_full AS ofec_committee_history_mv_2_party_full, ofec_committee_history_mv_2.state AS ofec_committee_history_mv_2_state, ofec_committee_history_mv_2.street_1 AS ofec_committee_history_mv_2_street_1, ofec_committee_history_mv_2.street_2 AS ofec_committee_history_mv_2_street_2, ofec_committee_history_mv_2.city AS ofec_committee_history_mv_2_city, ofec_committee_history_mv_2.state_full AS ofec_committee_history_mv_2_state_full, ofec_committee_history_mv_2.zip AS ofec_committee_history_mv_2_zip, ofec_committee_history_mv_2.candidate_ids AS ofec_committee_history_mv_2_candidate_ids, ofec_committee_history_mv_2.cycle AS ofec_committee_history_mv_2_cycle, ofec_committee_history_mv_2.cycles_has_financial AS ofec_committee_history_mv_2_cycles_has_financial, ofec_committee_history_mv_2.last_cycle_has_financial AS ofec_committee_history_mv_2_last_cycle_has_financial, ofec_committee_history_mv_2.cycles_has_activity AS ofec_committee_history_mv_2_cycles_has_activity, ofec_committee_history_mv_2.last_cycle_has_activity AS ofec_committee_history_mv_2_last_cycle_has_activity, ofec_committee_history_mv_2.is_active AS ofec_committee_history_mv_2_is_active, ofec_committee_history_mv_2.former_committee_name AS ofec_committee_history_mv_2_former_committee_name, ofec_committee_history_mv_2.former_candidate_id AS ofec_committee_history_mv_2_former_candidate_id, ofec_committee_history_mv_2.former_candidate_name AS ofec_committee_history_mv_2_former_candidate_name, ofec_committee_history_mv_2.former_candidate_election_year AS ofec_committee_history_mv_2_former_candidate_election_yea_2, ofec_committee_history_mv_2.convert_to_pac_flag AS ofec_committee_history_mv_2_convert_to_pac_flag, ofec_committee_history_mv_2.sponsor_candidate_ids AS ofec_committee_history_mv_2_sponsor_candidate_ids
FROM disclosure.fec_fitem_sched_a LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_1 ON disclosure.fec_fitem_sched_a.cmte_id = ofec_committee_history_mv_1.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_1.cycle LEFT OUTER JOIN ofec_committee_history_mv AS ofec_committee_history_mv_2 ON disclosure.fec_fitem_sched_a.clean_contbr_id = ofec_committee_history_mv_2.committee_id AND disclosure.fec_fitem_sched_a.two_year_transaction_period = ofec_committee_history_mv_2.cycle
WHERE disclosure.fec_fitem_sched_a.cmte_id IN ('C00574103') AND disclosure.fec_fitem_sched_a.two_year_transaction_period IN (2020) ORDER BY disclosure.fec_fitem_sched_a.contb_receipt_dt DESC, disclosure.fec_fitem_sched_a.sub_id DESC
LIMIT 30