Avoiding RBAR in updates

[RRinTetons]

I have two table variables, one called @ChangedID contains a selection of primary keys, and the other called @EmployeeMap that tells me how to get from one database to another to find the records in the second database that need to be changed.

For each row in @ChangedID I need to find matching rows in @EmployeeMap. @ChangedID is on the order of 10 rows, @EmployeeID is on the order of 100 rows.

If there are matching rows then I need to do some complex processing in the second db. Lots of conditional branching to determine what's required and how to accomplish it. I can do it nicely with a loop that works through the rows of @ChangedID, and given the number of rows involved it wouldn't be heinous, but I'd like to try to do it better. Does that info suggest any better approaches?

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[philhege]

It depends on the complexity of your conditional processes. If they can be expressed in a set-based environment, then there might be performance gains to be had.

Start with converting your list variables to table variables and see where that can take you.

-----------
With business clients like mine, you'd be better off herding cats.

 

[RRinTetons]

They are tables, or at least table vars. I have data types defined for them so I can pass them to sp's and functions, too. The processing is moderately complex. For each member of @ChangedID I find out from @EmployeeMap what kind of an employee this is and what needs to be done. Based on that I connect to one or more other databases or linked servers and generate rows in one or more tables in each. I really don't see a way to convert it to set-based logic, but I'll keep looking at it.

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[philhege]

If you're reaching out across linked servers, it's probably more of a process-based approach. I'd be curious to see a simple schema and a cut-down set of tasks.

-----------
With business clients like mine, you'd be better off herding cats.