I’m creating a table from another table. The source table has about 400 million records and the final table has about 110 million records. I’m doing a lot of calculation from the source table then inserting them into a table using select into statement. The query is taking about 2 hours 40 minutes to complete. I was expecting it to complete in one hour. I looked at the execution plan and found out insert statement is taking 77% of the time. The estimate number of rows is 400 million records and the actual number of rows is 110 million. Will the difference between estimated and actual number of rows slow down performance? How can I bring down the estimated number of rows? Also, will I get a better performance if I first create the table and then do insert into rather than select into the table? Is there any other ways I can improve the performance?
Beyond @ThomasRushton suggestion to update your statistics, I couldn't possibly make any suggestions without at least seeing the execution plan and query. But, 70% of the time spent on writes in a query of this sort actually makes plenty of sense when you think about it. The most work is going to be done writing the data to disk, splitting & rearrange pages as necessary, etc. All the work of maintaining data on disk is almost always more expensive than reading from the disk.