FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

How can I enhance the productivity of my fact table load?

  • Joe Oates
  • DM Review Online, November 5, 2007

Question: How can I enhance the productivity (performance) of my fact table load? Currently, I am loading 1 million records in the fact table, which takes 6 hours 20 min.

Joe Oates Answer: The time required to load a million records seems excessive. For example, on a dual processor, dual core server with 32 GB of memory, we are extracting, transforming and loading (ETLing) more than 8 million fact records in an hour; this includes doing surrogate key transformation for 12 dimensions.

Without knowing the details of your particular implementation, I can only suggest a couple of things to check.

  • Check to make sure that the ETL processes are doing set operations rather than cursor operations.
  • Check to make sure you have enough real memory. ETL requires a lot of real memory. If you have a server with only two GB of real memory, it will be excruciatingly slow.
  • Make sure that none of the ETL jobs are waiting on another server, such as a replica server to be ready for the ETL task.


Joe Oates is an internationally known speaker, author and consultant on data warehousing. Oates has more than 30 years of experience in the successful management and technical development of business, real-time and data warehouse applications for industry and government clients. He has designed or helped design and implement more than 30 successful data warehouse projects.

For more information on related topics, visit the following channels:



Industry Vendors