Update performance issue

algfr · Post by **algfr** » Fri Aug 07, 2009 3:44 am

Hello all,

When using the insert/update method I get ridiculous performance on volumes over 5000 records. I have a dimension over 150,000 records. Performing an update of 10,000 records using a SCD stage took 2 hours...

I have to use it because I have a SCD stage which cannot be used with a load/truncate method.

I checked if the join columns were indexed, they are...

Any idea of where to optimize ?

Thanks people

miwinter · Post by **miwinter** » Fri Aug 07, 2009 3:57 am

Join columns were indexed?

Does this mean the columns in the 'where' of your update are indexed?

algfr · Post by **algfr** » Fri Aug 07, 2009 4:33 am

miwinter wrote:
Join columns were indexed?
Does this mean the columns in the 'where' of your update are indexed?

Yes

miwinter · Post by **miwinter** » Fri Aug 07, 2009 4:42 am

What are the target database and stage being used to insert/update? I haven't got access to v8 as yet, so apologies if the SCD stage is the stage being used to do the write.

algfr · Post by **algfr** » Fri Aug 07, 2009 5:03 am

miwinter wrote:What are the target database and stage being used to insert/update? I haven't got access to v8 as yet, so apologies if the SCD stage is the stage being used to do the write.

It is Oracle 10g with Oracl Enterprise.

miwinter · Post by **miwinter** » Fri Aug 07, 2009 5:08 am

Can you elaborate on your job design?

priyadarshikunal · Post by **priyadarshikunal** » Fri Aug 07, 2009 5:14 am

get a trace from oracle, i suspect there are some other queries blocking the update query.

I performed few tests on oracle 10g with Oracle Enterprise Stage earlier with the similar number of records than you, below are the results in short:

2 oracle stages one with insert/load and one with update

if there are records in load with same key as update. It took 12 minutes where one was blocking another.
with few of the current load records updated

when I change the upsert query to identify the previous records and force it to update only previous records, it took only 30 seconds also populated correct data.

see if its the same case with you.

algfr · Post by **algfr** » Fri Aug 07, 2009 5:17 am

priyadarshikunal wrote:get a trace from oracle, i suspect there are some other queries blocking the update query.

I performed few tests on oracle 10g with Oracle Enterprise Stage earlier with the similar number of records than you, below are the results in short:

2 oracle stages one with insert/load and one with update

if there are records in load with same key as update. It took 12 minutes where one was blocking another.
with few of the current load records updated

when I change the upsert query to identify the previous records and force it to update only previous records, it took only 30 seconds also populated correct data.

see if its the same case with you.

Thanks.

Do you mean that the insert/update option should never be used ?

algfr · Post by **algfr** » Fri Aug 07, 2009 5:20 am

With the SCD stage, I'm supposed to use the insert/update mode normally

priyadarshikunal · Post by **priyadarshikunal** » Fri Aug 07, 2009 5:25 am

not in same job with 2 different Oracle enterprise stage. If you are bound to use that then make sure the where condition contains enough constraints to leave the current records.

upsert mode is OK to be used with SCD.

Before I can suggest any thing you need to answer the question asked by miwinter and explain the job design.

Also get trace file/ report from DBA to see if there was a lock.

algfr · Post by **algfr** » Fri Aug 07, 2009 5:31 am

priyadarshikunal wrote:not in same job with 2 different Oracle enterprise stage. If you are bound to use that then make sure the where condition contains enough constraints to leave the current records.

upsert mode is OK to be used with SCD.

Before I can suggest any thing you need to answer the question asked by miwinter and explain the job design.

Also get trace file/ report from DBA to see if there was a lock.

This looks like this (it update a Product dimension with 150,000 records)

part 1
LKP1 LKP2 LKP3
| | |
ODBC Stage => Transformer => Lookup constraints==>Dispatch rejects ==>

part 2
Old Product dim
|
==> SCD Stage ==> New Product dim (update/insert)
|
Dummy copy stage

If I load the table for the fisrt time, I assume it uses insert statements. This runs ok.

If I load it for the second time and rows haven't all be loaded in the first place, the update statements take forever.

I use this design with all my dim tables. It doesn't happne when tables are small.

algfr · Post by **algfr** » Fri Aug 07, 2009 5:32 am

priyadarshikunal wrote:not in same job with 2 different Oracle enterprise stage. If you are bound to use that then make sure the where condition contains enough constraints to leave the current records.

upsert mode is OK to be used with SCD.

Before I can suggest any thing you need to answer the question asked by miwinter and explain the job design.

Also get trace file/ report from DBA to see if there was a lock.

When I check the SGA trace, I don't see any lock but I can see many inactive sessions.

priyadarshikunal · Post by **priyadarshikunal** » Fri Aug 07, 2009 6:21 am

This is a pretty simple job and should not take that much time

you should start with

Run update query for 10000 records from toad or any client and see how much time it takes. (take backup first)

algfr · Post by **algfr** » Fri Sep 04, 2009 6:20 am

Still slow...

I wonder if it would be nice to use a change apply stage to rebuild the whole data and then truncate/reload instead of inserting/updating. it seems updating is a huge pain.

What do you think ?

chulett · Post by **chulett** » Fri Sep 04, 2009 7:09 am

Updates can be expensive but it shouldn't be a 'huge pain'. Are the 'key' columns you are updating against indexed?