May 11, 2005 at 12:11 pm
Whenever I see a tutorial, I always see temporary tables operation used in this order:
My questions are: Why are the indexes/clustered index created after the data is inserted into the temporary table instead of before? Is it that is performance is a lot better that way? When I create a clustered index after the data is insert, doesn't sql server have to resort the data physically in that order, so why would that be more efficient that having it inserted into the correct order at the get go? Thanks a lot in advance.
May 12, 2005 at 12:49 am
It depends of the numer of data to be inserted in the table. If you create the clustered index first, the server has to find first where to insert the data, and then fysically insert the data at the right place. This can create fragmentation of the data and index. If there is no index, the data just has to be appended, and there will be no ( or less) fragmentation. The creation of the clustered index afterwards will defragment the table, and will make the use of the index more performant.
In most cases, creating the index ( with fillfactor = 100 ) after the inserts will incease performance of the script.
Bert
May 12, 2005 at 1:42 am
Definitely worth using temp tables. We're seen query times (using joins to very large tables) dramatically improve - from hours to minutes. SQL server just seems to choose the wrong execution plan (even with stats up to date) sometimes. Dumping only the rows you want into a temporary table and then using that temp table rather than the orig table helps SQL to find a better execution plan. Also, the temp table could have different indexes from the orig table so that it more suits that particular query.
May 12, 2005 at 1:43 am
However if there is only a single INSERT, inserting multiple rows using a select statement, I do not think that this would be a problem. The rows would need to be sorted (slight performance hit, but probably less than reorganizing the rows if creating index afterwards), but after that they could be merged into the index in one operation that would not cause any page splits.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply