Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    91

    Unanswered: Which one is faster?

    Hi everyone -

    Would it be faster to create a cursor and have it loop
    through the result set calling another stored procedure
    that actually performs the delete...

    OR

    would it be faster to create a temp table based on a query
    and have the delete statement perform a delete where in


    I am trying to get an idea prior to doing the task for myself,
    hoping someone with much more knowledge than me has
    accomplished this before...

    thanks
    tony

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I suspect that the answer to that is "it depends"...

    How many rows are you likely going to delete?
    Do you have enough log space to perform the delete as single transaction?

    Michael

  3. #3
    Join Date
    Oct 2004
    Posts
    91

    ok

    The answer to your question is...

    It varies....

    but for the testing enviroment, it will be about 320,000 records...

    perhaps i should place a commit between each
    delete statement??

    thanks for the reply


    take care
    tony

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Why do you need a cursor or a temp table? Just delete based on a query.
    e.g.
    delete salesdetail
    from salesdetail, titles
    where salesdetail.title_id = titles.title_id
    and type = "business"

  5. #5
    Join Date
    Dec 2005
    Posts
    39
    if your intention is to delete specific data sets is it possible for you to select data which has to be retained into a new temp table, truncate the old one and copy back again. just a thought as this could be faster operation than deleting each record ( and possibly committing after every record as well ).

Posting Permissions

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