QUERY tuning pls

[peac3]

Hi guys,

I have the query as below

SELECT   queen.exp_std_rec_type,   convert(varchar(12),dc.reporting_date,103) as reporting_date, replace(replace(replace(replace(dc.description, '(FULL) - ',''),convert(varchar(12), dc.reporting_date, 103),''),
convert(varchar,dc.reporting_date,107),''), '['+ rtrim(cast(dc.cid as char(4))) + '] - ','') as report_type, 
queen.exp_std_rec_id, b.is_trading_book, 'OT' AS TABLETYPE,  queen.supervisory_category,                      
queen.exp_key, queen.entity                      
FROM    v_t_queen_airb_otc_derivative_cb AS queen
INNER     JOIN             v_t_bisb2110 AS b ON queen.exp_std_rec_id = b.std_rec_id  and b.std_rec_type = queen.exp_std_rec_type INNER JOIN
                      v_r_calc_lot AS cl ON queen.lid = cl.lid INNER JOIN
                      v_r_calc_definition AS run ON queen.cid = run.cid  INNER JOIN
                      v_d_calc AS dc ON dc.cid = run.cid    INNER JOIN
                      v_d_exp_class AS dec ON queen.exp_class = dec.exp_class LEFT OUTER JOIN
                      v_d_customer AS dcu ON dcu.customer_nr = queen.customer_nr AND dcu.is_latest = 1 LEFT OUTER JOIN (select distinct supervisory_category, ul_rw, el_rw       
                from v_r_supervisory_slotting_relation_cb       
                inner join v_d_calc cc2 on 1 = 1       
                where start_validity_date <= cc2.reporting_date
                        and end_validity_date >= cc2.reporting_date) as sl   
on isnull(queen.supervisory_category,0) = isnull(sl.supervisory_category,0) LEFT OUTER JOIN
                      v_d_entity AS de ON de.entity = queen.entity
WHERE     (dec.is_latest = 1) AND (queen.report_type NOT LIKE '%No com%') AND queen.exp_std_rec_type = 'retail'

the column all coming from 'VIEW' as you guys could see start with 'v_' which takes around 4 mins.

If I change the query using table (remove the leading v_ ) it only takes around 30 seconds.

we need the view because it contains conversion from table column.

I was wondering what should I do to reduce the difference between view and table for above query.

Thanks for the input

 

[gmmastros]

I see a lot of views in this query. Have you determined which one is causing the performance problems? Once you determine that, you should take a look at the view to see if there is anything you can do to speed it up. Sometimes, views have multiple joins, and can be written in a way that causes performance problems.

I suggest... determine which view is causing the performance problem, and then post that code here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

You have identified yourself that the view is slow. This query uses the view that is slow - so this will also be slow. If the view was made quicker then this was still slow - then look at this.

You cant expect a query to run fast over a slow running view....

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[peac3]

Hi all,

I have run the comparison between table and view for each above using select *

eg:
select * from v_t_queen_airb_otc_derivative_cb -- view
select * from t_queen_airb_otc_derivative_cb -- table

and others...

The result are exactly the same except for 1 which is v_t_bis210 hasn't given me the result because it contains tens of millions of records for either table and view.

Here's the code for view v_t_bis210

CREATE VIEW [v_t_bis210]
AS
SELECT
      std_record_id
      ,lot_id
      ,sequence_nr
      ,CONVERT(VARCHAR(8), std_record_type) AS std_record_type
      ,CONVERT(VARCHAR(50), source_system) AS source_system
      ,CONVERT(VARCHAR(20), object_origin) AS object_origin
      ,CONVERT(CHAR(1), object_key_type) AS object_key_type
      ,CONVERT(VARCHAR(500), object_key_value) AS object_key_value
      ,drill_back_id
      ,last_modified
      ,CONVERT(VARCHAR(255), modified_by) AS modified_by
      ,CONVERT(VARCHAR(20), exposure_object_origin) AS exposure_object_origin
      ,CONVERT(CHAR(1), exposure_object_key_type) AS exposure_object_key_type
      ,CONVERT(VARCHAR(500), exposure_object_key_value) AS exposure_object_key_value
      ,CONVERT(VARCHAR(255), customer_nr) AS customer_nr
      ,CONVERT(VARCHAR(3), country) AS country
      ,is_repo_core_market_participant
      ,CONVERT(VARCHAR(3), orig_currency) AS orig_currency
      ,gross_exposure_amount
      ,is_valid_when_negative
      ,residual_value
      ,is_residual_value_guaranteed
      ,exposure_type
      ,CONVERT(VARCHAR(4), irba_mitigation_method) AS irba_mitigation_method
      ,is_capital_component
      ,CONVERT(VARCHAR(30), capital_component_type) AS capital_component_type
      ,is_trading_book
      ,is_subordinated
      ,provision_amount
      ,original_risk_maturity
      ,residual_risk_maturity
      ,CONVERT(VARCHAR(3), funding_currency) AS funding_currency
      ,CONVERT(VARCHAR(30), exposure_class) AS exposure_class
      ,CONVERT(VARCHAR(30), related_credit_risk_exposure_class) AS related_credit_risk_exposure_class
      ,is_exposure_subset_of_irb_ciu
      ,is_credit_institution
      ,CONVERT(VARCHAR(3), extern_counterparty_rating_agency) AS extern_counterparty_rating_agency
      ,extern_counterparty_rating_type
      ,CONVERT(VARCHAR(20), extern_counterparty_rating) AS extern_counterparty_rating
      ,CONVERT(VARCHAR(3), extern_instrument_rating_agency) AS extern_instrument_rating_agency
      ,extern_instrument_rating_type
      ,CONVERT(VARCHAR(20), extern_instrument_rating) AS extern_instrument_rating
      ,CONVERT(VARCHAR(3), intern_counterparty_rating_agency) AS intern_counterparty_rating_agency
      ,intern_counterparty_rating_type
      ,CONVERT(VARCHAR(20), intern_counterparty_rating) AS intern_counterparty_rating
      ,CONVERT(VARCHAR(3), intern_instrument_rating_agency) AS intern_instrument_rating_agency
      ,intern_instrument_rating_type
      ,CONVERT(VARCHAR(20), intern_instrument_rating) AS intern_instrument_rating
      ,reval_period
      ,ccy_reval_period
      ,risk_hc_trn_type
      ,risk_hc_prod_type
      ,debt_security_residual_risk_maturity
      ,is_sov_debt_secur
      ,ccr_method
      ,derivative_nom_amount
      ,derivative_repl_cost
      ,is_derivative_mult_princ_exch
      ,derivative_mult_princ_exch_nbr_pay
      ,is_single_ccy_flo_flo_irs
      ,is_exchange_rate_contract_mat_14d
      ,is_traded_on_recognised_exchange
      ,off_bs_add_on_instr_type
      ,is_credit_default_swap_buyer_closeout
      ,credit_default_swap_add_on_amount_cap
      ,off_bs_credit_conv_type
      ,off_bs_ccf_instr_type
      ,effective_maturity
      ,is_exp_ongoing_fin_obligor
      ,sme_total_sales
      ,pd
      ,lgd
      ,is_double_default_treatment
      ,is_non_material
      ,risk_equity_type
      ,is_scal_factor_app
      ,intern_model_var
      ,is_irb_exemption
      ,CONVERT(VARCHAR(20), supervisory_sl_category) AS supervisory_sl_category
      ,is_dilution_risk_immaterial
      ,expected_loss_pct_dilution_risk
      ,is_purchased_rec_corporate_pd_reliable
      ,CONVERT(VARCHAR(20), master_netting_agreement_id) AS master_netting_agreement_id
      ,CONVERT(VARCHAR(36), instrument_identifier) AS instrument_identifier
      ,CONVERT(VARCHAR(255), repo_style_contract_id) AS repo_style_contract_id
      ,repo_style_contract_nominal_amount
      ,is_defaulted
      ,expected_loss_pct
      ,expected_loss_amount
      ,is_past_due
      ,nbr_of_days_delay
      ,is_cross_border_transaction
      ,replacement_cost
      ,is_free_delivery_alternative_treatment
      ,CONVERT(VARCHAR(30), concentration_risk_exposure_class) AS concentration_risk_exposure_class
      ,concentration_risk_exposure_type
      ,CONVERT(VARCHAR(10), economic_sector) AS economic_sector
      ,is_exposure_underlying_item
      ,CONVERT(VARCHAR(20), parent_exposure_object_origin) AS parent_exposure_object_origin
      ,CONVERT(CHAR(1), parent_exposure_object_key_type) AS parent_exposure_object_key_type
      ,CONVERT(VARCHAR(500), parent_exposure_object_key_value) AS parent_exposure_object_key_value
      ,derivative_add_on
      ,off_bs_item_credit_conv_factor
      ,dilution_risk_effective_maturity
      ,credit_quality_scale
      ,credit_quality_step
      ,base_hc
      ,base_hc_holding_period
      ,effective_hc
      ,ead
      ,rw
      ,correlation
      ,maturity_adj
      ,capital_requirement
      ,CONVERT(VARCHAR(10), entity) AS entity
      ,CONVERT(VARCHAR(30), deal_type) AS deal_type
      ,CONVERT(VARCHAR(40), deal_subtype) AS deal_subtype
      ,risk_age
      ,custom_exposure_type
      ,CONVERT(VARCHAR(100), account_code) AS account_code
      ,is_exposure_pool
      ,number_of_obligors_within_pool
      ,number_of_exposures_within_pool
      ,CONVERT(VARCHAR(2), ead_adjustment_method) AS ead_adjustment_method
      ,loan_to_value
      ,is_ltv_updated
      ,bureau_score
      ,CONVERT(VARCHAR(64), char_cust_element1) AS char_cust_element1
      ,CONVERT(VARCHAR(64), char_cust_element2) AS char_cust_element2
      ,CONVERT(VARCHAR(64), char_cust_element3) AS char_cust_element3
      ,CONVERT(VARCHAR(64), char_cust_element4) AS char_cust_element4
      ,CONVERT(VARCHAR(64), char_cust_element5) AS char_cust_element5
      ,num_cust_element1
      ,num_cust_element2
      ,num_cust_element3
      ,num_cust_element4
      ,num_cust_element5
      ,CONVERT(VARCHAR(255), intern_model_var_val_type) AS intern_model_var_val_type
      ,intern_model_var_date
      ,lgd_dilution
      ,nominal_amount_of_the_tranche
