January 23, 2012 at 3:39 pm
Hi Friends,
Could someone clarify my question?
I am loading data from source to destination using ssis( I am using ssis, just to execute sql Execute task from where i am executing stored procs to load data).
Before doing the load, I am dropping all the primary keys and foreign keys of the related tables.
and after the load, i am recreating those foreign keys and primary keys.
Is this the correct approach that I am following?
I appreciate your help.
Dsh
January 23, 2012 at 3:50 pm
As always - it depends.
Lets say you have a table of 100 million rows and your ETL is inserting/updating a few thousand.
In that case dropping amd recreating constraints is almost certainly not the "right thing"
Or maybe you have the opposite extreme of say a table of 10000 rows , and your ETL deltes and re-inserts every row - now in this case dropping and re-creating constraints almost certainly makes the process faster, but does it help you with your error handling and re-runnability of the process?
Now in practice you are probably somewhere in the middle of those two extremes, so it may help, or it may not it depends on how the ETL is being done, and many factors such as how many constraints you need to drop and recreate.
Mike John
January 24, 2012 at 8:55 am
Hi Mike,
Thank you very much for you reply.
In my case I am inserting and updating about 4-5 table. I might insert update 5 thousand rows and might update about 10-15 thousand rows. I am dropping and recreating about 5-10 PKs and 10-15 FKs.
As of now my load is pretty fast, hardly takes about 5-10 min. As of now I feel ok. but in case if the data keep growing in the db, do I need to change my approach of dropping and creating pks and fks? what is the best way ?
Thanks alot
Dsh.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply