Stored Procedure Performance

[brxmas]

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

 

[SQLDenis]

Easy to find out
hit CTRL + K
run query, look in execution plan tab and look for table scans that's where you want to create indexes
Also index seeks are better than index scans
also take a look at fragmentation levels and make sure statistics are up to date



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[brxmas]

SQLDenis,

How would I apply an index seek? Thx.

 

[SQLDenis]

use a sargable where clause, in other words use a where clause that the index can use.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[brxmas]

I followed your advice as follows:

CREATE INDEX mc_mb_103region_amvreg_indx
ON mc_acct_master_view (bus_unit_id, pub_id,region)
CREATE INDEX mc_mb_103region_adcvobj_indx
ON mc_ad_cost_view (obj_code)

And, the query is still taking a long time to process.

Thanks in advance.

 

[SQLBill]

1. How long is it taking?
2. How big is the database?

-SQLBill

Posting advice: FAQ481-4875

 

[brxmas]

The db has 53,742 records. And, it took 12 minutes to process.

Thanks.

 

[SQLDenis]

That should not even take 12 seconds let alone 12 minutes
Is there any blocking going on?
try updating the statiscics for the tables, sometimes that is the culprit
also do you get tables scans or index seeks when you execute the query, look at the execution plan



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[brxmas]

I looked at the 'Execution Plan' and there are table scans.
What can I do to improve the process? Thanks in advance for your expertise.

Below are the Statistics.




Application Profile Statistics
Timer resolution (milliseconds)
0 0 Number of INSERT, UPDATE, DELETE statements
0 0 Rows effected by INSERT, UPDATE, DELETE statements
0 0 Number of SELECT statements
0 0 Rows effected by SELECT statements
0 0 Number of user transactions
5 5 Average fetch time
0 0 Cumulative fetch time
0 0 Number of fetches
0 0 Number of open statement handles
0 0 Max number of opened statement handles
0 0 Cumulative number of statement handles
0 0
Network Statistics
Number of server roundtrips
3 3 Number of TDS packets sent
3 3 Number of TDS packets received
1625 1626 Number of bytes sent
490 490 Number of bytes received
6.63117e+006 6.63596e+006
Time Statistics
Cumulative client processing time
21484 24301 Cumulative wait time on server replies
677243 4.42468e+007

 

[SQLDenis]

Ddd indexes to those table scan tables
You should be able to see which field(s) from the plan

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Heh... good "remote" query tuning is anything but trivial - for various reasons. There are lots of possible factors involved and many questions to ask.

Anyway, run this:

set showplan_text on
go

<insert your query here>
go

set showplan_text off
go

... and post textual exec plan here.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[brxmas]

Here's the exec plan. Thanks in advance.


StmtText
DECLARE @year int,
@start_date_CY smalldatetime,
@end_date_CY smalldatetime,
@start_date_LY smalldatetime,
@end_date_LY smalldatetime

--SET @start_date_CY = '11/28/2005'
SET @year = '2005'
SET @start_date_CY = CONVERT(varchar(10), @start_date_CY, 1)
SET @end_date_CY = CONVERT(varchar(10), @end_date_CY, 1)
SET @start_date_LY = CONVERT(varchar(10), @start_date_LY, 1)
SET @end_date_LY = CONVERT(varchar(10), @end_date_LY, 1)

SELECT
ad_num = mc_ad_detail.ad_num
, 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_c

(6 row(s) affected)

StmtText
----------------------------------------------------------------------
|--Compute Scalar(DEFINE[Expr1041]=Convert([mc_ad_insertion].[insertion_date]),
[Expr1042]=If ([Union1040]='1') then [mc_ad_insertion].[effective_lines]
else 0.00))
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join,
HASH[mc_obj_code_vals].[obj_code])=([Union1039]),
RESIDUAL[mc_obj_code_vals].[obj_code]=[Union1039]))
|--Parallelism(Broadcast)
| |--Index
Scan(OBJECT[bee_dw_dev].[dbo].[mc_obj_code_vals].[mc_obj_code_pk]))
|--Nested Loops(Left Outer Join,
WHERE[mc_acct_master].[acct_num]=[mc_acct_master].[corp_acct_num] OR
IsFalseOrNull([mc_acct_master].[acct_num]<>[mc_acct_master].
[corp_acct_num]))OUTER REFERENCES[mc_acct_master].[corp_acct_num], [mc_ac
|--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_acct_master].[corp_acct_num], [mc_acct_master].[pub_id],
[mc_acct_master].[bus_unit_id]))
| |--Hash Match(Inner Join,
HASH[mc_region_vals].[region])=([mc_location].[region]), RESIDUAL[mc_location].[region]=[mc_region_vals].[region]))
| |--Parallelism(Broadcast)
| | |--Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_region_vals]))
| |--Hash Match(Inner Join, HASH[mc_rep_master].[bus_unit_id], [mc_rep_master].[pub_id],
[mc_rep_master].[rep_num])=([Union1026], [Union1027],
[mc_rep_hist].[rep_num]),
RESIDUAL([mc_rep_master].[bus_unit_id]=[Union1026] AN
| |--Parallelism(Repartition Streams, PARTITION COLUMNS[mc_rep_master].[bus_unit_id], [mc_rep_master].[pub_id],
[mc_rep_master].[rep_num]))
| | |--Parallelism(Distribute Streams)
| | |--Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_rep_master]),
WHERE[mc_rep_master].[end_date]>=getdate() AND
[mc_rep_master].[start_date]<=getdate()))
| |--Filter(WHERE((((((((((((((((([mc_acct_master].[bus_unit_id]='MMB' AND
[mc_acct_master].[pub_id]='MMB') AND ((((([mc_ad_detail].[adv_type]='RE' OR
[mc_ad_detail].[adv_type]='GE') OR [mc_ad_detail].[adv_type]='GT') O
| |--Hash Match(Right Outer Join, HASH[mc_location].[bus_unit_id], [mc_location].[pub_id],
[mc_location].[trans_id])=([mc_acct_master].[bus_unit_id],
[mc_acct_master].[pub_id], [mc_rep_hist].[rep_num]), RESIDUAL(
| |--Parallelism(Repartition Streams, PARTITION COLUMNS[mc_location].[bus_unit_id],
[mc_location].[pub_id], [mc_location].[trans_id]))
| | |--Clustered Index Scan(OBJECT[bee_dw_dev].[dbo].[mc_location].[mc_acct_location_pk]),
WHERE[mc_location].[end_date]>=getdate() AND
[mc_location].[start_date]<=getdate()))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS[mc_acct_master].[bus_unit_id],
[mc_acct_master].[pub_id], [mc_rep_hist].[rep_num]))
| |--Hash Match(Inner Join, HASH[mc_rep_hist].[bus_unit_id], [mc_rep_hist].[pub_id],
[mc_rep_hist].[acct_num])=([Union1026], [Union1027], [Union1028]),
RESIDUAL([mc_rep_hist].[bus_unit_id]=[Union1026] A
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_rep_hist].[bus_unit_id], [mc_rep_hist].[pub_id],
[mc_rep_hist].[acct_num]))
| | |--Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_rep_hist]),
WHERE[mc_rep_hist].[end_date]>=getdate() AND
[mc_rep_hist].[start_date]<=getdate()))
| |--Hash Match(Inner Join, HASH[mc_acct_master].[bus_unit_id],
[mc_acct_master].[pub_id], [mc_acct_master].[acct_num])=([Union1026],
[Union1027], [Union1028]), RESIDUAL([Union1026]=[mc_acct_master
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_acct_master].[bus_unit_id], [mc_acct_master].[pub_id],
[mc_acct_master].[acct_num]))
| | |--Clustered Index
Scan(OBJECT[bee_dw_dev].[dbo].[mc_acct_master].[mc_acct_master_pk]))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS[Union1026],
[Union1027], [Union1028]))
| |--Hash Match(Inner Join, HASH[mc_ad_detail].[bus_unit_id],
[mc_ad_detail].[pub_id], [mc_ad_detail].[acct_num],
[mc_ad_detail].[ad_num], [mc_ad_insertion].[insertion_num])=([Union1026], [
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_ad_detail].[bus_unit_id], [mc_ad_detail].[pub_id],
[mc_ad_detail].[acct_num], [mc_ad_detail].[ad_num], [mc_ad_insertion].[ins
| | |--Hash Match(Inner Join,
HASH[mc_period_dates].[calendar_date])=([mc_ad_insertion].[insertion_date]))
| | |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_period_dates].[calendar_date]))
| | | |--Clustered Index
Scan(OBJECT[bee_dw_dev].[dbo].[mc_period_dates].[mc_period_dates_pk]))
| | |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_ad_insertion].[insertion_date]))
| | |--Hash Match(Inner Join, HASH[mc_ad_detail].[bus_unit_id],
[mc_ad_detail].[pub_id], [mc_ad_detail].[acct_num],
[mc_ad_detail].[ad_num], [mc_ad_detail].[entry_date_tim
| | |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_ad_detail].[bus_unit_id], [mc_ad_detail].[pub_id],
[mc_ad_detail].[acct_num], [mc_ad_detail].[ad_num], [m
| | | |--Index
Scan(OBJECT[bee_dw_dev].[dbo].[mc_ad_detail].[mc_mb_103region_advtype_indx]))
| | |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_ad_insertion].[bus_unit_id], [mc_ad_insertion].[pub_id],
[mc_ad_insertion].[acct_num], [mc_ad_insertion].
| | |--Clustered Index
Scan(OBJECT[bee_dw_dev].[dbo].[mc_ad_insertion].[mc_ad_insertion_pk]),
WHERE((((((((((([mc_ad_insertion].[prod_code]<>'VIDA' AND [mc_ad_
| |--Concatenation
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_ad_cost].[bus_unit_id], [mc_ad_cost].[pub_id],
[mc_ad_cost].[acct_num], [mc_ad_cost].[ad_num], [mc_ad_cost].[trans_id]))
| | |--Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_ad_cost]))
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS[mc_ad_cost_tmp].[bus_unit_id], [mc_ad_cost_tmp].[pub_id],
[mc_ad_cost_tmp].[acct_num], [mc_ad_cost_tmp].[ad_num], [mc_ad_co
| |--Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_ad_cost_tmp]))
|--Row Count Spool
|--Clustered Index Seek(OBJECT[bee_dw_dev].[dbo].[mc_acct_master].[mc_acct_master_pk]),
SEEK[mc_acct_master].[bus_unit_id]=[mc_acct_master].[bus_unit_id] AND
[mc_acct_master].[pub_id]=[mc_acct_master].[pub_id] AND [mc_acct_ma

(44 row(s) affected)

 

[SQLDenis]

From a quick glance
conversion on the dates (Convert([mc_ad_insertion].[insertion_date]), )
Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_region_vals]))
Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_rep_master]),
Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_rep_hist])
Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_ad_cost]))
Table Scan(OBJECT[bee_dw_dev].[dbo].[mc_ad_cost_tmp]))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[brxmas]

I really appreciate everyones help in resolving why it's taking forever to run this query via 'Stored Procedure'.

What do I need to change to increase the speed? Thanks.

 

[vongrunt]

Here are query relationships:

mc_acct_master_view
	mc_ad_detail (bus_unit_id, pub_id, acct_num)
		mc_ad_insertion (bus_unit_id, pub_id, acct_num, ad_num, entry_date_time)
			mc_ad_cost_view (bus_unit_id, pub_id, acct_num, ad_num, trans_id)
				mc_obj_code_vals (obj_code)
		mc_period_dates (calendar_date)
			mc_period_dates_view (calendar_date)
	mc_region_vals (region)
	mc_territory_vals (territory)

4 (four) levels of nesting, composite keys, plus some of objects involved are views. Ack...

The following objects:

mc_obj_code_vals, mc_territory_vals, mc_period_dates_view

... are referenced only in FROM (joins), not in SELECT or WHERE. What happens if you remove these objects/joins from query (returned number of rows, exec time)? I'm trying to determine this:

- if inner objects (mc_ad_cost_view, mc_acct_master_view and mc_period_dates respectively) have no orphaned data these three objects/joins are not needed in query at all. Fewer joins = definitely faster.

- if these objects/joins must be used (orphaned data present, joins used for filtering) AND exec time becomes significantly smaller, it is worth to consider splitting query into two smaller steps - with temp table/table variable for holding intermediate results

Also: this code:

...
    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)

... looks suspect. AND has higher precedence than OR... is existing logic OK or some extra braces () are missing around ORed expressions?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[brxmas]

That's for everyone's input. I have the query down to 5 min and 12 seconds.....Thanks again.