Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
8022421
Starting Member
45 Posts |
 Posted - 2009-01-28 : 19:13:52
|
| I have a main table which has 55 million rows..I am populating a staging table from the main table with the conditions as from date to to date...I wrote this staging table pouplation in a stored procedure and I called the stored Procedure in the integration services..But it is taking almost 40 minutes to complete the execution of the prcoedure.. this main table is indexed. Can any one give some idea how to reduce the execution of the procedure.Is there any way that I can put in the integration services so that the insertion to staging table is much faster....Please advise. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 19:17:40
|
| Do you need all 55 million rows for Staging? What actually are you doing? |
 |
|
|
8022421
Starting Member
45 Posts |
Posted - 2009-01-28 : 19:29:23
|
| Actually this table gets populated with everyday transaction. After the population of this table they will be doing a report from this table..which was previously written in cobol.I am rewriting the cobol program to stored procedure and using integration services I will prodcuce files what ever they are producing through cobol. So, for this instead of using the Main table directly for all the calculations I can just take those transactions which is done today and populate that to a staging table and I will use this staging table which has got minimum number of rows for further calcualtion instead of a main table... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-29 : 11:53:23
|
| First time loading the stating table will take that long..but any other load you do after that should that is if you are only loading DELTAS (changes in the source database) and when adding new records that exist in source database but not in destination. but if you are doing load of all 55 million every day...Also please show us your stored procedure...it might not be the 55 million tables it might be your sproc |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-29 : 12:00:10
|
quote:
Originally posted by 8022421
Actually this table gets populated with everyday transaction. After the population of this table they will be doing a report from this table..which was previously written in cobol.I am rewriting the cobol program to stored procedure and using integration services I will prodcuce files what ever they are producing through cobol. So, for this instead of using the Main table directly for all the calculations I can just take those transactions which is done today and populate that to a staging table and I will use this staging table which has got minimum number of rows for further calcualtion instead of a main table...
you can just use audit column like dateadded for this. make sure you store the max value of dateadded to a log table each day after package run. next time you run package, only grab those records from main table having dateadded > the latest value from table. then process this records and bring them to destination (use fast load option if possible). after that, again put max of date value to log table for next run value. |
|
|
|
|
|
|
Populating the staging table..I