SQL Server Performance

sproc tuning..

Discussion in 'T-SQL Performance Tuning for Developers' started by rohitkochar, Jun 6, 2007.

  1. rohitkochar New Member

    HI ,
    I have tuned a sproc...initially it took 2 minutes ( 12381 rows ) to run but second time it took about 45 seconds ....and on production server to my very surprise it took about
    7 minutes but next time it runs in 20 sec....So can we considered it tuned ???
    What i mean to say...execution time of sproc when it run first time should be neglected
    or not beacause in many cases it run in half the time in second attempt..
    but if i run the command DBCC dropcleanbuffers..it again take the same time..
    I have used temp tables and table variables heavily in my sproc with clustered index
    on temp table...
    Regrds,


    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  2. Adriaan New Member

    Looks like cached data is improving the response time on the second run. Could you post the (skeleton) sproc?
  3. rohitkochar New Member

    Hi Adriaan ,
    Right sql query analyzer is picking it from cache....But I am confused whether it has to do
    something with TempDB or not ?? I m using temporary table and table vaiables in my sproc
    heavily which are stored in tempDB and since restatrting the server clean the tempDb as it is
    again then copied from Model DB ..itz size is also set to initial one...that means every time I
    shut dowm my server all my sprocs using ( temp table and variable) when run first time will take time and in second time they will run fast....
    Anyways I am sending u the proc..Please tell me where i need to tuned it again...
    **********************---------------------------------*********************************************


    alter Procedure [dbo].[sproc_PPM_RPTTotalDepositsDueFromProperties_new]
    (
    @ip_UrbanEstateCode varchar (5),
    @ip_SectorId varchar (20)=null ,
    @ip_CategoryCode varchar (5) =null,
    @ip_SubCategoryCode varchar (5)=null ,
    @ip_PlotNumber varchar(20)=null,
    @ip_LocationCode varchar(5)
    )

    AS
    Set nocount on
    Declare @PlotId int,
    -- @GLdescription varchar(150),
    @DueDate varchar(50),
    -- @AmountToBePaid decimal(14,2),
    @FullName varchar(82),
    @FatherFullName varchar(82),
    @TotalCost decimal(14,2),
    @UrbanEstateName varchar(40),
    @SectorId varchar(20),
    @CategoryName varchar(40),
    @SubCategoryName varchar(40),
    @PlotNumber varchar(20),
    @DueAmount decimal(14,2),
    @PaymentCategory varchar(90),
    @AccountCode varchar(5),
    @LastPaymentDate datetime,
    @TotalInterestPaid decimal(14,2),
    @EnhancementInterestAmt decimal(14,2),
    @PossessionInterestAmt decimal(14,2),
    @TotalAmount decimal(14,2)
    SET @TotalAmount = 0


    create table #TempTable
    (

    FullName varchar(82),
    FatherFullName varchar(82),
    TotalCost decimal(14,2) ,
    UrbanEstateName varchar(40),
    SectorId varchar(20),
    CategoryName varchar(40),
    SubCategoryName varchar(40),
    PlotNumber varchar(20),
    GLDescription varchar(150),
    DueAmount decimal(14,2),
    DueDate varchar(30),
    PlotID int
    )



    ---Print ' B0 : Begins account table insert '+' : '+ convert(varchar,getdate(),109)
    declare @temp_tb_AccountCodeMaster table
    (
    TempAccountCode varchar(5),
    TempGL_Code int
    primary key (TempAccountCode)
    )

    insert into @temp_tb_AccountCodeMaster
    select AccountCode , GL_Code from tb_AccountCodeMaster

    --Print ' B0 : end account table insert '+' : '+ convert(varchar,getdate(),109)

    ---Print ' B1 : Begins curent ouststtable '+' : '+ convert(varchar,getdate(),109)
    declare @Temptb_ppm_CurrentOutstanding TABLE
    (
    TempCuOustPlotId int,
    TempCuOustGLCode int,
    TempCuOustCurrentOutstandingAmount decimal(14,2)

    )

    insert into @Temptb_ppm_CurrentOutstanding
    select PlotId ,GLCode ,CurrentOutstandingAmount from
    tb_ppm_CurrentOutstanding where CurrentOutstandingAmount > 0
    and PartitionColumn = PlotId%8

    --Print ' B1 : curent ouststtable insert ends '+' : '+ convert(varchar,getdate(),109)

    ---Print ' B2 : payment adjustment insert '+' : '+ convert(varchar,getdate(),109)
    Declare @Temp_PaymentAdjustment table
    (PlotId int , PaymentId int, AdjustedPaymentAmount decimal(14, 2) )

    Insert into @Temp_PaymentAdjustment
    Select PlotId, PaymentId , AdjustedPaymentAmount
    from Tb_PPM_AdjustmentPayment
    where GLCode in ((Select TempGL_Code FROM @temp_tb_AccountCodeMaster where
    TempAccountCode in ('ENINT','POINT')))
    and PartitionColumn = PlotId%8

    ---Print ' B2 : ends payment adjustment insert '+' : '+ convert(varchar,getdate(),109)

    /*create temp table for Tb_PPM_PaymentSchedule aded by rohit 6/6/2006*/
    ---Print ' B3 : Begins PaymentSchedule '+' : '+ convert(varchar,getdate(),109)
    create table #TempTb_PPM_PaymentSchedule
    (
    TempPPSPlotid int ,
    TempPPSGlcode int ,
    TempPPSPrincipalDueAmount decimal(14,2),
    TempPPSEnhancementNumber varchar(20),
    TempPPSDueDate datetime
    )

    insert into #TempTb_PPM_PaymentSchedule
    select Plotid , Glcode , PrincipalDueAmount , EnhancementNumber , DueDate
    from Tb_PPM_PaymentSchedule where GLCOde in
    (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where
    TempAccountCode in ('ENHAN','INSTA') )
    and PartitionColumn = Plotid%8

    createclustered index IndPPS on #TempTb_PPM_PaymentSchedule
    (TempPPSPlotid)

    ---Print ' B3 : end insert PaymentSchedule '+' : '+ convert(varchar,getdate(),109)


    ---Print ' B4 : Begins curr outstanding account '+' : '+ convert(varchar,getdate(),109)
    create table #TempTb_PPM_CurrentOutstandingAccount
    (

    TempCOAAsOnDate datetime ,
    TempCOAPlotId int,
    TempCOAGLCODE int ,
    TempCOAOSCalculationFlag varchar(1),
    TempCOAOutstandingInterest decimal(14,2)
    )

    insert into #TempTb_PPM_CurrentOutstandingAccount
    select AsOnDate , PlotId , GLCODE , OSCalculationFlag , OutstandingInterest
    from Tb_PPM_CurrentOutstandingAccount where OSCalculationFlag <> 'M'
    AND PartitionColumn = plotid% 8 and
    GLCODE in
    (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode in ('ENINT','POINT')
    )


    create clustered index IndCurrentOutstandingAccount on #TempTb_PPM_CurrentOutstandingAccount
    (TempCOAPlotId )

    --Print ' B4 : curr outstanding account insert ends '+' : '+ convert(varchar,getdate(),109)


    ---temp table for table Tb_PPM_PlotDetail rohit kochar 7/6/2007
    ---Print ' B6 : insert plot details '+' : '+ convert(varchar,getdate(),109)
    create table #TempTb_PPM_PlotDetail
    (
    Name varchar(40) ,
    Surname varchar(40) ,
    FatherName varchar(40) ,
    FatherSurname varchar(40) ,
    IncreasedCost decimal(14,2) ,
    LocationCost decimal(14,2) ,
    Cost decimal(14,2) ,
    PlotId int ,
    SectorId varchar(20),
    PlotNumber varchar(20),
    UrbanEstateCode varchar(5) ,
    CategoryCode varchar(5) ,
    SubCategoryCode varchar(5)

    )


    insert into #TempTb_PPM_PlotDetail
    select Name ,Surname ,FatherName ,FatherSurname ,IncreasedCost ,
    LocationCost ,Cost ,PlotId ,SectorId ,PlotNumber ,UrbanEstateCode ,
    CategoryCode ,SubCategoryCode from Tb_PPM_PlotDetail
    where UrbanEstateCode = @ip_UrbanEstateCode
    and PartitionColumn = plotid%8

    create clustered index IndPPD on #TempTb_PPM_PlotDetail
    (PlotId)

    ---Print ' B6 : insert end plot details '+' : '+ convert(varchar,getdate(),109)



    ---Print ' B5 : enter cursor '+' : '+ convert(varchar,getdate(),109)
    declare Curs_1 cursor for
    select
    upper(substring(PPD.Name,1,1))+lower(substring(PPD.Name,2,len(PPD.Name)))+' '+upper(substring(PPD.Surname,1,1))+lower(substring(PPD.Surname,2,len(PPD.Surname))) AS FullName,
    upper(substring(PPD.FatherName,1,1))+lower(substring(PPD.FatherName,2,len(PPD.FatherName)))+' '+upper(substring(PPD.FatherSurname,1,1))+lower(substring(PPD.FatherSurname,2,len(PPD.FatherSurname))) AS FatherFullName,
    --TotalCost,
    Isnull(PPD.IncreasedCost,0) + Isnull(PPD.LocationCost,0) + isnull(PPD.Cost,0) + (Select ISNull(SUM(TempPPSPrincipalDueAmount),0) FROM #TempTb_PPM_PaymentSchedule
    where TempPPSPlotId = PPD.PlotId
    --and EnhancementNumber <> '0' commented on 4th may'07
    and TempPPSGLCOde in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode = 'ENHAN')) +
    (Select ISNULL(SUM(EnhancementCost),0) FROM tb_PPM_PlotMemoDetail
    where PlotId = PPD.PlotId
    and LetterName = 'DELET'
    and EnhancementCost is not null
    and EnhancementNumber not in (Select TempPPSEnhancementNumber FROM #TempTb_PPM_PaymentSchedule
    where TempPPSPlotId = PPD.PlotId
    --and EnhancementNumber = '0' commented on 4th may'07
    and TempPPSGLCOde in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode = 'ENHAN')))
    AS TotalCost,
    b.UrbanEstateName,
    PPD.SectorId,
    c.CategoryName,
    d.SubCategoryName,
    PPD.PlotNumber,
    PPD.PlotId,
    convert(varchar,getdate(),103) As DueDate,
    Curr.TempCuOustCurrentOutstandingAmount As DueAmount,
    coa.GL_Description AS PaymentCategory ,
    AcMas.TempAccountCode
    FROM #TempTb_PPM_PlotDetail PPD,
    Tb_PPM_UrbanEstateMaster b,
    Tb_PPM_PropertyCategoryMaster c,
    Tb_PPM_PropertySubCategoryMaster d,
    Tb_PPM_EstateOfficeMaster e,
    @temp_tb_AccountCodeMaster AcMas,
    @Temptb_ppm_CurrentOutstanding Curr,
    Tb_FAS_COA_Mast coa

    WHERE Curr.TempCuOustGLCode = coa.GL_Code and Curr.TempCuOustCurrentOutstandingAmount > 0
    AND PPD.plotid = Curr.TempCuOustPlotId
    and AcMas.TempGL_Code = Curr.TempCuOustGLCode
    AND PPD.UrbanEstateCode = @ip_UrbanEstateCode
    AND PPD.UrbanEstateCode = b.UrbanEstateCode
    AND PPD.SectorId = isnull(@ip_SectorId,PPD.SectorId)
    AND PPD.CategoryCode = isnull(@ip_CategoryCode,PPD.CategoryCode)
    AND PPD.CategoryCode = c.CategoryCode
    AND PPD.SubCategoryCode = isnull(@ip_SubCategoryCode,PPD.SubCategoryCode)
    AND PPD.SubCategoryCode = d.SubCategoryCode
    AND PPD.CategoryCode = d.CategoryCode
    AND PPD.PlotNumber = isnull(@ip_PlotNumber,PPD.PlotNumber)
    AND e.EstateOfficeCode = b.EstateOfficeCode
    and convert(datetime,Convert(varchar,getdate(),102)) BETWEEN convert(datetime,Convert(varchar, b.DateFrom,102)) AND IsNull( convert(datetime,Convert(varchar, b.DateTo ,102)) , convert(datetime,Convert(varchar,getdate(),102)))
    and convert(datetime,Convert(varchar,getdate(),102)) BETWEEN convert(datetime,Convert(varchar, e.DateFrom,102)) AND IsNull( convert(datetime,Convert(varchar, e.DateTo ,102)) , convert(datetime,Convert(varchar,getdate(),102)))

    AND (@ip_LocationCode = 'HO001' OR e.ZoneCode = @ip_LocationCode OR e.EstateOfficeCode = @ip_LocationCode)

    open Curs_1
    FETCH NEXT FROM Curs_1 INTO @FullName, @FatherFullName, @TotalCost,@UrbanEstateName,@SectorId,
    @CategoryName,@SubCategoryName,@PlotNumber,@PlotId,@DueDate,@DueAmount,@PaymentCategory,@AccountCode
    WHILE @@fetch_status=0
    BEGIN



    if (@AccountCode =('DIINS'))
    BEGIN
    SELECT @DueAmount = SUM(TempCuOustCurrentOutstandingAmount)
    FROM @Temptb_ppm_CurrentOutstanding
    WHERE TempCuOustPlotId = @Plotid
    AND TempCuOustGLCode IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where
    TempAccountCode in ('DIINS','DIPOA','DIIAC'))

    END

    if (@AccountCode = 'ENINT')
    begin
    select @LastPaymentDate= IsNull(MAX(TempCOAAsOnDate), '01/01/1800')
    from #TempTb_PPM_CurrentOutstandingAccount where TempCOAPlotId = @Plotid

    and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'ENINT')
    and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) < convert(datetime,Convert(varchar,getdate(),102))
    and ( (convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) in (Select convert(datetime,Convert(varchar,TempPPSDueDate,102)) From
    #TempTb_PPM_PaymentSchedule where TempPPSplotid = @Plotid
    and TempPPSGLCode IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where
    TempAccountCode = 'ENHAN')))
    /*OR
    (convert(datetime,Convert(varchar,AsONDate,102)) in (Select convert(datetime,Convert(varchar,PaymentDate,102)) from tb_PPM_PaymentReceived
    where Plotid = @ip_PlotId and PaymentFlag = 'P' and PaymentId in (Select PaymentId from
    Tb_PPM_AdjustmentPayment where PlotId = @ip_PlotId and
    GLCode in (Select GL_Code from tb_AccountCodeMaster where AccountCode IN ('ENINT', 'ENHAN','DIENH')))))
    OR
    (convert(datetime,Convert(varchar,AsOnDate,102)) in (Select convert(datetime,Convert(varchar,ValidStartDate-1,102)) From tb_PPM_InterestRateMaster where InterestType = 'ENHAN')) */
    )
    ---AND TempCOAOSCalculationFlag <> 'M'


    select @EnhancementInterestAmt = IsNull(SUM(TempCOAOutstandingInterest),0) from
    #TempTb_PPM_CurrentOutstandingAccount
    where TempCOAPlotId = @Plotid
    and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'ENINT')
    and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) =
    convert(datetime,Convert(varchar,@LastPaymentDate,102))
    ---and TempCOAOSCalculationFlag <> 'M'

    SELECT @TotalInterestPaid = IsNull(SUM(AdjustedPaymentAmount) ,0)
    -- FROM Tb_PPM_AdjustmentPayment
    FROM @Temp_PaymentAdjustment
    WHERE PlotId = @Plotid
    --AND GLCODE in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode in ('ENINT'))
    AND PaymentId in (select PaymentId from tb_PPM_PaymentReceived
    where plotId = @Plotid
    and convert(datetime,Convert(varchar,paymentdate,102)) between convert(datetime,Convert(varchar,@LastPaymentDate,102)) and convert(datetime,Convert(varchar,getdate(),102)) )

    SET @DueAmount = @EnhancementInterestAmt - @TotalInterestPaid

    IF @DueAmount < 0
    SET @DueAmount = 0

    end



    --SET @TotalAmount = @TotalAmount - @EnhancementInterestAmt

    if (@AccountCode = 'POINT')
    begin
    select @LastPaymentDate= IsNull(MAX(TempCOAAsOnDate), '01/01/1800')
    from #TempTb_PPM_CurrentOutstandingAccount where TempCOAPlotId = @Plotid

    and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'POINT')
    and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) < convert(datetime,Convert(varchar,getdate(),102))
    and ( (convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) in (Select convert(datetime,Convert(varchar,TempPPSDueDate,102)) From #TempTb_PPM_PaymentSchedule where TempPPSplotid = @Plotid
    and TempPPSGLCode IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'INSTA')))
    /*OR
    (convert(datetime,Convert(varchar,AsONDate,102)) in (Select convert(datetime,Convert(varchar,PaymentDate,102)) from tb_PPM_PaymentReceived
    where Plotid = @ip_PlotId and PaymentFlag = 'P' and PaymentId in (Select PaymentId from
    Tb_PPM_AdjustmentPayment where PlotId = @ip_PlotId and
    GLCode in (Select GL_Code from tb_AccountCodeMaster where AccountCode IN ('POINT', 'INSTA')))))
    OR
    (convert(datetime,Convert(varchar,AsOnDate,102)) in (Select convert(datetime,Convert(varchar,ValidStartDate-1,102)) From tb_PPM_InterestRateMaster where InterestType = 'POSES')) */
    )
    ---AND TempCOAOSCalculationFlag <> 'M'



    select @PossessionInterestAmt = IsNull(SUM(TempCOAOutstandingInterest),0) from #TempTb_PPM_CurrentOutstandingAccount
    where TempCOAPlotId = @Plotid
    and TempCOAGLCODE IN (Select TempGL_Code from @temp_tb_AccountCodeMaster where TempAccountCode = 'POINT')
    and convert(datetime,Convert(varchar,TempCOAAsOnDate,102)) = convert(datetime,Convert(varchar,@LastPaymentDate,102))
    --- and TempCOAOSCalculationFlag <> 'M'

    SELECT @TotalInterestPaid = IsNull(SUM(AdjustedPaymentAmount) ,0)
    FROM @Temp_PaymentAdjustment
    WHERE PlotId = @Plotid
    -- AND GLCODE in (Select TempGL_Code FROM @temp_tb_AccountCodeMaster where TempAccountCode in ('POINT'))
    and PaymentId in (select PaymentId from tb_PPM_PaymentReceived
    where plotId = @Plotid
    and convert(datetime,Convert(varchar,paymentdate,102)) between convert(datetime,Convert(varchar,@LastPaymentDate,102)) and convert(datetime,Convert(varchar,getdate(),102)) )

    SET @DueAmount = @PossessionInterestAmt - @TotalInterestPaid

    IF @DueAmount < 0
    SET @DueAmount = 0
    end
    if (@AccountCode not in ('DIPOA','DIIAC') )
    insert into #TempTable
    (
    FullName ,
    FatherFullName ,
    TotalCost ,
    UrbanEstateName ,
    SectorId ,
    CategoryName ,
    SubCategoryName ,
    PlotNumber ,
    GLDescription ,
    DueAmount ,
    DueDate ,
    PlotId
    )
    values
    (
    @FullName,
    @FatherFullName,
    @TotalCost,
    @UrbanEstateName,
    @SectorId,
    @CategoryName,
    @SubCategoryName,
    @PlotNumber,
    @PaymentCategory,
    @DueAmount,
    @DueDate,
    @PlotId
    )

    FETCH NEXT FROM Curs_1 INTO @FullName, @FatherFullName, @TotalCost,@UrbanEstateName,@SectorId,
    @CategoryName,@SubCategoryName,@PlotNumber,@PlotId,@DueDate,@DueAmount,@PaymentCategory,@AccountCode
    end
    close Curs_1
    deallocate Curs_1
    ---Print ' B5 : end cursor '+' : '+ convert(varchar,getdate(),109)


    ---Print ' B6 : drop and select to temptable'+' : '+ convert(varchar,getdate(),109)
    drop table #TempTb_PPM_PaymentSchedule
    drop table #TempTb_PPM_CurrentOutstandingAccount
    drop table #TempTb_PPM_PlotDetail

    create clustered index CITempTable on #TempTable (dueamount)

    select * from #TempTable where dueamount <> 0
    order by UrbanEstateName,
    CASE WHEN (ascii(LEFT(SectorId,1)) between 48 and 57) THEN
    convert(integer, dbo.translate(SectorId,'-ABCDEFGHIJKLMNOPQRSTUVWXYZ','')) ELSE 999999999 END, sectorid,
    CategoryName, convert(integer, dbo.translate(PlotNumber, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',''))



    drop table #TempTable
    ---Print ' B6 : drop and select to temptable ends'+' : '+ convert(varchar,getdate(),109)












    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  4. Adriaan New Member

    Caching will affect only data read from permanent tables (and perhaps global temp tables, but you don't have any of those).

    Why the mix of temp tables and table variables? I would stick to variables - better chance of things getting handled in memory, not in tempdb (no guarantee, though).

    Did you notice that the cursor repeats for the number of rows found in that table variable, but the assignment that you do in the loop is not cumulative, and you are not storing the SUM value anywhere else? So after the loop, the value in @DueAmount is that of the last iteration only.

    The subqueries might also be less than ideal.

    With this type of processing, I always wonder how much of it can be done in a single query ...

    You start by taking the queries that fill the temp tables and table variables, and use them as derived tables in your FROM clause (leave the joins until later). If this involves any aggregate functions, don't worry at this point. Don't forget to include the relevant filter criteria inside the derived tables.

    You then add the table that you query in the cursor, and start replacing the parameter(s) by appropriate joins.

    You then add any additional tables you need to get the results.

    Happy restructuring!
  5. rohitkochar New Member

    Well , As I read and experienced table variables are faster than temporaray table but
    in case where original table you used to populate table variables contains more data
    it is better to use temporaray table with clustered index
    You can read it here
    http://www.sql-server-performance.com/dt_temp_tables_vs_variables.asp

    I have used table variable with a primary key where I knew it beforehand that number of
    rows is not that much coz with large data table variable doesnt give that good
    performance ...I dont have much idea about Derrived tables I will go through it now...
    It was a gud analysis Adriaan...

    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  6. Adriaan New Member

    If we're talking about table variables that are not silently done as temp tables, then the amount of data is probably not big enough so that you would notice much difference in performance for clustered or nonclustered.<br /><br />The idea behind derived tables is that SQL can work out the best strategy by itself, using the indexes that already exist. If you're telling SQL to move data into a temp table, then it will follow your strategy (which might still be the best one, for all I know [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]) and you have to make sure that your temp tables have supporting indexes for the operation.
  7. rohitkochar New Member

    Fine i will go through derived tables ....one non technical qs <img src='/community/emoticons/emotion-1.gif' alt=':)' />...<br />what does this 5 star below ur name shows????<br /><br />Rohit Kochar<br />MCP .Net(Web and SqlServer)<br />Company : TCS ,Noida
  8. Adriaan New Member

    It's related to the number of posts I've made on this forum, nothing else.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]

Share This Page