Re: Loading 300 million records

Billy wrote:
> AlexK wrote:
>
> > I'm trying to improve the performance of a procedure that loads
> > approximately 300 million records into a table.
> <snipped>
>

...
> The basic and most fundemental rule in solving problems (be that bugs,
> performance or writing code or business processes) is to IDENTIFY THE
> PROBLEM. Only then do you have a hope to solve it.

well there isn't really anything I can add to Billy's comments. PQ and CTAS without index maintainance have the potential to signigicantly speed up your load... but as he said it all depends on your configuration and where the bottlenecks are.

... last night one of our load processes loaded about 20 million rows from about 90 source tables to about 40 destination tables in 1 hr 10 min -- probably about as fast as it could pump the data over the 100m link, process it with the server's single 2g processor, and push the data blocks over the PCI bus to the HBA. (Of course who knows... maybe there's still a few ways I could tune that and make it run faster... but it's fast enough for now and as i heard it aptly put a few days ago, "i prefer sleeping".) And row/record count can be very misleading too since of course your rows could be very small or very large...

Other questions to ask... is there anything else running concurrently on this box? What's read and write IO throughput are you getting? What is the max IO throughput you can get? (Try "cat /dev/your_disk > /dev/null" and run iostat or vmstat.) How many processors do you have?  Are they all being utilized? How many IO channels? Sounded like you're only loading one table... is it partitioned? Are the partitions spread over different IO channels? Are you applying data transformations that are using all the available CPU? It's just a matter of figuring out *WHAT* is taking so long. A 10046 might not hurt either, like Anurag suggested.

With the information you've given all we can do is suggest a few more questions to ask that will hopefully (but maybe not) lead you in the right direction. Received on Fri Jun 10 2005 - 08:56:12 CDT