Re: Data Warehouse - Upload Time

If 5,000 rows take 15 minutes than 2,000,000 rows would appear to need about 4 days, not 2 hours.

It sounds from your notes that each of the 5,000 rows is handled separately, and a user-defined function is called for each row. This is probably where most of your time goes.

Investigate ways to avoid using a user-defined function at this point Check that the function is working very efficiently if you have to have it

There is no correct answer to 'is 2 hours normal for 1M rows'. Depending on the hardware and complexity of processing the rate is anywhere between pathetic and miraculous. (My best to date has been 40M rows per hour maintaining indexes on the fly but that was a cunning design and a VERY aggressive bit of kit).

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Nandakumar wrote in message <7rj40i$nve$1_at_nnrp1.deja.com>...
>Hi
>
>Thanks for the info.
>
>I have a small Data Warehouse, where in the Upload to the Temporary
>table takes place in few seconds for about 5,000 rows. But the
>dissemination of the records from the Temporary table to 5 other fact
>tables take about 15 minutes. Dynamic SQL (used as the fact table name
>is decided in run-time and a column's value is modified by a
>user-defined function) has been extensively used in the DISSENINATION
>process. I am afraid if it could take longer like 2 hours when it comes
>to 1 or 2 million records. Can anyone who has used Dynamic SQL in Data
>Warehouse, suggest if this is a right idea to continue with DYN SQL, or
>they should be changed to static SQLs. Moreover, i would like to know if
>2 Hours time for COMPLETE UPLOAD ( for EXTERNAL DATA to get to the FACT
>TABLEs thru' TEMPORARY TABLE) of 1 Million records is NORMAL.
>
>Would appreciate any suggestion in this regard!
>Thanks
>Nandakumar
>
Received on Mon Sep 13 1999 - 10:33:06 CDT