asked
Performance of Index on Temp tables
Consider the below scenario and tell me what will be the best solution Scenario : I am using a Temp table with Index in Sp to stored data Let's say I will insert 4000 entries in Temp Table each time I will execute the SP Solution 1) Will it be good to create Index while temp table creation Solution 2) OR Will it be good to create Index once I am done inserting 4000 rows in Temp table
Creating index while creating table is faster but will lead to fragmented once the data populated. Since, the primary objective of creating index on temp table here is to improve performance so creating index after data population will always be better. Considering checking the execution plan for the query if the index properly utilized or not. You can try to tweak the code so that it uses the index properly.
By creating the index first and then loading data, you will see automatic statistics updates which can lead to recompiles of your procedures. Probably you'll be better off creating the index after loading the data, assuming the data load doesn't need to reference the index.
17 People are following this question.
