SQL Server Performance

Suggestions needed for SQL Server 2000

Discussion in 'T-SQL Performance Tuning for Developers' started by abi, Oct 18, 2005.

  1. abi New Member

    Hi,
    I have a Stored Procedure(SP) in SQL Server 2000. The function of this SP is to delete outdated records from several tables based on some conditions. The SP has a series of "Delete" statements only.

    The execution of this SP happens only in Midnight, so as not to affect any database actvities during peak hours. The problem is that, this SP takes more than 20 minutes to accomplish its deletion.
    Can you suggest any ways of improving this procedure, so that the Lead time taken for deletion is reduced?
    Expecting your replies soon.

    Thanks
    Abilash
  2. satya Moderator

    Does the underlying tables has clustered indexes?
    Post the code used to delete the rows and ensure to wrap them in transactions in order to takeup the sizes during the process.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Twan New Member

    or is there any declarative Referential Integrity which hasn't been indexed appropriately?

    e.g. delete from a parent table checks that there are no children in a child table but the FK in the child table isn't indexed?

    Cheers
    Twan
  4. abi New Member


    The underlying tables doesn't have any Clustered Indexes.
    I shall make sure Indexes are created for the tables.

    "Post the code used to delete the rows and ensure to wrap them in transactions in order to takeup the sizes during the process"
    I dont understand the above statement. Could you be a little more precise?

    Thanks
    Abilash
  5. satya Moderator

  6. abi New Member

    Here im posting the code for the SP below.

    /****** Object: Stored Procedure dbo.del_outdated_reports Script Date: 21/09/2005 11:16:25 ******/


    CREATE proc dbo.del_outdated_reports -- This SP called as Midnight Delete Process
    as
    begin
    declare @ReportDateForDBUsers int,
    @ReportDateForNonDBUsers int,
    @DataElementDate int,
    @ReportDataForInvestReport int

    select @ReportDateForDBUsers = ConfigValue
    from Configuration
    where ConfigCode = 'ReportDurationForDBUsers'

    select @ReportDateForNonDBUsers = ConfigValue
    from Configuration
    where ConfigCode = 'ReportDurationForNonDBUsers'

    select @ReportDataForInvestReport = ConfigValue
    from Configuration
    where ConfigCode = 'ReportDurationForInv'

    select @DataElementDate = ConfigValue
    from Configuration
    where ConfigCode = 'DateElementDuration'

    begin tran

    -- Delete the Reports which are less than 7 days for DB and 1 for NonDB Users

    delete Report
    from Report r, ReportHeader rh, profile f
    where r.requestID = rh.requestID
    and f.dbid = rh.dbid
    and f.refid = rh.refid
    and (DATEDIFF(dd,rh.saveddate,getdate()) >=
    ((case when rh.reportkind = 'IN' then @ReportDataForInvestReport
    else(case when IshistAccessible = 1 then @ReportDateForDBUsers
    else @ReportDateForNonDBUsers
    end)
    end)))

    if @@error <> 0
    begin
    rollback tran
    return
    end

    -- Delete the data elements for DB Disabled User
    delete DataElements
    from ReportHeader r, DataElements de,profile p
    where r.requestID = de.requestID
    and p.dbid = r.dbid
    and p.refid = r.refid
    and p.IshistAccessible = 0
    and DATEDIFF(dd,r.saveddate,getdate()) >= (case when r.reportkind = 'IN' then @ReportDataForInvestReport
    else @ReportDateForNonDBUsers
    end)



    -- Vj - Should delete from DMI_dataelements only for non DB enabled users. For database enabled
    -- users data from DMI_dataelements needs to be deleted after 13 months
    delete DMI_dataelements
    from ReportHeader rh, profile f, DMI_dataelements dm
    where DATEDIFF(dd,rh.saveddate,getdate()) >= @ReportDateForNonDBUsers
    and f.dbid = rh.dbid
    and f.refid = rh.refid
    and dm.requestid = rh.requestid
    and f.IshistAccessible = 0

    if @@error <> 0
    begin
    rollback tran
    return
    end

    -- Delete the ReportHeader which are 1/7 days older and there is no entry in the data elements table.

    delete ReportHeader
    from ReportHeader rh, profile f
    where f.dbid = rh.dbid
    and f.refid = rh.refid
    and DATEDIFF(dd,rh.saveddate,getdate()) >= (((case when rh.reportkind = 'IN' then @ReportDataForInvestReport
    else(case when IshistAccessible = 1 then @ReportDateForDBUsers
    else @ReportDateForNonDBUsers
    end)
    end)))
    and ( not exists ( select 1 from DataElements de where de.requestid = rh.requestid )
    and not exists ( select 1 from DMI_DataElements de where de.requestid = rh.requestid)
    )

    if @@error <> 0
    begin
    rollback tran
    return
    end

    -- Update the ReportHeader to indicate that the reports have been deleted for rows older than 7 days.
    update ReportHeader
    set reportexists = 0
    from ReportHeader rh, profile p
    where rh.dbid = p.dbid
    and rh.refid = p.refid
    and p.IshistAccessible = 1
    and DATEDIFF(dd,rh.saveddate,getdate()) >=(case when rh.reportkind = 'IN' then @ReportDataForInvestReport
    else @ReportDateForDBUsers
    end)
    and reportexists = 1

    if @@error <> 0
    begin
    rollback tran
    return
    end

    commit tran
    begin tran
    -- Delete the data elements for DB enabled User/all dataelements which are > than DataElementDate
    delete DataElements
    from ReportHeader r, DataElements de
    where r.requestID = de.requestID
    and DATEDIFF(mm,r.saveddate,getdate()) >= @DataElementDate

    if @@error <> 0
    begin
    rollback tran
    return
    end

    -- Delete the DMI DataElements which are older than 13 months
    delete DMI_DataElements
    from ReportHeader r, DMI_DataElements de
    where r.requestID = de.requestID
    and DATEDIFF(mm,r.saveddate,getdate()) >= @DataElementDate

    if @@error <> 0
    begin
    rollback tran
    return
    end

    -- Delete the ReportHeader which are older than 13 months.
    delete ReportHeader
    where DATEDIFF(mm,saveddate,getdate()) >= @DataElementDate

    if @@error <> 0
    begin
    rollback tran
    return
    end

    commit tran
    begin tran

    --vj need to delete records from DMI_dataelements for those orders where dataexists =0
    --in dmi_dataelements table and reportexists is 0
    delete DMI_DataElements
    from ReportHeader r, DMI_DataElements de
    where r.requestID = de.requestID
    and r.reportexists = 0 and de.dataexists = 0

    if @@error <> 0
    begin
    rollback tran
    return
    end

    --vj Delete all entires in reportheader for wich there are no dataelements and also
    -- reportexists flag is 0

    delete ReportHeader
    from ReportHeader rh
    where not exists ( select 1 from DataElements de where de.requestid = rh.requestid )
    and not exists ( select 1 from DMI_DataElements de where de.requestid = rh.requestid)
    and rh.reportexists = 0

    if @@error <> 0
    begin
    rollback tran
    return
    end

    commit tran
    begin tran

    -- vj delete entries in dm_usr_entry_flds_val when the report has been deleted from the report header table
    delete dm_usr_entry_flds_val from dm_usr_entry_flds_val du
    where not exists (select 1 from reportheader rh where rh.requestid=du.requestid)

    if @@error <> 0
    begin
    rollback tran
    return
    end

    commit tran
    end



    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO


    Thanks
    Abilash

    Abilash. P
  7. abi New Member

    And one more doubt i have with respect to the above SP.

    Since we have many inserts to the tables on a single day the database internally will re-arrange the index(If Clustered Index is created on the tables) each time any inserts happens on the table. Whether this will have performance impact?

    Kinldy let me know how should i overcome this.

    Thanks
    Abilash

    Abilash. P
  8. johnson_ef Member

  9. mmarovic Active Member

    quote:
    delete Report
    from Report r, ReportHeader rh, profile f
    where r.requestID = rh.requestID
    and f.dbid = rh.dbid
    and f.refid = rh.refid
    and (DATEDIFF(dd,rh.saveddate,getdate()) >=
    ((case when rh.reportkind = 'IN' then @ReportDataForInvestReport
    else(case when IshistAccessible = 1 then @ReportDateForDBUsers
    else @ReportDateForNonDBUsers
    end)
    end)))
    DateDiff usage prevents effective use of index on savedDate if exists. Rewrite the query to use DateAdd function instead. The same applies for other deletes using dateDiff function.
    quote:you can use the UPDATE STATISTICS to update the indexes which is fragmented.
    No, he can't.


    quote:And one more doubt i have with respect to the above SP.

    Since we have many inserts to the tables on a single day the database internally will re-arrange the index(If Clustered Index is created on the tables) each time any inserts happens on the table. Whether this will have performance impact?

    Kinldy let me know how should i overcome this.
    Yes index will be updated and if wrong index is choosed to be clustered that can lead to frequent page splits and fragmentation. The best choice of clustered index in your scenario is identity column.

  10. fijunkie New Member

    Is this the same Abi from that helped me with windows vista?? How weird would that be?

    Sheldon

Share This Page