Thanks a lot for the response Steve. I am sorry I took some time to post. Weekend Fever!! 🙂
sjimmo (8/13/2010)
I would definitely create my indexes/PK's after the data is loaded. That way the statistics will be up to date and provide a better query plan. From what you said, your own testing has verified this.
No, in the sample I had posted, index creation after insertion took more time but, I think there were other factors at play, as Paul has explained. Say, I create the index in the table definition. How will that affect statistics or query plan? If temporary tables are used, execution plan will be changed once the table has data, won't it be? (Ref: http://support.microsoft.com/kb/243586)
There is one issue which I have run into, and that is when the table is created and the indexes/statistics are created - If you need to delete and reload the data, then the queryplan can be way of. Also, I have run into an issue where deleting the data from a table and recompiling the stored procedures on that table, whether temp or permanent, the query plan has been way off.
By way off, do you mean the new plan yields less performance or that it is way different from the older plan?
- arjun
https://sqlroadie.com/