The first step is to determine where the majority of the time is being chewed up. Is your job waiting on IO, CPU, memory, network traffic and/or a combination of the above? Is your job contending with other jobs vying for those same resources? Put a performance monitor and/or sniffer on your job. Look for ways to improve performance without making programming/ETL changes. Simple scheduling changes and computer resource allocations can have a big impact. Next, look to your target RDBMS for vendor supplied utilities that support bulk loading. Many of them offer a means to sort the data into primary key sequence, load the data segment, then the index segment. Typically, data warehouses are appending data, so make sure you set the RDBMS parameters to expect inserts only. Finally, turn to your ETL product and your transformation/load logic. Most ETL products have a rich systems integration partner network and/or user group forums. Tap into these groups to find the intuitive helps/hints for making the product perform at its fullest.
Best wishes on your quest to improve your throughput.
Dig Deeper on Oracle database performance problems and tuning
Related Q&A from Mike Lampa
When trying to design a data warehouse, we often try to model the database on the operational data model. Are there any guidelines in trying to ... Continue Reading
What is a surrogate key in a table? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.