Post reply

Looping Statement

  • July 21, 2009 at 2:41 pm

    #208612

    Hi Guys

    Can you please kindly help me on how can I recode this line of script using a looping statement so that i can delete an item one at a time.

    DELETE CN FROM [Able703].dbo.ac_CatalogNodes CN, [Able703].dbo.ac_Products P, deleted D

    WHERE D.strProductID = P.SKU AND P.ProductID = CN.CatalogNodeID

    I'm looking forward for your help

    Thanks

  • July 21, 2009 at 2:53 pm

    #1027941

    Why would you want to change from a set-based solution to a cursor-based (row by row) solution?

    Your current method is more efficient.

  • July 21, 2009 at 2:55 pm

    #1027946

    Looking closer at your code, this is in a trigger isn't it.

  • July 21, 2009 at 4:22 pm

    #1027993

    Yup this code is in the trigger. I know this scripts is more efficient but i need to loop it to avoid the transaction context is in used by another session in a linked server 😀

  • July 21, 2009 at 5:29 pm

    #1028021

    ..+ hunter +.. (7/21/2009)

    Hi Guys

    Can you please kindly help me on how can I recode this line of script using a looping statement so that i can delete an item one at a time.

    DELETE CN FROM [Able703].dbo.ac_CatalogNodes CN, [Able703].dbo.ac_Products P, deleted D

    WHERE D.strProductID = P.SKU AND P.ProductID = CN.CatalogNodeID

    I'm looking forward for your help

    Thanks

    Yeah... change that DELETE to be the SELECT statement for a Cursor that will do the delete. Then, hook up extra phone lines so users can call you and tell you that the server is running slow. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 21, 2009 at 5:43 pm

    #1028028

    ..+ hunter +.. (7/21/2009)

    Yup this code is in the trigger. I know this scripts is more efficient but i need to loop it to avoid the transaction context is in used by another session in a linked server 😀

    If you are deleting over a linked server, then the deletion is ALREADY happening in a cursor mode whether or not you wanted it to. updtes over linked servers are essentially serialized out into a cursor (whether or not you actually see it)

    If you want to gain some speed, I would look at a way to "throw" the delete (using OPENQUERY) so that the delete command is executed on the remote end (i.e. where the data is).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • July 21, 2009 at 6:12 pm

    #1028038

    So what do you think is the best way to handle my issue? I know you have a lot of experience than mine? so i think i need your help badly with this one.Please share some light on this 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply