CODEWITHSUNDEEP
sql-serveroptimizationinsert
Martin
Martin

Reputation: 755

insert with order by is faster

I have the following query:

 INSERT INTO table(field, field1)
 SELECT value, value1  FROM table2 e 
 ORDER BY value

which takes less time than this one:

 INSERT INTO table(field, field1)
 SELECT value, value1  FROM table2 e

Does anyone know why?

The execution plan of the second one shows that sql does an "order by" operation anyway, but is less performant than mine

Upvotes: 5

Views: 4994

Answers (2)

Gulli Meel
Gulli Meel

Reputation: 891

Do you have any nonclustered index on the value column in the table table2? Do you have clustered index on table on value clause? I could see two possible reason for this.

1.There is some kind of nonclustered index on column value so that optimizer picks this index and avoids sorts (it could be a covering index as well, in this case it will be very fast). The reason why the query without any order by did not pick that index is because it is a simple query and no optimization happened and it did a clustered index or table scan and then sorted the data and it caused the performance degraded as compared to order by. This is the most likely reaosn.

  1. The other reason could be that while inserting the data it inserts the data as it get and then if the data is ordered and same clustered index on order by column. There are no page splits and things will be fine.But if there is no sort then values will be inserted randomly an dcould cause page splits which slightly degrades performance. However, OP has mentioned that optimizer has done a sort before inserting that means this scenario is not applicable here.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37364

Insert performance depends on how many indexes you have and on what columns. If there is a clustered index on table.field inserting unsorted values is quite expensive (values not sorted by field).

Upvotes: 2

Related Questions