question

Bhupendra99 avatar image
Bhupendra99 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
temporary-tableindex-performance
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would say "it depends". Why do you think that you need an index to begin with? Are you seeing slowness? What kind of index and what are the keys of the index?
1 Like 1 ·
NeerajTripathi avatar image
NeerajTripathi answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.