SQL: Is it quicker to insert sorted data into a table?

Question

A table in Sybase has a unique varchar(32) column, and a few other columns. It is indexed on this column too. At regular intervals, I need to truncate it, and repopulate it with fresh data from other tables.

insert into MyTable  
  select list_of_columns  
  from OtherTable  
  where some_simple_conditions  
  order by MyUniqueId  

If we are dealing with a few thousand rows, would it help speed up the insert if we have the order by clause for the select? If so, would this gain in time compensate for the extra time needed to order the select query?

I could try this out, but currently my data set is small and the results don't say much.

Answer 1

I don't believe order speeds in INSERT, so don't run ORDER BY in a vain attempt to improve performance.

Answer 2

I'd say that it doesn't really matter in which order you execute these functions. Just use the normal way of inserting INSERT INTO, and do the rest afterwards.

Answer 3

Hello,

I can't say about sybase, but MS SQL inserts faster if records are sorted carefully. Sorting can minimize number of index expansions. As you know it is better to populate the table ant then create index. Sorting data before insertion leads to the similar effect.

Answer 4

The order in which you insert data will generally not improve performance. The issues that affect insert speed have more to do with your databases mechanisms for data storage than the order of inserts.

One performance problem you may experience when inserting a lot of data into a table is the time it takes to update indexes on the table. However again in this case the order in which you insert data will not help you.

If you have a lot of data and by a lot I mean hundreds of thousands perhaps millions of records you could consider dropping the indexes on the table, inserting the records then recreating the indexes.

Answer 5

With only a few thousand rows, you're not likely to see much difference even if it is a little faster. If you anticipate approaching 10,000 rows or so, that's when you'll probably start seeing a noticeable difference -- try creating a large test data set and doing a benchmark to see if it helps.

Since you're truncating, though, deleting and recreating the index should be faster than inserting into a table with an existing index. Again, for a relatively small table, it shouldn't matter -- if everything can fit comfortably in the amount of RAM you have available, then it's going to be pretty quick.

One other thought -- depending on how Sybase does its indexing, passing a sorted list could slow it down. Try benchmarking against an ORDER BY RANDOM() to see if this is the case.

Answer 6

Dropping and recreating indexes (at least in SQL server) is by far the best way to do the inserts. At least some of the time ;-) Seriously though, if you aren't noticing any major performance problems, don't mess with it.