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

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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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.


This was first published in July 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top