dhanasekar.palani (1/28/2009)
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 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.
If the staging table does not have to exist prior to the insert (for example, you have to insert some other rows first) try using a Select Into... instead of an Insert Into. This will create the table when you run the query and you can create any required indexes, etc, afterwards.
I had a similar situation a couple of years ago and making this changed reduced the processing time from over nine hours to about fifteen minutes. Your mileage may vary. 😀
Don