insert with order by is faster

Question

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

What do you mean by more optimized? and "why it works now?" does that mean something does not work? — mmmmmm, Jul 13 '12 at 16:07
In addition to Mark's important questions, How much faster is it? Is the difference significant enough to matter? — hatchet - done with SOverflow, Jul 13 '12 at 16:13
@CharlesBeattie not for me, I still don't know what "more optimized" means. — Aaron Bertrand, Jul 13 '12 at 16:33
Sorry guys, argentinian boy here trying to speak in english :) — Martin, Jul 13 '12 at 17:29
In SQL Server 2012 this `ORDER BY` [will likely be ignored unless you have an `IDENTITY` column on the table or are running at `SET ROWCOUNT` other than 0](http://stackoverflow.com/questions/11222043/table-valued-function-order-by-is-ignored-in-output/11231935#11231935) — Martin Smith, Dec 30 '12 at 17:07

score 2 · Answer 1 · answered Jul 13 '12 at 16:18

2

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).

answered Jul 13 '12 at 16:18

a1ex07

36,826
12
90
103

SQL Server can add a sort to the plan `DMLRequestSort` to take account of this though. – Martin Smith Jul 13 '12 at 16:23
@Martin Smith: I agree, it's very likely optimizer will take care of it. However, I don't think it's guaranteed; if there is no index on source column, it might decide that sorting itself is more expensive. Anyway, I'd prefer to give optimizer less work if I can express it with SQL. – a1ex07 Jul 13 '12 at 17:02

score 0 · Accepted Answer · answered Jul 13 '12 at 18:49

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.

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.

insert with order by is faster

2 Answers2