SQL Server Performance

How do increase this procedure performance?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by amitadmin, Feb 29, 2008.

  1. amitadmin New Member

    Hi
    Please suggest me how do increase this stored procedure performance.

    CREATE PROCEDURE [dbo].[HV2_rptAttributeTrendingDetailByMonth]
    @ClientID int,
    @CampaignList varchar(1000),
    @LocationList varchar(1000),
    @VendorList varchar(1000),
    @AgentList varchar(1000),
    @ShowByDate int,
    @YearMonth char(6)
    AS
    /****************************************************************************************************************************
    * Date: 11 th Feb, 2008
    * Author: Biswajit Das, HQ
    * Purpose: Monthly Attribute Trending Detail
    * Client: Cingular
    * Notes:

    Kodiak--
    exec HV2_rptAttributeTrendingDetailByMonth_J1382 2,'2','2','2','855',1,'200709'
    **POPCORN
    exec HV2_rptAttributeTrendingDetailByMonth 2,'0','0','0','2',1,'200802'
    Duke

    *****************************************************************************************************************************/
    BEGIN
    Declare @Year char(4), @Month integer
    Declare @EvalStartDate datetime, @EvalEndDate datetime
    Declare @CallStartDate datetime, @CallEndDate datetime
    Declare @YearEnd char(10), @OverAllScore numeric(15,2), @Goal float

    --set last day of year for filter on LeaveDate
    set @YearEnd = '12/31/' + cast(year(getdate()) as char(4))
    set @Year = left(@YearMonth,4)
    set @Month = cast(right(@YearMonth,2) as integer)
    set @CallStartDate = case when @ShowByDate = 1 then DateAdd(m,-5,Convert(Datetime,cast(@Month as varchar(2))+ '/1/' + @Year)) else '01/01/1900' end
    set @CallEndDate = case when @ShowByDate = 1 then dateadd(d,-1,(dateadd(m,6,@CallStartDate))) else '12/31/2050' end
    set @EvalStartDate = case when @ShowByDate = 2 then DateAdd(m,-5,Convert(Datetime,cast(@Month as varchar(2))+ '/1/' + @Year)) else '01/01/1900' end
    set @EvalEndDate = case when @ShowByDate = 2 then dateadd(d,-1,(dateadd(m,6,@EvalStartDate))) else '12/31/2050' end


    CREATE TABLE #users
    (userid int)

    INSERT INTO #users
    select UserID from dbo.getDirectReportsCVL(@ClientID,@AgentList,@CampaignList,@LocationList,@VendorList,1)

    DECLARE @MonthTable Table
    (
    CalendarYear int
    ,MonthNumber int
    ,MonthBeginDate datetime
    ,MonthEndDate datetime
    ,PRIMARY KEY (CalendarYear, MonthNumber)
    )


    select @Goal = max(UpperLimit)
    from dbo.Threshold
    where ClientID = @ClientID
    and Type = 'Goal'

    if (@month<6)
    begin
    Declare @Yearprevious char(6)
    set @Yearprevious =@Year-1
    insert @MonthTable
    select CalendarYear, MonthNumber, min(FullDateID), max(FullDateID)
    from dbo.DimDate
    where CalendarYear =@Yearprevious and MonthNumber between left(convert(CHAR(11),@CallStartDate,101),2) and 12
    group by CalendarYear, MonthNumber

    insert @MonthTable
    select CalendarYear, MonthNumber, min(FullDateID), max(FullDateID)
    from dbo.DimDate
    where CalendarYear =@Year and MonthNumber between @Month-5 and @Month
    group by CalendarYear, MonthNumber
    End

    else
    begin

    insert @MonthTable
    select CalendarYear, MonthNumber, min(FullDateID), max(FullDateID)
    from dbo.DimDate
    where CalendarYear =@Year and MonthNumber between @Month-5 and @month
    group by CalendarYear, MonthNumber

    end

    /****Select CallStarDate & Endates as per Month dates as discussed **** */
    Set @CallStartDate = (Select min(MonthBeginDate) from @MonthTable)
    Set @CallEndDate = (Select max(MonthEndDate) from @MonthTable)


    --select * from @MonthTable


    select
    m.CalendarYear
    ,m.MonthNumber
    ,m.MonthBeginDate
    ,m.MonthEndDate
    ,q.QuestionID
    ,f.CallEvalID
    ,f.CallID
    ,q.GroupSortOrder
    ,q.QuestionGroupName
    ,q.QuestionSortOrder
    ,q.QuestionName
    ,form.SortOrder as FormSortOrder
    -- ,form.FormName
    -- ,f.ActualPoints
    -- ,q.PossiblePoints
    ,case when f.ActualPoints < 0 then 0 else q.PossiblePoints end as PossiblePoints
    ,case when f.ActualPoints < 0 then 0 else f.ActualPoints end as ActualPoints
    ,f.AgentID
    ,ans.AnswerName

    ,@Goal as Goal
    ,ff.weightedscore as InitialAP
    ,ff.weight as InitialPP

    from


    factlineitem f
    join dbo.DimAnswer ans on (f.AnswerID=ans.AnswerID)
    join dbo.DimDate d on d.FullDateID = case when @ShowByDate = 1 then f.CallDate else f.EvalDate end
    join dbo.DimQuestion q on f.QuestionID = q.QuestionID
    join @MonthTable m on d.CalendarYear = m.CalendarYear and d.MonthNumber = m.MonthNumber
    join dbo.DimForm form on form.formid = q.formid
    join dbo.FactCallEvaluation fc on f.CallEvalID=fc.CallEvalID
    join dbo.FactFormEvaluation ff on f.CallID=ff.CallID and ff.FormID=f.FormID

    where
    f.ClientID = @ClientID
    and (f.CampaignId in (select Param from dbo.getCSVtoTable(@CampaignList,',')) or @CampaignList = '0')
    and (f.LocationId in (select Param from dbo.getCSVtoTable(@LocationList,',')) or @LocationList = '0')
    and (f.VendorId in (select Param from dbo.getCSVtoTable(@VendorList,',')) or @VendorList = '0')
    and (f.CallDate >= @CallStartDate and f.CallDate <= @CallEndDate)
    and (f.EvalDate >= @EvalStartDate and f.EvalDate <= @EvalEndDate)
    --and ans.AnswerName not in ('N/A' ,'NA')
    and q.PossiblePoints > 0
    and q.QuestionGroupName <> ' '
    and (q.AutofailFlag is null OR q.AutofailFlag = 'N')
    and q.deductFlag is Null
    and ans.Elementname<>'COMMENT'
    and ans.Elementname<>'Comments'
    and fc.evalcount=1
    and q.ScoreableFlag = 'Y'
    and (f.AgentID in (select distinct UserID from #users))
    order by
    form.FormID,
    form.SortOrder,
    q.GroupSortOrder,
    q.QuestionSortOrder




    END





  2. satya Moderator

Share This Page