FROM
      dbo.t_bisb2110

I have tried put the index in table t_bisb2110

CREATE NONCLUSTERED INDEX [idx3_bisb2110] ON [dbo].[t_bisb2110] 
(
      [std_record_type] ASC,
      [std_record_id] ASC,
      [lot_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

Still did not make any difference

 

[IanWaterman]

If this is the view causing trouble then use the table instead. You are only using 3 fields from this view and only one of them is converted. You could do that in your query.

Ian

 

[peac3]

Hi Ian,

this view is not being used for only this query but for many queries...

is there any way to put index on the view?

 

[fredericofonseca]

your problem is that one of the join fields on v_t_bis210 is a function within the view.
,CONVERT(VARCHAR(8), std_record_type) AS std_record_type

Others may give more input here, but I think that if you create a index on this view your performance will improve (note that on this case an index on the table will not help)

your view would also need to be created with SCHEMA BINDING and there are other requirements for this to work.


Look at BOL for full syntax and requirements, and also search the net for "create index on view" for further info

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

Good catch Frederico.

There is another way to accomplish this without schema binding on the view. You could create a computed column in the base table, and then include that computed column in an index on the base table.

You could add the computed column like this:

Alter Table dbo.t_bisb2110
Add char_std_record_type As CONVERT(VARCHAR(8), std_record_type)

CREATE NONCLUSTERED INDEX [idx3_bisb2110] ON [dbo].[t_bisb2110] 
(
      [char_std_record_type] ASC,
      [std_record_id] ASC,
      [lot_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

Then, change the view to use the computed column, or change the query to use the base table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom