January 25, 2007 at 1:43 am
thsi a very long query that i have to optimize.Please help with sime hints!
SELECT
valn_lob_segment.seg_level4,
rtrim(vlan_pol_key.bus_split_cd),
Count(valn_pol_key.valn_pol_id),
sum(valn_pol_key.curr_std_prem_amt),
valn_pol_key.admin_meth_cd,
valn_pol_key.deriv_sta_cd,
valn_pol_key.valid_sta_cd,
valn_val_data1.vdat_proc_sta_cd,
valn_val_data4.vdat_proc_sta_cd,
valn_val_data1.valn_basis_cd,
valn_val_data4.valn_basis_cd,
sum(valn_pol_key.curr_benf_amt),
sum(valn_val_data4.ibnr_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data4.tot_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data4.unearned_prem_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data4.gaap_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data4.dac_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data4.gaap_pvp_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data1.ibnr_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data1.tot_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data1.gaap_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data1.dac_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data1.gaap_pvp_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data1.tot_resv_amt),
sum(valn_val_data4.tot_resv_amt),
rtrim(valn_pol_key.co_mnm),
rtrim(valn_pol_key.orig_co_mnm),
valn_pol_key.iso_crncy_cd,
valn_pol_key.cess_retro_ind,
Case when valn_pol_key.cess_retro_ind = 'C' then 'Cess' else 'Retro' end,
sum(valn_pol_key.curr_tx_prem_amt),
sum(valn_pol_key.curr_tx_prem_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_pol_key.curr_std_prem_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_pol_key.curr_benf_amt / valn_pol_key.exch_rte_end_rte),
Case when valn_pol_key.cess_retro_ind = 'R' then -1 else 1 end,
rtrim(valn_pol_key.move_plan_cd),
valn_bas_info.gaap_era_end_dt,
valn_bas_prem_details.gtee_term_months,
valn_benf.sr_calc_def_wks,
valn_bas_info.gaap_acct_ind,
sum(valn_val_data4.office_prem_pv_amt*valn_misc_val_data4.gaap_resv_fctr_k1),
sum(valn_val_data4.office_prem_pv_amt*valn_misc_val_data4.gaap_dac_pvfp_fctr_k2),
sum(valn_val_data4.office_prem_pv_amt),
rtrim(valn_bas_info.tty_id),
valn_val_data3.vdat_proc_sta_cd,
valn_val_data3.valn_basis_cd,
sum(valn_val_data3.tot_resv_amt),
sum(valn_val_data3.ibnr_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data3.gaap_pvp_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data3.tot_resv_amt / valn_pol_key.exch_rte_end_rte),
sum(valn_val_data3.gaap_resv_amt / valn_pol_key.exch_rte_end_rte)
FROM
valn_pol_key,
valn_lob_segment,
valn_val_data1,
valn_val_data4,
valn_bas_info,
valn_bas_prem_details,
valn_benf,
valn_misc_val_data4,
valn_val_data3,
valn_pol_link
WHERE
( valn_benf.valn_pol_id=valn_pol_key.valn_pol_id )
AND ( valn_bas_prem_details.valn_pol_id=valn_pol_key.valn_pol_id )
AND ( valn_bas_info.valn_pol_id=valn_pol_key.valn_pol_id )
AND ( valn_val_data1.valn_pol_id=valn_pol_key.valn_pol_id )
AND ( valn_pol_key.valn_pol_id=valn_val_data3.valn_pol_id )
AND ( valn_pol_key.valn_pol_id=valn_val_data4.valn_pol_id )
AND ( valn_pol_key.valn_pol_id=valn_misc_val_data4.valn_pol_id )
AND ( valn_lob_segment.tty_id=valn_pol_link.tty_id )
AND ( valn_lob_segment.move_plan_cd=valn_pol_link.move_plan_cd )
AND ( valn_pol_key.valn_pol_id=valn_pol_link.valn_pol_id )
AND (
valn_lob_segment.seg_level4 =( 'OT ')
)
GROUP BY
valn_lob_segment.seg_level4,
rtrim(valn_pol_key.bus_split_cd),
valn_pol_key.admin_meth_cd,
valn_pol_key.deriv_sta_cd,
valn_pol_key.valid_sta_cd,
valn_val_data1.vdat_proc_sta_cd,
valn_val_data4.vdat_proc_sta_cd,
valn_val_data1.valn_basis_cd,
valn_val_data4.valn_basis_cd,
rtrim(valn_pol_key.co_mnm),
rtrim(valn_pol_key.orig_co_mnm),
valn_pol_key.iso_crncy_cd,
valn_pol_key.cess_retro_ind,
Case when valn_pol_key.cess_retro_ind = 'C' then 'Cess' else 'Retro' end,
Case when valn_pol_key.cess_retro_ind = 'R' then -1 else 1 end,
rtrim(valn_pol_key.move_plan_cd),
valn_bas_info.gaap_era_end_dt,
valn_bas_prem_details.gtee_term_months,
valn_benf.sr_calc_def_wks,
valn_bas_info.gaap_acct_ind,
rtrim(valn_bas_info.tty_id),
valn_val_data3.vdat_proc_sta_cd,
valn_val_data3.valn_basis_cd
January 25, 2007 at 6:28 am
a single query isn't too hard to diagnose, but rather than give specific suggestions, let me give you some general hints i think might help.
the key is to test the query as is by looking at the execution plan, then add something, like an index, and then test it again and see if they execution plan has changed
for any joins in the query (ie valn_benf.valn_pol_id=valn_pol_key.valn_pol_id ), you want to consider whether an index exists that contains the join items for each table... in this example, is there an index which contains valn_benf.valn_pol_id and separately, is there an index which contains valn_pol_key.valn_pol_id ; one table is proable a PK, and whould thus be indexed, but the foreign key'd table most likely does not, and might benefit from an index; you really have to test it; my rule of thum is if there is over 1000 rows in a foreign keys table, the table needs to be evaluated to determine if an index on the FK field will be beneficial; if it's less than 1000 rows, I kind of assume that SQl Server might just table scan the table no matter what, but it's something you can always check.
similaryly, if you are are doing a GROUP BY some column, the table might benefit from an index on that column; in your example, is there an index on valn_lob_segment.seg_level4,
Lowell
January 25, 2007 at 8:46 am
thanks for hints,
actually I want to rewrite the query for speed
January 25, 2007 at 9:05 am
structure wise, if you were to change it all to inner joins, the execution plans would be the same, so there's no speed difference...
i think this is functionally the same but with inner join statements:
FROM
valn_pol_key
INNER JOIN valn_pol_link ON valn_pol_key.valn_pol_id = valn_pol_link.valn_pol_id
INNER JOIN valn_lob_segment ON valn_pol_link.tty_id = valn_lob_segment.tty_id
AND valn_pol_link.move_plan_cd = valn_lob_segment.move_plan_cd
INNER JOIN valn_val_data1 ON valn_val_data1.valn_pol_id = valn_pol_key.valn_pol_id
INNER JOIN valn_val_data4 ON valn_pol_key.valn_pol_id = valn_val_data4.valn_pol_id
INNER JOIN valn_bas_info ON valn_bas_info.valn_pol_id = valn_pol_key.valn_pol_id
INNER JOIN valn_bas_prem_details ON valn_bas_prem_details.valn_pol_id = valn_pol_key.valn_pol_id
INNER JOIN valn_benf ON valn_pol_id = valn_pol_key.valn_pol_id
INNER JOIN valn_misc_val_data4 ON valn_pol_key.valn_pol_id = valn_misc_val_data4.valn_pol_id
INNER JOIN valn_val_data3 ON valn_pol_key.valn_pol_id = valn_val_data3.valn_pol_id
WHERE
valn_lob_segment.seg_level4 =( 'OT ')
GROUP BY
valn_lob_segment.seg_level4,
rtrim(valn_pol_key.bus_split_cd),
valn_pol_key.admin_meth_cd,
valn_pol_key.deriv_sta_cd,
valn_pol_key.valid_sta_cd,
valn_val_data1.vdat_proc_sta_cd
Lowell
January 26, 2007 at 6:10 am
Thanks Lowell,
you help me much.
January 26, 2007 at 2:47 pm
Beyond that, you may want to look at indexing. Start by making sure every column that is involved in a join is indexed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy