I read the article 'parameter sniffing'. Below is my 'Stored Procedure', which fields do I need to create index on to speed up the performance. I've created indexes on region, territory, insertion date, but it still is taking forever to process.
Thanks in advance for anyones help.
(
@start_date_CY varchar(10)
,@end_date_CY varchar(10)
,@start_date_LY varchar(10)
,@end_date_LY varchar(10)
,@year int
)
AS
SELECT
ad_num = mc_ad_detail.ad_num
, source = mc_acct_master_view.source
, adv_type = mc_ad_detail.adv_type
, period_year = mc_period_dates.period_year
, prod_code = mc_ad_insertion.prod_code
, credit_amt = mc_ad_cost_view.credit_amt
, debit_amt = mc_ad_cost_view.debit_amt
, obj_code = mc_ad_cost_view.obj_code
, obj_code_sub = mc_ad_cost_view.obj_code_sub
, region = mc_region_vals.region
, region_desc = mc_region_vals.region_desc
, territory = mc_acct_master_view.territory
, insertion_date = Convert(VarChar(10)
, mc_ad_insertion.insertion_date, 101)
, effective_inches = mc_ad_insertion.effective_inches
, effective_lines =
CASE
WHEN mc_ad_cost_view.obj_code_sub = '1'
THEN mc_ad_insertion.effective_lines
ELSE 0
END
FROM mc_acct_master_view
INNER JOIN mc_ad_detail
ON (mc_acct_master_view.bus_unit_id = mc_ad_detail.bus_unit_id) AND
(mc_acct_master_view.pub_id = mc_ad_detail.pub_id) AND
(mc_acct_master_view.acct_num = mc_ad_detail.acct_num)
INNER JOIN mc_ad_insertion
ON (mc_ad_detail.bus_unit_id = mc_ad_insertion.bus_unit_id) AND
(mc_ad_detail.pub_id = mc_ad_insertion.pub_id) AND
(mc_ad_detail.acct_num = mc_ad_insertion.acct_num) AND
(mc_ad_detail.ad_num = mc_ad_insertion.ad_num) AND
(mc_ad_detail.entry_date_time = mc_ad_insertion.entry_date_time)
INNER JOIN mc_ad_cost_view
ON (mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num) AND
(mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND
(mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id)
INNER JOIN mc_obj_code_vals
ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code
INNER JOIN mc_region_vals
ON mc_acct_master_view.region = mc_region_vals.region
INNER JOIN mc_territory_vals
ON mc_acct_master_view.territory = mc_territory_vals.territory
INNER JOIN (mc_period_dates INNER JOIN mc_period_dates_view
ON mc_period_dates.calendar_date = mc_period_dates_view.calendar_date)
ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date
WHERE
mc_acct_master_view.bus_unit_id = 'MMB' AND
mc_acct_master_view.pub_id = 'MMB' AND
mc_ad_detail.adv_type IN ( 'CL', 'GC', 'CT', 'GT','GE','RE') AND
mc_acct_master_view.region IN ('COMM', 'COOP', 'GENL','INSI','LEGL','LROT','MERC','MISC','MJVP','ONLI',
'OSCL','OTHR','REAL','RECR','SHOW') AND
mc_ad_cost_view.obj_code NOT IN ('19', '29', '59', '485', '900', '905', '915') AND
mc_ad_insertion.prod_code NOT IN ('ELDY','INEM','ONAT','ONCR','ONDI','ONHO','ONLI','ONNA',
'ONWK','VIDA') AND
mc_period_dates.period_year = @year AND
(mc_ad_insertion.insertion_date between @start_date_CY and @end_date_CY) OR
(mc_ad_insertion.insertion_date between @start_date_LY AND @end_date_LY)
--GROUP BY adv_type, class_code_int, mc_ad_cost_view.obj_code,
prod_code, mc_ad_insertion.insertion_date
ORDER BY region, territory
-- This will create the indexes needed to query the table.
CREATE INDEX mc_mb_103region_insdte_indx
ON mc_ad_insertion (insertion_date)
GO
Thanks in advance for anyones help.
(
@start_date_CY varchar(10)
,@end_date_CY varchar(10)
,@start_date_LY varchar(10)
,@end_date_LY varchar(10)
,@year int
)
AS
SELECT
ad_num = mc_ad_detail.ad_num
, source = mc_acct_master_view.source
, adv_type = mc_ad_detail.adv_type
, period_year = mc_period_dates.period_year
, prod_code = mc_ad_insertion.prod_code
, credit_amt = mc_ad_cost_view.credit_amt
, debit_amt = mc_ad_cost_view.debit_amt
, obj_code = mc_ad_cost_view.obj_code
, obj_code_sub = mc_ad_cost_view.obj_code_sub
, region = mc_region_vals.region
, region_desc = mc_region_vals.region_desc
, territory = mc_acct_master_view.territory
, insertion_date = Convert(VarChar(10)
, mc_ad_insertion.insertion_date, 101)
, effective_inches = mc_ad_insertion.effective_inches
, effective_lines =
CASE
WHEN mc_ad_cost_view.obj_code_sub = '1'
THEN mc_ad_insertion.effective_lines
ELSE 0
END
FROM mc_acct_master_view
INNER JOIN mc_ad_detail
ON (mc_acct_master_view.bus_unit_id = mc_ad_detail.bus_unit_id) AND
(mc_acct_master_view.pub_id = mc_ad_detail.pub_id) AND
(mc_acct_master_view.acct_num = mc_ad_detail.acct_num)
INNER JOIN mc_ad_insertion
ON (mc_ad_detail.bus_unit_id = mc_ad_insertion.bus_unit_id) AND
(mc_ad_detail.pub_id = mc_ad_insertion.pub_id) AND
(mc_ad_detail.acct_num = mc_ad_insertion.acct_num) AND
(mc_ad_detail.ad_num = mc_ad_insertion.ad_num) AND
(mc_ad_detail.entry_date_time = mc_ad_insertion.entry_date_time)
INNER JOIN mc_ad_cost_view
ON (mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num) AND
(mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND
(mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id)
INNER JOIN mc_obj_code_vals
ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code
INNER JOIN mc_region_vals
ON mc_acct_master_view.region = mc_region_vals.region
INNER JOIN mc_territory_vals
ON mc_acct_master_view.territory = mc_territory_vals.territory
INNER JOIN (mc_period_dates INNER JOIN mc_period_dates_view
ON mc_period_dates.calendar_date = mc_period_dates_view.calendar_date)
ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date
WHERE
mc_acct_master_view.bus_unit_id = 'MMB' AND
mc_acct_master_view.pub_id = 'MMB' AND
mc_ad_detail.adv_type IN ( 'CL', 'GC', 'CT', 'GT','GE','RE') AND
mc_acct_master_view.region IN ('COMM', 'COOP', 'GENL','INSI','LEGL','LROT','MERC','MISC','MJVP','ONLI',
'OSCL','OTHR','REAL','RECR','SHOW') AND
mc_ad_cost_view.obj_code NOT IN ('19', '29', '59', '485', '900', '905', '915') AND
mc_ad_insertion.prod_code NOT IN ('ELDY','INEM','ONAT','ONCR','ONDI','ONHO','ONLI','ONNA',
'ONWK','VIDA') AND
mc_period_dates.period_year = @year AND
(mc_ad_insertion.insertion_date between @start_date_CY and @end_date_CY) OR
(mc_ad_insertion.insertion_date between @start_date_LY AND @end_date_LY)
--GROUP BY adv_type, class_code_int, mc_ad_cost_view.obj_code,
prod_code, mc_ad_insertion.insertion_date
ORDER BY region, territory
-- This will create the indexes needed to query the table.
CREATE INDEX mc_mb_103region_insdte_indx
ON mc_ad_insertion (insertion_date)
GO