Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Delete Transaction is very in Sql Server 2000 compare to Sql Server 2005

    I have a procedure which contains temporary tables,joins,join with temporary ,Select into temp tables etc.

    When i am executing this procedure in Sql server 2005 it is taking 5 minutes .the same procudre run in in Sql server and it is taking 4 hrs .

    Please let me know why this huge difference?

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Blocking ?
    Same H/W ?
    updated stats ?
    Defragged Indexes ?
    SP4 ? (maybe a Maxdop issue?) will see self blocking which are i/o waits.

    Need to provide more details, like asking why do some people die of the flu and some dont.

  3. #3
    Join Date
    Aug 2009
    Posts
    4

    executing this procedure taking 3 hrs in Sql server 2000 and only 5 minutes in 2005

    executing this procedure taking 4 hrs in Sql server 2000 and only 5 minutes in 2005 in Same machine configuration .

    If exists (SELECT name FROM sysobjects WHERE
    name = 'Pr_DA_DelServiceTransactions' AND type = 'P')
    DROP PROCEDURE [dbo].[Pr_DA_DelServiceTransactions]
    GO

    CREATE PROC Pr_DA_DelServiceTransactions
    @in_DealerId Int
    , @in_ArchiveYear Int
    , @in_RetainNoYrs int
    WITH ENCRYPTION
    AS
    BEGIN
    Declare @v_YearToRemv int
    set @v_YearToRemv = isnull(@in_ArchiveYear,0) - isnull(@in_RetainNoYrs,0)

    -- job cards which have invoices created in the next financial year.
    Select distinct JC.DEALER_ID, JC.BRANCH_ID,JC.JOB_CARD_ID,JC.JOB_CARD_DATE,JC.ST ATUS
    into #DMS_JOB_CARD_TEMP
    from dms_job_card JC
    inner join dms_job_card_invoice JCInv
    on JCInv.dealer_id = JC.dealer_id
    and JCInv.branch_id = JC.Branch_id
    and JCInv.Job_card_id = JC.Job_card_id
    WHERE dbo.fn_get_financial_year(JCInv.Invoice_date) > @v_YearToRemv
    and dbo.fn_get_financial_year(JC.JOB_CARD_DATE)<= @v_YearToRemv

    -- Job cards which have transit claim, warranty claim, delivery document or spare grn created in the next
    -- financial year.
    select distinct JC.DEALER_ID, JC.BRANCH_ID,JC.JOB_CARD_ID,JC.JOB_CARD_DATE,JC.ST ATUS
    into #DMS_JOB_CARD_CLM_TEMP
    from dms_job_card jc
    left outer join DMS_DLR_JCARD_WAR_CLM jcwarclm
    on jcwarclm.dealer_id = jc.dealer_id
    and jcwarclm.branch_id = jc.branch_id
    and jcwarclm.job_card_id = jc.job_card_id
    left join dms_dlr_war_claim warclm
    on warclm.dealer_id = jcwarclm.dealer_id
    and warclm.branch_id = jcwarclm.branch_id
    and warclm.dlr_war_clm_id = jcwarclm.dlr_war_clm_id
    left outer join dms_delivery_doc_det deldet
    on deldet.dealer_id = jcwarclm.dealer_id
    and deldet.claim_branch_id = jcwarclm.branch_id
    and deldet.claim_det_id = jcwarclm.jcard_war_clm_id
    and deldet.claim_type = 2
    left outer join dms_delivery_doc deldoc
    on deldoc.dealer_id = deldet.dealer_id
    and deldoc.branch_id = deldet.branch_id
    and deldoc.delivery_doc_id = deldet.delivery_doc_id
    LEFT OUTER JOIN DMS_SPARE_GRN SprGRN
    on SprGRN.dealer_id = deldoc.dealer_id
    and SprGRN.branch_id = deldoc.branch_id
    and SprGRN.delivery_doc_id = deldoc.delivery_doc_id
    left outer join DMS_DLR_TRAN_CLM_DET tranclm
    on tranclm.dealer_id = jc.dealer_id
    and tranclm.branch_id = jc.branch_id
    and tranclm.job_card_id = jc.job_card_id
    left outer join dms_dlr_tran_claim trn
    on trn.dealer_id = tranclm.dealer_id
    and trn.branch_id = tranclm.branch_id
    and trn.DLR_TRAN_CLM_ID = tranclm.DLR_TRAN_CLM_ID
    where dbo.fn_get_financial_year(jc.job_card_date) <= @v_YearToRemv
    and (jcwarclm.job_card_id in (case when (SprGRN.dealer_id is not null and dbo.fn_get_financial_year(SprGRN.GRN_DATE) > @v_YearToRemv) then (jcwarclm.job_card_id)
    when (deldoc.DEALER_ID IS NOT NULL AND deldet.dealer_id is not null and dbo.fn_get_financial_year(deldoc.delivery_doc_date ) > @v_YearToRemv) then (jcwarclm.job_card_id)
    when (jcwarclm.dealer_id is not null and dbo.fn_get_financial_year(warclm.claim_DATE) > @v_YearToRemv) then (jcwarclm.job_card_id)
    else 0
    end)
    OR tranclm.job_card_id in (case when (tranclm.dealer_id is not null and dbo.fn_get_financial_year(trn.claim_DATE) > @v_YearToRemv) then (jc.job_card_id)
    else 0
    end))


    -- job cards which have outwork memo created in the next financial year
    Select distinct JC.DEALER_ID, JC.BRANCH_ID,JC.JOB_CARD_ID,JC.JOB_CARD_DATE,JC.ST ATUS
    into #DMS_JOB_CARD_OUTWORK_TEMP
    from dms_job_card JC
    left join dms_job_card_labour JCLab
    on JC.dealer_id = JCLab.dealer_id
    and JC.branch_id = JCLab.branch_id
    and JC.job_Card_id = JCLab.job_Card_id
    left join dms_outwork_memo_det OutMemoDet
    on JCLab.dealer_id = OutMemoDet.dealer_id
    and JCLab.branch_id = OutMemoDet.branch_id
    and JCLab.JCARD_LAB_ID = OutMemoDet.JCARD_LAB_ID
    left join dms_outwork_memo OutMemo
    on OutMemo.dealer_id = OutMemoDet.dealer_id
    and OutMemo.Branch_id = OutMemoDet.Branch_id
    and OutMemo.OUTWORK_MEMO_ID = OutMemoDet.OUTWORK_MEMO_ID
    left join DMS_OUTWORK_PMT OutPay
    on OutMemoDet.dealer_id = OutPay.dealer_id
    and OutMemoDet.branch_id = OutPay.branch_id
    and OutMemoDet.OUTWORK_PMT_ID = OutPay.OUTWORK_PMT_ID
    where dbo.fn_get_financial_year(jc.job_card_date) <= @v_YearToRemv
    and jc.job_Card_id in (case when (OutPay.dealer_id is not null and dbo.fn_get_financial_year(OutPay.pmt_date) > @v_YearToRemv) then jclab.job_card_id
    when (OutMemoDet.dealer_id is not null and dbo.fn_get_financial_year(OutMemo.memo_date) > @v_YearToRemv) then jclab.job_card_id
    else 0
    end)

    -- Select only those job cards which can be deleted
    Select distinct JC.DEALER_ID, JC.BRANCH_ID,JC.JOB_CARD_ID
    into #DMS_ARCHI_JOB_CARD_TEMP
    from DMS_JOB_CARD JC
    left outer join #DMS_JOB_CARD_TEMP JCTEMP
    on JCTEMP.dealer_id = JC.dealer_id
    and JCTEMP.branch_id = JC.branch_id
    and JCTEMP.job_card_id = JC.job_card_id
    left outer join #DMS_JOB_CARD_CLM_TEMP JCCLMTEMP
    on JC.dealer_id = JCCLMTEMP.dealer_id
    and JC.branch_id = JCCLMTEMP.branch_id
    and JC.job_card_id = JCCLMTEMP.job_card_id
    left outer join #DMS_JOB_CARD_OUTWORK_TEMP JCOUTTEMP
    on JC.dealer_id = JCOUTTEMP.dealer_id
    and JC.branch_id = JCOUTTEMP.branch_id
    and JC.job_card_id = JCOUTTEMP.job_card_id
    where jc.dealer_id = @in_DealerId
    and dbo.fn_get_financial_year(jc.job_card_date) <= @v_YearToRemv
    and JCTEMP.job_card_id is null
    and JCCLMTEMP.job_card_id is null
    and JCOUTTEMP.job_card_id is null

    /*
    ----------------------------------------------------------------------------------------------------
    Delete scripts start from here. The condition used to insert is used to delete also
    ----------------------------------------------------------------------------------------------------
    */


    --delete from DMS_OUTWORK_MEMO_DET
    delete from DMS_OUTWORK_MEMO_DET
    from DMS_OUTWORK_MEMO_DET OUTMEMODET
    INNER JOIN DMS_JOB_CARD_LABOUR JCLAB
    ON JCLAB.DEALER_ID = OUTMEMODET.DEALER_ID
    AND JCLAB.BRANCH_ID = OUTMEMODET.BRANCH_ID
    AND JCLAB.JCARD_LAB_ID = OUTMEMODET.JCARD_LAB_ID
    INNER JOIN #DMS_ARCHI_JOB_CARD_TEMP JCTEMP
    ON JCTEMP.DEALER_ID = JCLAB.DEALER_ID
    AND JCTEMP.BRANCH_ID = JCLAB.BRANCH_ID
    AND JCTEMP.JOB_CARD_ID = JCLAB.JOB_CARD_ID

    -- Delete from DMS_OUTWORK_PMT
    Delete from DMS_OUTWORK_PMT
    from DMS_OUTWORK_PMT OUTPMT
    LEFT OUTER JOIN DMS_OUTWORK_MEMO_DET OUTMEMODET
    ON OUTMEMODET.DEALER_ID = OUTPMT.DEALER_ID
    AND OUTMEMODET.BRANCH_ID = OUTPMT.BRANCH_ID
    AND OUTMEMODET.OUTWORK_PMT_ID = OUTPMT.OUTWORK_PMT_ID
    LEFT OUTER JOIN DMS_JOB_CARD_LABOUR JCLAB
    ON JCLAB.DEALER_ID = OUTMEMODET.DEALER_ID
    AND JCLAB.BRANCH_ID = OUTMEMODET.BRANCH_ID
    AND JCLAB.JCARD_LAB_ID = OUTMEMODET.JCARD_LAB_ID
    INNER JOIN #DMS_ARCHI_JOB_CARD_TEMP JCTEMP
    ON JCTEMP.DEALER_ID = JCLAB.DEALER_ID
    AND JCTEMP.BRANCH_ID = JCLAB.BRANCH_ID
    AND JCTEMP.JOB_CARD_ID = JCLAB.JOB_CARD_ID

    delete from DMS_OUTWORK_MEMO
    from DMS_OUTWORK_MEMO OUTMEMO
    Left Outer join DMS_OUTWORK_MEMO_DET OUTMEMODET
    on OUTMEMODET.dealer_id = OUTMEMO.dealer_id
    and OUTMEMODET.branch_id = OUTMEMO.branch_id
    and OUTMEMODET.outwork_memo_id = OUTMEMO.outwork_memo_id
    Left Outer join DMS_JOB_CARD_LABOUR JCLAB
    on JCLAB.dealer_id = OUTMEMODET.dealer_id
    and JCLAB.branch_id = OUTMEMODET.branch_id
    and Jclab.JCARD_LAB_ID = OUTMEMODET.JCARD_LAB_ID
    INNER JOIN #DMS_ARCHI_JOB_CARD_TEMP JCTEMP
    ON JCTEMP.DEALER_ID = JCLAB.DEALER_ID
    AND JCTEMP.BRANCH_ID = JCLAB.BRANCH_ID
    AND JCTEMP.JOB_CARD_ID = JCLAB.JOB_CARD_ID
    where (OUTMEMO.outwork_memo_id is not null and OUTMEMODET.outwork_memo_id is null and JCTEMP.dealer_id is null)

    -- Delete from DMS_JOB_CARD_INVOICE
    DELETE FROM DMS_JOB_CARD_INVOICE
    from DMS_JOB_CARD_INVOICE JCINV
    INNER JOIN #DMS_ARCHI_JOB_CARD_TEMP JCTEMP
    ON JCTEMP.DEALER_ID = JCINV.DEALER_ID
    AND JCTEMP.BRANCH_ID = JCINV.BRANCH_ID
    AND JCTEMP.JOB_CARD_ID = JCINV.JOB_CARD_ID


    -- Delete from DMS_JOB_CARD_STATUS
    DELETE FROM DMS_JOB_CARD_STATUS
    FROM DMS_JOB_CARD_STATUS JCSTAT
    INNER JOIN #DMS_ARCHI_JOB_CARD_TEMP JCTEMP
    ON JCTEMP.DEALER_ID = JCSTAT.DEALER_ID
    AND JCTEMP.BRANCH_ID = JCSTAT.BRANCH_ID
    AND JCTEMP.JOB_CARD_ID = JCSTAT.JOB_CARD_ID


    -- Delete from DMS_JOB_CARD_BEFORE_DEL
    DELETE FROM DMS_JOB_CARD_BEFORE_DEL
    FROM DMS_JOB_CARD_BEFORE_DEL JCBEFDEL
    INNER JOIN #DMS_ARCHI_JOB_CARD_TEMP JCTEMP
    ON JCTEMP.DEALER_ID = JCBEFDEL.DEALER_ID
    AND JCTEMP.BRANCH_ID = JCBEFDEL.BRANCH_ID
    AND JCTEMP.JOB_CARD_ID = JCBEFDEL.JOB_CARD_ID

    --
    DROP TABLE #DMS_JOB_CARD_TEMP
    DROP TABLE #DMS_JOB_CARD_CLM_TEMP
    DROP TABLE #DMS_JOB_CARD_OUTWORK_TEMP
    DROP TABLE #DMS_ARCHI_JOB_CARD_TEMP
    END
    GO

  4. #4
    Join Date
    Aug 2009
    Posts
    4
    Deleting Tables which have child tables taking more time in sql server 2000(more than 1 hr) ,However it takes only less than 1 minutes in Sql server 2005.Please help me to find out reason .

    --DMS_JOB_CARD_INV_LAB--245585 rows

    Delete from DMS_JOB_CARD_INV_LAB
    from DMS_JOB_CARD_INV_LAB JCLABINV
    Inner join DMS_JOB_CARD_INVOICE JCINV
    On JCINV.dealer_id = JCLABINV.dealer_id
    and JCINV.branch_id = JCLABINV.branch_id
    and JCINV.JCARD_INV_ID = JCLABINV.JCARD_INV_ID
    INNER JOIN #DMS_ARCHI_JOB_CARD_TEMP JCTEMP
    ON JCTEMP.DEALER_ID = JCINV.DEALER_ID
    AND JCTEMP.BRANCH_ID = JCINV.BRANCH_ID
    AND JCTEMP.JOB_CARD_ID = JCINV.JOB_CARD_ID

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Why not just upgrade?

  6. #6
    Join Date
    Aug 2009
    Posts
    4
    Thank you for your reply .Sql server 2000 is using by 350 clients.I am very thankful you if i got ant alternative solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •