Hi,
I want to implement the following script through an ETL in Data Services
UPDATE CLIENT clt SET clt.contrat =(SELECT MAX(co.contrat_id)
FROM CONTRAT_CLIENT co WHERE co.idf= clt.idf_client );
I made it like shown in the attached file, but i’m not sure weither or not it’s the most performant way to implement an update especially when the tables are large
NouhaS (BOB member since 2013-07-29)
Update operations are quite expensive in the database. Are you sure that you want to update EVERY row in the table? Sometimes it is less expensive to do an an INSERT into a different table (or partition) than to do the UPDATE.
The Table Comparison is not necessary. You’re updating every row so don’t bother with it. Instead, use a Map_Operation to change all the Normal rows to Update.
If you want to tweak the performance a bit then in the QR_Mapping filter OUT the rows where the old and new values for contrat are the same. That way you don’t have to update those rows.
eganjp 
(BOB member since 2007-09-12)
I had your Dataflow graphic on my desktop for the past day so I’ve been thinking about it some more.
There are many ways to implement this. Some are easier to write while others are more efficient. I think it would be more efficient to create the result set from a SQL transform. In Oracle code this would look like this:
SELECT …,
(SELECT MAX(co.contrat_id)
FROM CONTRAT_CLIENT co WHERE co.idf= clt.idf_client ) contrat
FROM CLIENT clt
I’m teaching a session on performance tuning at the upcoming Business Objects User Conference. Can I use the graphic of your Dataflow in my presentation? I think it is a good example to explain how Data Services does things.
eganjp (BOB member since 2007-09-12)
Thank you for your reply!
Feel free to you use the graphic 
NouhaS (BOB member since 2013-07-29)
Thanks. Let me know what you come up with for a solution. Sometimes you have to be creative.
eganjp (BOB member since 2007-09-12)