Problem solve Get help with specific problems with your technologies, process and projects.

What factors should we consider to improve the performance of loading?

One of the tables in our data warehouse has around one million records to be loaded every week. It takes over ten hours to load this table. What factors should we consider to improve the performance of loading?

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.