• To clarify what Sergiy just said... all those functions that do "lookups" are just an insideous way of doing correlated subqueries which are RBAR on sterioids. RBAR can appear to work fairly well if it all fits in memory cuz memory is fast. If it doesn't, you get the symptoms you just mentioned... everything was fine and then, one day, BOOM! Same goes when something works well on dev and then blows up in prod... just like Sergiy said, chances are you have less usable memory available on the prod machine because you have more users or processes which means it'll "tip" well before the dev box does.

    Also as Sergiy stated... the best thing to do would be to resolve the lookups in the form of a join. If you're real careful, you can do that in a view and then join to the view.

    --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)