none
Tuning a calculated field and sub query.

    Question

  • hi ,

    I'm trying to optimize a stored procedure which has a lengthy expression and a subquery . Can any one help me out to fine tune below expresion and a join condition.

    extension_out = count(distinct 
    			case when (coalesce(rtrim(ltrim(f.origlogin)),'')<>'' and f.conference <> 1 and f.transferred <> 1 and d.destination_dim_id! = 0
    			and coalesce(rtrim(ltrim(f.origlogin)),'')<>coalesce(rtrim(ltrim(f.anslogin)),'')) then f.callid else null end),
    		
    warm_transfer = count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.conference = 1 then f.callid else null end),
    		cold_transfer = count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.transferred = 1 then f.callid else null end),
    	
    ------------------------
    -----------------------
    --Sub Query
    ----------------------
    ---------------------
    join etl_dm.dbo.tblcchassociatehierarchybridge b (nolock) on f.date_id = b.date_id and mp.date_id = b.date_id and h.associate_id = b.associate_id 
    
    		where not exists(
    			select 1 from etl_dm.dbo.tblcct_work_excludemu ex (nolock)
    			where ex.metric_table_id = @metric_table_id
    					and ex.mu_id = h.iex_mu_id 
    					and ex.start_effective_date <= @date
    					and (ex.end_effective_date is null or ex.end_effective_date >= @date))
    

    Thansk for help !

    Thursday, March 27, 2014 5:23 PM

Answers

  • That's a start, but as I said, to be able to give good advice, we also need to see the CREATE TABLE and CREATE INDEX statement for the involved tables. We also need to see the actual query plan, in XML form. (Which you need to upload on SkyDrive, Dropbox, or elsewhere, since the forum does not permit attachments.)

    Nevertheless, I can make one suggestion from the query text alone. Test what happens if you change the table variable @metricperiod to be a temp table instead. That may seem like a meaningless change, but table variables preclude parallelism, while temp tables do not. So this could have a drastic effect. Or none at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 29, 2014 9:37 AM

All replies

  • Generally it is not possible to help with optimization from only the fragment of a query. Not only do we like to see the full query, we also find it helpful to see CREATE TABLE and CREATE INDEX statements and the actual query plan.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 27, 2014 11:15 PM
  • INSERT INTO #e2e_fact_aggregates (
    		date_id,
    		associate_id,
    		metric_period_id,
    		[level],
    		[source],
    		answered,
    		warm_transfer,
    		cold_transfer,
    		extension_out,
    		total_transfer,
    		icr_excluded)
    		-- Aggregate avaya fact data for all metric periods and perform rollups
    		SELECT 
    		date_id = @date_id,
    		associate_id = b.manager_associate_id,
    		metric_period_id = mp.metric_period_id,
    		level = b.level,
    		source = 'avaya',
    		answered = COUNT(DISTINCT 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and coalesce(rtrim(ltrim(f.anslogin)),'') = rtrim(ltrim(h.acd_id)) 
    			then f.callid else null end),
    		warm_transfer = count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.conference = 1 then f.callid else null end),
    		cold_transfer = count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.transferred = 1 then f.callid else null end),
    		extension_out = count(distinct 
    			case when (coalesce(rtrim(ltrim(f.origlogin)),'')<>'' and f.conference <> 1 and f.transferred <> 1 and d.destination_dim_id! = 0
    			and coalesce(rtrim(ltrim(f.origlogin)),'')<>coalesce(rtrim(ltrim(f.anslogin)),'')) then f.callid else null end),
    		total_transfer =
    			isnull(count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.conference = 1 then f.callid else null end),0)
    			+
    			isnull(count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.transferred = 1 then f.callid else null end),0)
    			+
    			isnull(count(distinct 
    			case when (coalesce(rtrim(ltrim(f.origlogin)),'')<>'' and f.conference <> 1 and f.transferred <> 1 and d.destination_dim_id! = 0
    			and coalesce(rtrim(ltrim(f.origlogin)),'')<>coalesce(rtrim(ltrim(f.anslogin)),'')) then f.callid else null end),0),
    		icr_excluded = 	
    			isnull(count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.conference = 1 and d.exclude_icr = 1 then f.callid else null end),0)
    			+
    			isnull(count(distinct 
    			case when coalesce(rtrim(ltrim(f.anslogin)),'')<>'' and f.transferred = 1 and d.exclude_icr = 1 then f.callid else null end),0)
    			+
    			isnull(count(distinct 
    			case when (coalesce(rtrim(ltrim(f.origlogin)),'')<>'' and f.conference <> 1 and f.transferred <> 1 and d.destination_dim_id! = 0
    			and coalesce(rtrim(ltrim(f.origlogin)),'')<>coalesce(rtrim(ltrim(f.anslogin)),'')) and d.exclude_icr = 1 then f.callid else null end),0)
    
    		from dbo.tble2e_fact_call_segment_avaya as f with(nolock)
    		join @metricperiod mp on f.date_id = mp.date_id
    		join dbo.tble2e_dim_destination as d with(nolock) on f.destination_dim_id = d.destination_dim_id
    		join etl_dm.dbo.tblcchassociatehierarchymaster as h with(nolock) on f.associate_dim_id = h.associate_dim_id
    		join etl_dm.dbo.tblcchorghierarchymaster as o with(nolock) on f.org_dim_id = o.org_dim_id
    		join etl_dm.dbo.tblcchassociatehierarchybridge b (nolock) on f.date_id = b.date_id and mp.date_id = b.date_id and h.associate_id = b.associate_id 
    
    		where not exists(
    			select 1 from etl_dm.dbo.tblcct_work_excludemu ex (nolock)
    			where ex.metric_table_id = @metric_table_id
    					and ex.mu_id = h.iex_mu_id 
    					and ex.start_effective_date <= @date
    					and (ex.end_effective_date is null or ex.end_effective_date >= @date))
    
    		and f.date_id = @mp_date_id
    
    		group by
    		b.manager_associate_id,
    		mp.metric_period_id,
    		b.level
    
    Sure, here is the entire query.... thanks for help !
    Friday, March 28, 2014 12:50 PM
  • That's a start, but as I said, to be able to give good advice, we also need to see the CREATE TABLE and CREATE INDEX statement for the involved tables. We also need to see the actual query plan, in XML form. (Which you need to upload on SkyDrive, Dropbox, or elsewhere, since the forum does not permit attachments.)

    Nevertheless, I can make one suggestion from the query text alone. Test what happens if you change the table variable @metricperiod to be a temp table instead. That may seem like a meaningless change, but table variables preclude parallelism, while temp tables do not. So this could have a drastic effect. Or none at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 29, 2014 9:37 AM