June 13, 2010 at 12:48 am
Hi,
Am updating the Staging table with the help of 15 main tables.
Each main table will have around 900,000 records...But I will be selecting only present week records from those 15 main tables which would be around 20k records from the 15 main tables.....
Should i create new 15 main staging tables... based on the 15 main tables structure... and insert each week 20 k records into the staging tables and truncate them once the job is finished.....or is it ok to do a select on the 15 main tables... I am executing around 5 stored procedure inside a job with the help of 15 main tables...
Job takes around 10 to 15 minutes.... Please someone help me with this issue... is it ok to do a select on the main tables..
June 13, 2010 at 4:36 pm
Some clarification is needed - couldn't figure it out what is the question so far.
Is this part of an ETL process?
Are "main tables" the ones you are sourcing data during ETL?
Do these "main tables" have all of them the same structure?
Is this a weekly process as it appears to be?
Is data being moved from staging to core fact/dim tables as part of the same process?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 13, 2010 at 4:48 pm
Yes this is a part of ETL Process
I will be updating just one staging table. with the help of 15 main tables..
and this is a daily process and i need to run this ETL daily....
So my Question is this ok to select from the main 15 tables... or better to create 15 staging tables based on the main tables structure... and insert the daily required rows from 15 main tables into the 15 staging tables... and use the 15 staging tables in the update process...
June 13, 2010 at 4:55 pm
Lucky9 (6/13/2010)
So my Question is this ok to select from the main 15 tables... or better to create 15 staging tables based on the main tables structure... and insert the daily required rows from 15 main tables into the 15 staging tables... and use the 15 staging tables in the update process...
Whatever has the less impact on the source system. What you don't want to do in an ETL process is to negatively impact source system performance more than extrictely needed.
In general it is easy to design and easy to maintain a staging structure that closely matches the structure of the source system - after all, what you want to do is to move data from source system to staging area as fast as possible.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply