Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    104

    Unanswered: stmt inside proc taking very long time

    Hello All,

    Can anybody please help me .....

    My problem is ...

    I have a delete statement , which takes 1 min to delete records

    whereas if i put the same statement inside a procedure after certain more things....then this statement takes more than 90 min.

    SP code is something like
    ------------------------------------------------------------
    create proc A
    as
    create table tempdb..a(-----)
    insert into tempdb..a select * from b
    commit
    exec subproc1
    exec subproc2

    insert into some table
    commit

    delete tablea -- here it is taking time as i have print statement after every step

    commit
    --------------------------------------------------- create subproc1
    as
    stmt
    stmt
    stmt
    commit
    return 0

    --------------------------------------------------

    create subproc 2
    as
    stmt
    stmt
    stmt
    commit
    return 0
    -------------------------------------------
    sp_who shows runnable process for delete
    sp_lock shows exclusive lock in tablea
    i am using syabse 12.5

    any clue will be great help.
    thanx in advance pooja

  2. #2
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    If you delete all the records of a tempdb..a(-----), you can try to do:

    "drop table tempdb..a(-----)"

    or

    "truncate table tempdb..a(-----)"

    This commands run faster than "delete table ...", because don't need to log the transactions.
    And, if you replace:

    "insert into tempdb..a select * from b"

    by:

    -- without the create table "tempdb..a"
    "select * into tempdb..a from b"

    You will win more time.
    Bye bye

    Sebastian

  3. #3
    Join Date
    Dec 2002
    Posts
    104
    Originally posted by svelasco
    If you delete all the records of a tempdb..a(-----), you can try to do:

    "drop table tempdb..a(-----)"

    or

    "truncate table tempdb..a(-----)"

    This commands run faster than "delete table ...", because don't need to log the transactions.
    And, if you replace:

    "insert into tempdb..a select * from b"

    by:

    -- without the create table "tempdb..a"
    "select * into tempdb..a from b"

    You will win more time.
    Bye bye

    Sebastian
    Hello Sebastian,

    thanx for ur input,

    but my problem is only delete stmt inside the proc is taking exceptionally long where as same delete stmt if executed indepently then it just takes 1 min.

    secondly, i cannot use DDL in tran

    --Pooja

  4. #4
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    I don't know how is the logic of the store, but the differences of time is because when you make the delete into a transaction, the databases write the log, but in 12.5 if you executed independently, the databases don't use the log of tempdb (I'm not sure, I use 12.0).
    If you can put the delete outside the transaction may be you will see a reduction of the time, and if you replace the delete with a truncate will be better (or drop table).
    You have to delete all the rows or a set ?
    You can setup the databases to use DDL in tran, but I think is not a good idea.

    Sebastian

  5. #5
    Join Date
    Dec 2002
    Posts
    104
    Originally posted by svelasco
    I don't know how is the logic of the store, but the differences of time is because when you make the delete into a transaction, the databases write the log, but in 12.5 if you executed independently, the databases don't use the log of tempdb (I'm not sure, I use 12.0).
    If you can put the delete outside the transaction may be you will see a reduction of the time, and if you replace the delete with a truncate will be better (or drop table).
    You have to delete all the rows or a set ?
    You can setup the databases to use DDL in tran, but I think is not a good idea.

    Sebastian
    same piece of code was working earlier inside the procedure as well.But from last 2 days it is not working.
    Do u think multiple BCP in into the table can be a problem.

    As for testing what i am doing is, BCping the data(say in table A,permanent table). then running the procedure and in the end of procedure it will delete(table A generally all the rows will be deleted but i am providing conditions here)
    I have no "BEGIN TRAN" inside the proc...just the multiple commits.

    Pooja

  6. #6
    Join Date
    Feb 2004
    Posts
    28
    Pooja,

    Try running update statistics(an then sp_recompile) on the all the tables that are being used in the proc.

    Or recreate the proc after running update statistics.

    j

    Originally posted by pooja
    same piece of code was working earlier inside the procedure as well.But from last 2 days it is not working.
    Do u think multiple BCP in into the table can be a problem.

    As for testing what i am doing is, BCping the data(say in table A,permanent table). then running the procedure and in the end of procedure it will delete(table A generally all the rows will be deleted but i am providing conditions here)
    I have no "BEGIN TRAN" inside the proc...just the multiple commits.

    Pooja

Posting Permissions

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