April 30, 2008 at 1:48 am
The process is as follow,
The destination table is truncated and indexes are dropped before loading and after data being inserted we re-create the indexes.
Before this, a view extracts data from more than 22 tables from a staging database and tries to insert this data in the destination table.
it used to take 12-15 mins, but since yesterday loading one particular table never completes. While loading, the database is set to Simple recovery. There are no blocking. It's part of a daily batch thats loads 6 GB of data everyday. But while loading on particular table it's just keep running for hours. I tried rebuilding the indexes and re-starting the SQL Server but of no use.
Any help is much appreciated as this production batch job.
Thanks in advance.
April 30, 2008 at 5:06 am
[font="Verdana"]
...Before this, a view extracts data from more than 22 tables from a staging database and tries to insert this data in the destination table...
I would suggest, optimize the view first. Fetching data from 22 tables and it takes 15+ mins, it does not make any sense. View is the base for your optimization.
If possible, can you post the query?
Mahesh
[/font]
MH-09-AM-8694
April 30, 2008 at 5:48 am
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER view [dbo].[v_udb_extract_contract]
as
select
'I' as inp_source_code
,LS_MASTER.CONTRACT_NO as contract_id
,LS_BILLING.REMIT_TO as remit_to_cd
,LS_AADDR.AR_NAME as customer_name
,LS_AADDR.AR_CITY as ar_city
,LS_AADDR.AR_STATE as ar_state
,LS_AADDR.AR_ZIP as ar_zip
,LS_AADDR.AR_ADDRESS1 as ar_address1
,LS_AADDR.AR_ADDRESS2 as ar_address2
,LS_MASTER.CR_ATTG_NAME as contact_name
,Case When LS_MASTER.CR_ATTG_PHONE is null Then null
Else dbo.fn_remove_non_numeric(LS_MASTER.CR_ATTG_PHONE)
End as contact_phone
,LS_BILLING.INV_DAYS as lead_inv_days
,LS_BILLING.INV_DUE_DAY as inv_due_day
,LS_BILLING.INVOICE_CODE as invoice_code
,LS_BILLING.CBR as cbr
,isnull(LS_MASTER.GROSS_FINANCE,0) - isnull(LS_INCOME.CTD_FINANCE,0) as unearned_fin_inc_bal
,LS_INCOME.NEXT_FINANCE as curr_mth_fin_inc
,LS_INCOME_TABLE.FIN_INCOME as next_mth_fin_inc
,LS_INCOME.CTD_RESIDUAL as ctd_residual
,LS_INCOME.NEXT_RESIDUAL as curr_mth_res_inc
,LS_INCOME_TABLE.RES_INCOME as next_mth_res_inc
,isnull(LS_MASTER.IDC,0) - isnull(LS_INCOME.CTD_IDC,0) as unearned_idc
,LS_INCOME.NEXT_IDC as curr_mth_idc
,LS_INCOME_TABLE.IDC_INCOME as next_mth_idc
,isnull(LS_BILLING.STATE_TAX_RATE,0) + isnull(LS_BILLING.CNTY_TAX_RATE,0) + isnull(LS_BILLING.CITY_TAX_RATE,0) + isnull(LS_BILLING.TCNTY_TAX_RATE,0) + isnull(LS_BILLING.TCITY_TAX_RATE,0) as blended_sales_tax_rate
,LS_MISC_INVD_D1.MISC_AMT as misc_due
,LS_MASTER.PROGRAM_TYPE as prog_type
,LS_MASTER.MANAGERS_RESIDUAL as mgr_residual
,LS_MISC_REP_D1.future_inc_in_rent as future_inc_in_rent
,LS_MASTER.SALVAGE_VALUE as salvage_value
,0 as renewals_billed_unpaid
,re_master.R_PYMTS_RECEIVED as renewals_paid
,null as broker_id
,LS_MASTER.CUST_CREDIT_ACCT as ccan
,LS_MASTER.CR_SCORING as credit_score
,'D' as fasb_class_code
,LS_BILLING.INCOME_METHOD as inc_method
,LS_BILLING.LATE_CHARGE_CODE as late_chg_cd
,LS_INCOME.NON_ACCRUAL_AFTER as days_before_non_accrual_begins
,LS_INCOME.SUSPEN_CODE as non_accru_code
,LS_MASTER.CONTRACT_STATUS as contract_status
,LS_MASTER.DEALER_SALESMAN as dealer_salesman
,LS_MASTER.AUTH_DECISION as cr_officer_id
,LS_MASTER.DEALER as dealer_id
,LS_BILLING.LEASE_TYPE as lease_type
,LS_MASTER.DEALER_REGION as dealer_region
,Case LS_BILLING.LEASE_TYPE When 'TL' Then '2020' Else '2010' End as product_code
,'0049' as business_unit_code
,AS_MASTER_D1.AS_RECOURSE_CODE as recourse_code
,LS_MASTER.GL_BOOKING_DATE as gl_booking_date
,LS_MASTER.BOOKING_DATE as booking_date
,LS_MASTER.SYSTEM_DATE as into_system_date
,LS_MASTER.TERM_DATE as term_date
,LS_BILLING.LAST_PYMT_DATE as last_pmt_date
,LS_INCOME.SUSPEN_DATE as non_accrl_date
,LS_BILLING.ACTIV_DATE as active_date
,LS_BILLING.PAID_TO_DATE as next_pmt_date
,RE_MASTER.R_RENEWAL_STATUS as renewal_status
,'L' as lse_loan_ind
,LS_BILLING.FIRST_PYMT_DATE as first_pymt_date
,LS_BILLING.VARIABLE_PYMT_CODE as variable_pymt_code
,RE_MASTER.R_RENEWAL_TYPE as renewal_type
,LS_USER.UM_ALPHA_FIELD4 as lessor_code
,LS_BILLING.INVOICE_DESC as equip_desc
,LS_BILLING.PYMTS_INVOICED as num_pymts_invoiced
,DATEDIFF(mm,LS_PARAM_CONTROL.RUN_DATE,LS_MASTER.TERM_DATE) - 1 as pymts_to_invoice
,LS_BILLING.NUM_OF_ASSETS as num_of_assets
,isnull(LS_MASTER.ENDING_PYMTS_ADV,0) as ending_pymts_adv
,datediff(dd,LS_BILLING.LAST_PYMT_DATE ,LS_PARAM_CONTROL.RUN_DATE) as unpaid_days
,LS_MASTER.EQUIPMENT_COST as equip_cost
,isnull(LS_MASTER.GROSS_FINANCE,0) + isnull(LS_MASTER.RESIDUAL,0) as unearn_inc_orig_incl_residual
,LS_MASTER.SEC_DEPOSIT as sec_deposit
,isnull(LS_MASTER.GROSS_FINANCE,0) + isnull(LS_MASTER.RESIDUAL,0) - isnull(LS_INCOME.CTD_FINANCE,0) - isnull(LS_INCOME.CTD_RESIDUAL,0) as unearn_inc
,1200 * isnull(LS_INCOME_TABLE.PRICING_YIELD,0) as yield_incl_residual_idc
,LS_BILLING.GROSS_CONTRACT as gross_contract,isnull(LS_INCOME.MTD_FINANCE,0) + isnull(LS_INCOME.MTD_RESIDUAL,0) as combined_inc_mtd
,LS_BILLING.LATE_CHRGS as late_chg_due
,LS_MASTER.LS_NET_INVEST as net_invest_orig
,isnull(LS_BILLING.PAST_1,0) + isnull(LS_BILLING.PAST_31,0) + isnull(LS_BILLING.PAST_61,0) + isnull(LS_BILLING.PAST_91,0) as past_due_excl_stax
,isnull(LS_BILLING.CONTRACT_PYMT,0) as base_rent_pmt_excl_var
,isnull(LS_BILLING.STATE_TAX_AMT,0) + isnull(LS_BILLING.CNTY_TAX_AMT,0) + isnull(LS_BILLING.CITY_TAX_AMT,0) + isnull(LS_BILLING.TCNTY_TAX_AMT,0) + isnull(LS_BILLING.TCITY_TAX_AMT,0) as taxes_invoiced
,isnull(LS_BILLING.PAST_1,0) + isnull(LS_BILLING.PAST_31,0) + isnull(LS_BILLING.PAST_61,0) + isnull(LS_BILLING.PAST_91,0) + isnull(LS_BILLING.CURRENT_RENTAL,0) as total_rent_pmts_due_excl_stax
,LS_MASTER.RESIDUAL as residual
,LS_MASTER.IDC as idc
,LS_BILLING.CBR as cbr_balance
,isnull(LS_BILLING.PAST_1,0) + isnull(LS_BILLING.PAST_31,0) + isnull(LS_BILLING.PAST_61,0) + isnull(LS_BILLING.PAST_91,0) + isnull(LS_BILLING.CURRENT_RENTAL,0) + isnull(LS_BILLING.STATE_TAX_AMT,0) + isnull(LS_BILLING.CNTY_TAX_AMT,0) + isnull(LS_BILLING.CITY_TAX_AMT,0) + isnull(LS_BILLING.TCNTY_TAX_AMT,0) + isnull(LS_BILLING.TCITY_TAX_AMT,0) as total_rent_pmts_due_incl_stax
,AS_GAIN_LOSS_D1.SUM_GL_TOTAL_RECEIVED as disp_recd
,isnull(LS_OI_CHARGES_D1.TOTAL_PAST_DUE,0) as outstanding_open_items
,LS_BILLING.PAST_1 as past_due_1_30
,LS_BILLING.PAST_31 as past_due_31_60
,LS_MASTER.PROV_LOSS as loss_provision
,AS_UPFRONT_TAX_D1.ut_amt as upfront_tax_paid
,isnull(LS_INCOME.YTD_FINANCE,0) + isnull(LS_INCOME.YTD_RESIDUAL,0) as combined_inc_ytd
,LS_BILLING.PAST_61 as past_due_61_90
,LS_BILLING.PAST_91 as past_due_91plus
,LS_GAIN_LOSS_D1.SUM_GL_BOOK_GAIN_LOSS as gain_loss_ytd
,isnull(LS_INCOME.YTD_FINANCE,0) + isnull(LS_INCOME.YTD_RESIDUAL,0) as remain_combined_inc_ytd
,RE_MASTER.R_YTD_INCOME as renewal_inc_ytd
,LS_BILLING.BILLING_CYCLE as invoice_cycle
,RE_MASTER.R_CONTRACT_TERM as renewal_term
,LS_BILLING.TIMES_DELIN as times_delin_0_30
,LS_BILLING.DELIN_31 as times_delin_31_60
,LS_BILLING.DELIN_61 as times_delin_61_90
,LS_BILLING.DELIN_91 as times_delin_91plus
,LS_BILLING.LATE_CHRGS_WAIVED as late_chgs_waived
,LS_BILLING.LATE_CHRGS as late_chgs_due
,LS_INVOICE.LAST_INV_DATE as last_inv_date
,LS_INVOICE.LAST_INV_AMT as last_inv_amt
,LS_INVOICE.LAST_DUE_DATE as last_inv_due_date
,LS_BILLING.INVOICING_DATE as next_inv_date
,DATEADD(dd,LS_BILLING.INV_DAYS,LS_BILLING.INVOICING_DATE) as next_inv_due_date
,LS_INVOICE.LAST_INV_NO as last_inv_number
--,LS_INVOICE.LAST_PAY_AMT as last_pmt_amt
,ls_ctd_pymthist_d1.h_payment_amount last_pmt_amt
,LS_MASTER.DISP_DATE as disp_date
,'N' as address_chg_flag
,LS_MASTER.CONTRACT_TERM as orig_term
,LS_MASTER.LEAD_BANK as securitization_id
,Case isnull(LS_MASTER.MUNICIPAL_LEASE,0) When 0 Then 'N' else 'Y' end as municipal_flag
,LS_BILLING.LINK_CODE as link_code
,LS_USER.UM_ALPHA_NUM2 as app_no
,LS_GAIN_LOSS_D2.DISPOSITION_CODE as disposition_code
,Case isnull(LS_MASTER.PRIVATE_LABEL,0) When 0 Then 'N' else 'Y' end as private_label_indicator
,LS_MASTER.CUST_ID as old_contract_number
,LS_MASTER.LEGAL_STATUS as legal_status
,LS_BILLING.DELIN_STATUS_CODE as delin_status_code
,Case isnull(LS_NEW_FIELDS.NMF_NON_NOTIFICATION,0) When 1 Then 'Y' else 'N' end as major_account_flag
,LS_MASTER.M_DEF_COLLECTOR as default_collector
,LS_MASTER.SIC_CODE as sic_code
,LS_BILLING.STATE_TAX_RATE as State_Tax_Rate
,LS_BILLING.CNTY_TAX_RATE as County_Tax_Rate
,LS_BILLING.CITY_TAX_RATE as City_Tax_Rate
,LS_BILLING.TCNTY_TAX_RATE as Transit_County_Rate
,LS_BILLING.TCITY_TAX_RATE as Transit_City_Rate
,LS_BILLING.STATE_MISC_TAX_RATE as State_Misc_Rate
,LS_BILLING.CNTY_MISC_TAX_RATE as County_Misc_Rate
,LS_BILLING.CITY_MISC_TAX_RATE as City_Misc_Rate
,LS_BILLING.TCNTY_MISC_TAX_RATE as Transit_County_Misc_Rate
,LS_BILLING.TCITY_MISC_TAX_RATE as Transit_City_Misc_Rate
,LS_USER.UM_ALPHA_FIELD9 as booked_by
,LS_MASTER.INSURANCE_CODE as Insurance_Code
,LS_MASTER.INCOME_START_DATE as income_start_date
,LS_MASTER.REGION as region_id
,PARAMETER_D1.region_name as region_name
,LS_MASTER.M_DEF_COLLECTOR as collector_id
,LS_MASTER.NET_RESERVE as net_reserve
/* Not needed
,Case When
isnull(LS_BILLING.STATE_TAX_RATE,0) + isnull(LS_BILLING.CNTY_TAX_RATE,0) +
isnull(LS_BILLING.CITY_TAX_RATE,0) +
isnull(LS_BILLING.TCITY_TAX_RATE,0) +
isnull(LS_BILLING.TCNTY_TAX_RATE,0) +
isnull(LS_BILLING.STATE_MISC_TAX_RATE,0) +
isnull(LS_BILLING.CNTY_MISC_TAX_RATE,0) +
isnull(LS_BILLING.CITY_MISC_TAX_RATE,0) +
isnull(LS_BILLING.TCNTY_MISC_TAX_RATE,0) +
isnull(LS_BILLING.TCITY_MISC_TAX_RATE,0)
> 0 Then 'Y' Else 'N' End as tax_exempt_code
*/
,LS_GAIN_LOSS_D2.DISPOSITION_DESC as termination_description
,LS_MASTER.MRKTNG_REP as marketing_rep_id
,LS_INCOME.CTD_FINANCE as ctd_finance
,LS_BILLING.PYMT_OPTION as pymt_option
,LS_MASTER.M_INS_CARRIER_NBR as insurance_carrier
,Case When LS_BILLING.ACTIV_DATE > LS_PARAM_CONTROL.RUN_DATE
Then LS_MASTER.contract_term
--When LS_PARAM_CONTROL.RUN_DATE > LS_MASTER.TERM_DATE
--Then 0
Else LS_MASTER.contract_term -
datediff(mm,LS_BILLING.ACTIV_DATE,LS_PARAM_CONTROL.RUN_DATE)
--((year(LS_PARAM_CONTROL.RUN_DATE) - year(LS_BILLING.ACTIV_DATE)) * 12) +
--(month(LS_PARAM_CONTROL.RUN_DATE) - month(LS_BILLING.ACTIV_DATE)) - 1
End as remaining_term
,LS_MISC_INVD_D1.MISC_INVD_TAXES as misc_billable_taxes
,LS_BILLING.LATE_CHRGS as late_charge_tax
,PARAMETER_D2.CURRENCY_CODE as currency_code
,0 as operating_unit_id
,0 as business_line_id
,Round((Case LS_BILLING.INCOME_METHOD When 'D' Then 36500 Else 1200 End) * LS_INCOME_TABLE.COMBINED_IRR,7) as annual_combined_irr_unrounded
--NEW
,LS_BILLING.BRANCH as branch
,LS_MASTER.PRODUCT_LINE as product_line
,LS_MASTER.LS_NET_INVEST as LS_NET_INVEST
,LS_BILLING.CURRENT_RENTAL as CURRENT_RENTAL
,isnull(LS_BILLING.PAST_1,0) + isnull(LS_BILLING.PAST_31,0) +
isnull(LS_BILLING.PAST_61,0) + isnull(LS_BILLING.PAST_91,0) +
isnull(LS_BILLING.LATE_CHRGS,0) + isnull(LS_BILLING.MISC_OVERDUE,0) as total_past_due
,LS_MASTER.LOCAL_SIC_CODE as LOCAL_SIC_CODE
,LS_MASTER.RELATIONSHIP as RELATIONSHIP
,LS_MASTER.CONTRACT_TERM as CONTRACT_TERM
,LS_MASTER.CUST_ID as CUST_ID
,LS_BILLING.PYMTS_ARREARS as PYMTS_ARREARS
,LS_INCOME_TABLE.BLENDED_YIELD as BLENDED_YIELD
,LS_INCOME_TABLE.PRICING_YIELD as PRICING_YIELD
,LS_MASTER.BUSINESS_SEGMENT as BUSINESS_SEGMENT
,LS_INCOME.COST_OF_FUNDS_RATE as COST_OF_FUNDS_RATE
,LS_USER.UM_USER_DATE1 as UM_USER_DATE1
,LS_USER.UM_USER_DATE2 as UM_USER_DATE2
,LS_USER.UM_USER_DATE3 as UM_USER_DATE3
,LS_CADDR.CUST_ADDRESS1 as CUST_ADDRESS1
,LS_CADDR.CUST_ADDRESS2 as CUST_ADDRESS2
,LS_CADDR.CUST_ADDRESS3 as CUST_ADDRESS3
,LS_AADDR.AR_ADDRESS3 as AR_ADDRESS3
,LS_CADDR.CUST_ZIP as CUST_ZIP
,LS_CADDR.CUST_CITY as CUST_CITY
,LS_GAIN_LOSS_D1.SUM_GL_UNEARNED_RESIDUAL as GL_UNEARNED_RESIDUAL
,LS_GAIN_LOSS_D1.SUM_GL_TAX_GAIN_LOSS as GL_TAX_GAIN_LOSS
,LS_USER.UM_ALPHA_NUM1 as UM_ALPHA_NUM1
,LS_MASTER.GROSS_FINANCE as GROSS_FINANCE
,LS_MASTER.CONSUMER_CODE as CONSUMER_CODE
,LS_MASTER.facility_score as facility_score
,LS_MASTER.FASB13_CHECK_FLAG as FASB13_CHECK_FLAG
,LS_INCOME.NA_RENTS_ACCR_MTDas NA_RENTS_ACCR_MTD
,LS_INCOME.NA_RENTS_ACCR_YTDas NA_RENTS_ACCR_YTD
,LS_INCOME.NA_RENTS_ACCR_CTDas NA_RENTS_ACCR_CTD
,LS_INCOME.NA_RENTS_ACCR_PREV_YRSas NA_RENTS_ACCR_PREV_YRS
,LS_INCOME.NA_MTD_RENTS_ACCR_PREV_YRSas NA_MTD_RENTS_ACCR_PREV_YRS
,LS_INCOME.MTD_SUSPEN_FINas MTD_SUSPEN_FIN
,LS_INCOME.YTD_SUSPEN_FINas YTD_SUSPEN_FIN
,LS_INCOME.CTD_SUSPEN_FINas CTD_SUSPEN_FIN
,LS_INCOME.NA_MTD_PREV_YRS_FINas NA_MTD_PREV_YRS_FIN
,LS_INCOME.NA_PREV_YRS_RESas NA_PREV_YRS_RES
,LS_INCOME.MTD_SUSPEN_RESas MTD_SUSPEN_RES
,LS_INCOME.YTD_SUSPEN_RESas YTD_SUSPEN_RES
,LS_INCOME.CTD_SUSPEN_RESas CTD_SUSPEN_RES
,LS_INCOME.NA_MTD_PREV_YRS_RESas NA_MTD_PREV_YRS_RES
,LS_INCOME.NA_MTD_PVas NA_MTD_PV
,LS_INCOME.NA_YTD_PVas NA_YTD_PV
,LS_INCOME.NA_CTD_PVas NA_CTD_PV
,LS_INCOME.NA_MTD_PREV_YRS_PVas NA_MTD_PREV_YRS_PV
,LS_INCOME.NA_PREV_YRS_PV as NA_PREV_YRS_PV
,LS_MASTER.CONTRACT_STATUS_DATE as CONTRACT_STATUS_DATE
,LS_BILLING.STORED_USERID as STORED_USERID
,LS_USER.UM_COMMENTS2 as UM_COMMENTS2
,LS_CADDR.CUST_NAME as CUST_NAME
,LS_INCOME.CTD_IDC as CTD_IDC
,LS_INCOME.MTD_FINANCE as MTD_FINANCE
,LS_INCOME.MTD_IDC as MTD_IDC
,LS_INCOME.MTD_RESIDUAL as MTD_RESIDUAL
,LS_INCOME.RENTS_ACCR_CTD as RENTS_ACCR_CTD
,LS_INCOME.RENTS_ACCR_MTD as RENTS_ACCR_MTD
,LS_INCOME.RENTS_ACCR_YTD as RENTS_ACCR_YTD
,LS_INCOME.YTD_FINANCE as YTD_FINANCE
,LS_INCOME.YTD_IDC as YTD_IDC
,LS_INCOME.YTD_RESIDUAL as YTD_RESIDUAL
,LS_BILLING.RESIDUAL_WD as RESIDUAL_WD
,LS_BILLING.WRITEDOWN as WRITEDOWN
,LS_MASTER.IDC_AMOUNTS as IDC_AMOUNTS
--TAX
,LS_NEW_FIELDS.nmf_contact_email as nmf_contact_email
-- Canada fields
--,ls_ctd_pymthist_d1.h_payment_amount as amt_last_pymt
,LS_USER.UM_ALPHA_FIELD7 as invoice_comment_1
,LS_USER.UM_ALPHA_FIELD8 as invoice_comment_2
,LS_USER.UM_ALPHA_FIELD9 as invoice_comment_3
,LS_BILLING.PYMTS_ARREARS as arrears_advance
,LS_MASTER.GUAR_RESIDUAL as guaranteed_resid_amt
,LS_MASTER.GUARANTEED_RESID as guaranteed_resid_code
,convert(varchar(1),LS_BILLING.NUM_UNITS) as num_of_invoice_copies
,LS_BILLING.INVOICE_DESC as invoice_long_description
,LS_BILLING.INVOICE_FRMT as invoice_format_code
,LS_MASTER.MRKTNG_REP as marketing_rep_1
,LS_MASTER.MRKTNG_REP as marketing_rep_2
,LS_BILLING.CONTRACT_PYMT as contract_payment
,CaseWhen LS_MASTER.PRIVATE_LABEL = '1' AND
PARAMETER_D3.description is not null
Then PARAMETER_D3.description
Else 'J1'
End as logo_id
,LS_MASTER.BANK_CODE as bank_code
,LS_MASTER.TAPE_BANK_NUM as papp_bank_num
,LS_MASTER.TAPE_ACCOUNT_NUM as papp_account_num
,LS_USER2.CMU_TABLE2 as first_right_of_refusal
,LS_MASTER.DEALER as originating_dealer
,LS_NEW_FIELDS.NMF_NON_NOTIFICATION as non_notification
,isnull(LS_BILLING.CBR,0) - isnull(ls_billing.CURR_RENT_RCVB,0) as remain_rent_rec
,RE_MASTER.R_TERM_DATE as renewal_term_date
,LS_USER.UM_ALPHA_FIELD5 as dlr_provided_cust
,LS_USER2.CMU_ALPHA_FIELD1 as disk_flag
,LS_USER2.CMU_ALPHA_FIELD2 as nad_eer_control_number
,LS_USER2.CMU_ALPHA_FIELD3 as bill_dept_code
,LS_USER.UM_ALPHA_FIELD3 as obligor_score
,LS_MASTER.CR_SCORING as cr_scoring
-- Canadian fields
-- TAX2
,LS_FLOAT.FLOAT_TYPE as float_type
,LS_FLOAT.PRIN_REMAIN as prin_remain
,LS_INCOME.INTEREST_ACCRUED as interest_accrued
,LS_FLOAT.UNPAID_INTEREST as unpaid_interest
,LS_FLOAT_INCOME.MTD_INT as mtd_int
,LS_FLOAT_INCOME.MTD_ADJ as mtd_adj
,LS_FLOAT_INCOME.YTD_INT as ytd_int
,LS_FLOAT_INCOME.YTD_ADJ as ytd_adj
,LS_FLOAT_INCOME.CTD_INT as ctd_int
,LS_FLOAT_INCOME.CTD_ADJ as ctd_adj
,LS_MASTER.NET_FINANCE as net_finance
,LS_MASTER.AMORT_RESIDUAL as amort_residual
,RE_MASTER_D1.TOT_R_CBR as tot_r_cbr
,RE_MASTER_D1.TOT_R_CONTRA_RESIDUAL as tot_r_contra_residual
,RE_MASTER_D1.TOT_R_CTD_CONTRA as tot_r_ctd_contra
,RE_MASTER_D1.TOT_R_CTD_INCOME as tot_r_ctd_income
,RE_MASTER_D1.TOT_R_GROSS_CONTRACT as tot_r_gross_contract
,RE_MASTER_D1.TOT_R_MTD_CONTRA as tot_r_mtd_contra
,RE_MASTER_D1.TOT_R_MTD_INCOME as tot_r_mtd_income
,RE_MASTER_D1.TOT_R_YTD_CONTRA as tot_r_ytd_contra
,RE_MASTER_D1.TOT_R_YTD_INCOME as tot_r_ytd_income
,RE_MASTER_D1.TOT_R_PYMTS_RECEIVED as tot_r_pymts_received
,LS_BILLING.FLOAT_RATE as float_rate
,LS_BLENDED_INCOME_D1.BI_MTD_ACCR as bi_mtd_accr
,LS_BLENDED_INCOME_D1.BI_YTD_ACCR as bi_ytd_accr
,LS_BLENDED_INCOME_D1.BI_CTD_ACCR as bi_ctd_accr
,LS_FLOAT_INCOME.YTD_SUSPEN_INT as ytd_suspen_int
,LS_FLOAT_INCOME.CTD_SUSPEN_INT as ctd_suspen_int
-- End TAX2
-- Start PQS
,LS_USER.UM_USER_AMT2 as um_user_amt2
,LS_MASTER.USER_CODE as user_code
,LS_MASTER.PROMOTION as promotion
,LS_MASTER.PURPOSE_OF_LOAN as purpose_of_loan
,LS_CADDR.CUST_DBA as cust_dba
,LS_CADDR.CUST_SHORT_NAME as cust_short_name
,LS_AADDR.AR_COUNTRY as ar_country
,Case When LS_MASTER.CONTACT_FAX_PHONE is null Then null
Else dbo.fn_remove_non_numeric(LS_MASTER.CONTACT_FAX_PHONE)
End as contact_fax_phone
,LS_MASTER.QUOTE_BUYOUT as quote_buyout
,LS_MASTER.TAPE_ACCT_TYPE as tape_acct_type
,LS_MASTER.PREV_CONTRACT as prev_contract
,Case When LS_MASTER.REQ_SIGNATURE_PHONE is null Then null
Else dbo.fn_remove_non_numeric(LS_MASTER.REQ_SIGNATURE_PHONE)
End as req_signature_phone
,LS_FLOAT.CURRENT_RATE as current_rate
,LS_BILLING.STATE_TAX_AMT as state_tax_amt
,LS_BILLING.CNTY_TAX_AMT as cnty_tax_amt
,LS_BILLING.CITY_TAX_AMT as city_tax_amt
,LS_BILLING.PYMTS_MADE as pymts_made
,LS_BILLING.P1_DATE as p1_date
,LS_BILLING.P31_DATE as p31_date
,LS_BILLING.P61_DATE as p61_date
,LS_BILLING.P91_DATE as p91_date
-- End PQS
--Start TAX3
,LS_INCOME.CASH_ACCR_MTDas cash_accr_mtd
,LS_INCOME.CASH_ACCR_YTDas cash_accr_ytd
,LS_INCOME.CASH_ACCR_CTDas cash_accr_ctd
--End TAX3
,LS_COLLECT_D1.COLLECTOR as collector
,LS_BILLING.MISC_OVERDUE as misc_overdue
,RE_MASTER.R_ACTIV_DATE as r_activ_date
,LS_LATE_CHARGES.LC_DAILY_LATE_CHRG_AMT as lc_daily_late_chrg_amt
,LS_COLLECT_D1.collector_ext as collector_ext
,LS_INCOME.CTD_PV as ctd_pv
,LS_BILLING.OPER_LEASE_RCVB as oper_lease_rcvb
,LS_MASTER.PRESENT_VALUE_AMT as present_value_amt
-- new for VisionPlus
,null as block_code_1
,null as block_code_2
,null as account_status
,null as Interest_billed_not_paid
,null as Dell_Customer_Number
,null as organization
,null as int_accr_thru_date
,null as credit_Limit
,null as unutilized_credit
,null as charge_off_status
,null as usage_flag
,LS_BILLING.FINAL_PYMT_DATE as final_pymt_date
,LS_BILLING.STATE_TAX_AMT as provincial_tax_due
,LS_BILLING.CNTY_TAX_AMT as federal_tax_due
,LS_BILLING.CURRENT_RENTAL as next_pymt_amt
,isnull(ls_billing.MISC_DUE,0) + isnull(ls_billing.MISC_OVERDUE,0) + isnull(ls_billing.LATE_CHRGS,0) as other_charges
,null as pct_number
,null as past_due_91_120
,null as past_due_120_150
,null as past_due_150_180
,null as past_due_180_210
,null as past_due_210plus
,null as times_delin_90_120
,null as times_delin_120_150
,null as times_delin_150_180
,null as times_delin_180_210
,null as times_delin_210plus
,null as current_balance
,null as end_of_deferred_period_date
,null as logo
,null as cycle_date
,null as Int_variance
,Case When LS_OI_CTD_INVOICE_D1.OIC_DUE_DATE is not null Then LS_OI_CTD_INVOICE_D1.OIC_DUE_DATE Else dateadd(dd,isnull(LS_BILLING.INV_DAYS,0),LS_BILLING.INVOICING_DATE) End as next_pmt_date_new
,LS_AADDR.AR_NAME as cust_ar_name
,null as mobile_phone
,LS_BILLING.FIRST_PYMT_AMT as first_pymt_amt
,null as next_pap_amt
,null as actual_pap_amt
-- The following column names are the names of the columns in the UDB
-- this makes no sense, but just following orders.
,ls_billing.wd_date --date of write down
,ls_new_fields.nmf_naics_code nmf_naics_code
,ls_user.um_alpha_field4 um_alpha_field4 -- pd_grade
--,ls_user.um_user_date2 um_user_date2 -- lgd effective date
,ls_user2.cmu_alpha_field2 cmu_alpha_field2 -- pd indicator
--,ls_user.um_user_date2 um_user_date2 -- pd effective date
,ls_user2.cmu_alpha_field1 cmu_alpha_field1 -- lgd indicator
,null as ambs_curr_secur_id
FROMLS_BILLING as LS_BILLING
left outer join LS_MASTER as LS_MASTER on LS_BILLING.CONTRACT_NO = LS_MASTER.CONTRACT_NO
left outer join LS_USER as LS_USER on LS_BILLING.CONTRACT_NO = LS_USER.CONTRACT_NO
left outer join LS_INCOME as LS_INCOME on LS_BILLING.CONTRACT_NO = LS_INCOME.CONTRACT_NO
left outer join LS_INCOME_TABLE as LS_INCOME_TABLE on LS_BILLING.CONTRACT_NO = LS_INCOME_TABLE.CONTRACT_NO
left outer join RE_MASTER as RE_MASTER on RE_MASTER.RENEW_ID = LS_BILLING.CURRENT_RENEWAL
left outer join LS_AADDR as LS_AADDR on LS_BILLING.CONTRACT_NO = LS_AADDR.CONTRACT_NO
left outer join LS_NEW_FIELDS as LS_NEW_FIELDS on LS_BILLING.CONTRACT_NO = LS_NEW_FIELDS.CONTRACT_NO
left outer join LS_INVOICE as LS_INVOICE on LS_BILLING.CONTRACT_NO = LS_INVOICE.CONTRACT_NO
left outer join LS_CADDR as LS_CADDR on LS_BILLING.CONTRACT_NO = LS_CADDR.CONTRACT_NO
left outer join
(selectO.h_contract_key,sum(O.h_payment_amount) as h_payment_amount
fromls_ctd_pymthist O
whereO.h_payment_type in ('1','2','3','4') and
O.h_date_received =
(selectmax(I.h_date_received)
fromls_ctd_pymthist I
whereI.h_contract_key = O.h_contract_key and
O.h_payment_type in ('1','2','3','4')
)
group by O.h_contract_key
) ls_ctd_pymthist_d1
on LS_BILLING.CONTRACT_NO = ls_ctd_pymthist_d1.h_contract_key
left outer join
(selectr_contract_key,
sum(RE_MASTER.R_CBR) as tot_r_cbr,
sum(RE_MASTER.R_CONTRA_RESIDUAL) as tot_r_contra_residual,
sum(RE_MASTER.R_CTD_CONTRA) as tot_r_ctd_contra,
sum(RE_MASTER.R_CTD_INCOME) as tot_r_ctd_income,
sum(RE_MASTER.R_GROSS_CONTRACT) as tot_r_gross_contract,
sum(RE_MASTER.R_MTD_CONTRA) as tot_r_mtd_contra,
sum(RE_MASTER.R_MTD_INCOME) as tot_r_mtd_income,
sum(RE_MASTER.R_YTD_CONTRA) as tot_r_ytd_contra,
sum(RE_MASTER.R_YTD_INCOME) as tot_r_ytd_income,
sum(RE_MASTER.R_PYMTS_RECEIVED) as tot_r_pymts_received
fromRE_MASTER
group by r_contract_key
) RE_MASTER_D1
on LS_BILLING.CONTRACT_NO = RE_MASTER_D1.r_contract_key
left outer join
(selectoic_contract_number,
max(OIC_DUE_DATE) as OIC_DUE_DATE
from LS_OI_CTD_INVOICE,ls_param_control
whereOIC_DUE_DATE > ls_param_control.run_date
group by oic_contract_number
) LS_OI_CTD_INVOICE_D1
on LS_OI_CTD_INVOICE_D1.oic_contract_number = LS_BILLING.CONTRACT_NO
left outer join
(select substring(P_ID,4,3) as p_id,description as region_name
fromPARAMETER
whereP_ID like '19*%'
) PARAMETER_D1
on LS_MASTER.REGION = PARAMETER_D1.P_ID
left outer join
(select substring(p_id,1,charindex('*',p_id)-1) as p_id,CURRENCY_CODE
fromPARAMETER
whereP_ID like '%*00' and currency_code is not null
) PARAMETER_D2
on substring(LS_BILLING.CONTRACT_NO,1,3) = PARAMETER_D2.P_ID
left outer join
(selectT1.contract_no,T1.as_recourse_code
fromas_master T1,
(select contract_no,min(asset_no) as asset_no
fromas_master
group by contract_no
) T2
whereT1.contract_no = T2.contract_no and
T1.asset_no = T2.asset_no
) AS_MASTER_D1
on LS_BILLING.CONTRACT_NO = AS_MASTER_D1.CONTRACT_NO
left outer join
(select left(GL_MASTER_KEY,15) as contract_no,sum(GL_TOTAL_RECEIVED) as SUM_GL_TOTAL_RECEIVED
from AS_GAIN_LOSS
group by left(GL_MASTER_KEY,15)
) AS_GAIN_LOSS_D1
on LS_BILLING.CONTRACT_NO = AS_GAIN_LOSS_D1.CONTRACT_NOleft outer join
(selectAM.CONTRACT_NO,
sum(isnull(AUT.UT_STATE_TAX_AMT,0) + isnull(AUT.UT_CNTY_TAX_AMT,0) + isnull(AUT.UT_CITY_TAX_AMT,0) + isnull(AUT.UT_TCNTY_TAX_AMT,0) + isnull(AUT.UT_TCITY_TAX_AMT,0)) as ut_amt
from AS_UPFRONT_TAX AUT, AS_MASTER AM
whereAUT.ASSET_NO = AM.ASSET_NO
group by AM.CONTRACT_NO
) AS_UPFRONT_TAX_D1
on LS_BILLING.CONTRACT_NO = AS_UPFRONT_TAX_D1.CONTRACT_NO
left outer join
(select left(LS_GAIN_LOSS_ID,15) as contract_no,
sum(GL_BOOK_GAIN_LOSS) as SUM_GL_BOOK_GAIN_LOSS,
sum(GL_UNEARNED_RESIDUAL) as SUM_GL_UNEARNED_RESIDUAL,
sum(GL_TAX_GAIN_LOSS) as SUM_GL_TAX_GAIN_LOSS
from LS_GAIN_LOSS
group by left(LS_GAIN_LOSS_ID,15)
) LS_GAIN_LOSS_D1
on LS_BILLING.CONTRACT_NO = LS_GAIN_LOSS_D1.CONTRACT_NO
left outer join (select left(O1.LS_GAIN_LOSS_ID,15) as contract_no,max(O1.GL_TABLE_TYPE) as DISPOSITION_CODE,max(P.description) as DISPOSITION_DESC
from ls_gain_loss O1
inner join
(select left(LS_GAIN_LOSS_ID,15) as contract_no, max(substring(LS_GAIN_LOSS_ID,17,3)) as seq
from ls_gain_loss
group by left(LS_GAIN_LOSS_ID,15)
) O2
on left(O1.LS_GAIN_LOSS_ID,15) = O2.contract_no
and substring(O1.LS_GAIN_LOSS_ID,17,3) = O2.seq
left outer join
(select substring(P_ID,4,3) as p_id,description
fromPARAMETER
whereP_ID like '35*%'
) P
on P.P_ID = O1.GL_TABLE_TYPE
group by left(O1.LS_GAIN_LOSS_ID,15)
) LS_GAIN_LOSS_D2
on LS_BILLING.CONTRACT_NO = LS_GAIN_LOSS_D2.CONTRACT_NO
left outer join
(selectmisc_contract_no,sum(isnull(LS_MISC_INVD.MISC_AMT,0)) as MISC_AMT,
sum(isnull(LS_MISC_INVD.MISC_STATE_TAX,0) +
isnull(LS_MISC_INVD.MISC_CNTY_TAX,0) +
isnull(LS_MISC_INVD.MISC_CITY_TAX,0) +
isnull(LS_MISC_INVD.MISC_TCITY_TAX_AMT,0) +
isnull(LS_MISC_INVD.MISC_TCNTY_TAX_AMT,0)) as MISC_INVD_TAXES
fromls_misc_invd as LS_MISC_INVD
group by misc_contract_no
) LS_MISC_INVD_D1
on LS_BILLING.CONTRACT_NO = LS_MISC_INVD_D1.misc_contract_no
left outer join
(select ls_oi_ctd_invoice.oic_contract_number as contract_no,
Sum(Case When oic_aging_bucket not like 'F%' Then oic_outstanding_balance Else 0 End) as total_past_due
fromls_oi_ctd_invoice inner join ls_oi_ctd_type
on ls_oi_ctd_type.oic_id = ls_oi_ctd_invoice.oic_id
group by ls_oi_ctd_invoice.oic_contract_number
) LS_OI_CHARGES_D1
on LS_BILLING.CONTRACT_NO = LS_OI_CHARGES_D1.contract_no
left outer join
(selectMISC_CONTRACT_NO,
Sum(Case INC_IN_RENT When 1
Then
(DATEDIFF(mm,substring(convert(varchar(12),LS_MISC_REP.MISC_DATE),1,4) + '/01/' + substring(convert(varchar(12),LS_MISC_REP.MISC_DATE),5,2), substring(convert(varchar(12),LS_MISC_REP.FINAL_MISC_DATE),1,4) + '/01/' + substring(convert(varchar(12),LS_MISC_REP.FINAL_MISC_DATE),5,2)) -1) * LS_MISC_REP.MISC_AMT
Else 0
End
) as future_inc_in_rent
from ls_misc_rep as ls_misc_rep
group by MISC_CONTRACT_NO) LS_MISC_REP_D1
on LS_BILLING.CONTRACT_NO = LS_MISC_REP_D1.MISC_CONTRACT_NO
cross join LS_PARAM_CONTROL LS_PARAM_CONTROL
-- Canadian field
left outer join
(select substring(P_ID,6,30) as p_id,description
fromPARAMETER
whereP_ID like '13LI*%'
) PARAMETER_D3
on LS_MASTER.DEALER = PARAMETER_D3.P_ID
left outer join LS_USER2 as LS_USER2 on LS_BILLING.CONTRACT_NO = LS_USER2.CONTRACT_NO
-- Canadian field
left outer join LS_FLOAT as LS_FLOAT on LS_BILLING.CONTRACT_NO = LS_FLOAT.CONTRACT_NO
left outer join LS_FLOAT_INCOME as LS_FLOAT_INCOME on LS_BILLING.CONTRACT_NO = LS_FLOAT_INCOME.CONTRACT_NO
left outer join
(selectBI_CONTRACT_NO,
sum(BI_MTD_ACCR) as BI_MTD_ACCR,
sum(BI_YTD_ACCR) as BI_YTD_ACCR,
sum(BI_CTD_ACCR) as BI_CTD_ACCR
fromLS_BLENDED_INCOME
group by BI_CONTRACT_NO
) LS_BLENDED_INCOME_D1
on LS_BILLING.CONTRACT_NO = LS_BLENDED_INCOME_D1.BI_CONTRACT_NO
left outer join
(selectsubstring(LS_COLLECT.CONTRACT_AND_DEPT,1,15) as contract_no,
max(collector) as collector,
max(fld6) as collector_ext
fromLS_COLLECT
left outer join parameter
on LS_COLLECT.collector = substring(parameter.p_id,4,30)
and parameter.p_id like '10*%'
wheresubstring(LS_COLLECT.CONTRACT_AND_DEPT,17,2) = '02'
group by substring(LS_COLLECT.CONTRACT_AND_DEPT,1,15)
) LS_COLLECT_D1
on LS_BILLING.CONTRACT_NO = LS_COLLECT_D1.contract_no
left outer join LS_LATE_CHARGES as LS_LATE_CHARGES on LS_BILLING.CONTRACT_NO = LS_LATE_CHARGES.CONTRACT_NO
WHERELS_BILLING.num_of_assets > 0 OR (LS_BILLING.num_of_assets is null AND LS_MASTER.DISP_DATE is null)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
April 30, 2008 at 8:54 am
Looking at the amount of left outer joins. Its fairly hard to crank up the query's performance from over here. Can you post the db Diagram of the view from View builder. or chat with me on messenger yahooid r20j@yahoo.com day after 2morrow time or mail me the view diagram on the same. I will give it a shot. The Idea is to cover up as many inner joins first and write the query with left outers' this gives at times 95% time gain for upto 100000 rows in a 1GB db. I would also be interested in Business logic of the query.
Regards,
Rajesh.
MBA-IT (Software Dev & Mgmt)
SCIT-2007-09
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply