Sybase: Does the column order in a non-clustered index affect insert performance?

Question

To be more specific (since the general answer to the subject is likely "yes"):

We have a table with lots of data in Sybase.

One of the columns is "date of insertion" (DATE, datetime type).

The clustered index on the table starts with the "DATE".

Question: For another, non-clustered index, does the order of columns (more specifically, whether "DATE" is the first or a second index column) affect the insert query performance?

Assume everything else is equal, e.g. the order of the second non-clustered index does not affect select query performance (even if it does, I don't care for the purposes of this question).

Answer 1

I believe it is largely determined by the Fill factor in the indexes and to a much lesser extent how selective the columns are.

Answer 2

what locking scheme is in place on the table All-pages or data-pages? (you can find out by selecting lockscheme('table_name'). The (application-observed) performance of index-maintenance is much better with data-pages locking scheme.

An index is ordered. The insert time depends on the cost of maintaining that order. If you insert rows with monotonically increasing values for the columns which are indexed then the index will grow 'at the end' and performance will be great (modulo any concurrency issues due to multiple concurrent updaters). The index tree will have to be re-balanced from time to time but I believe that is a rapid operation.

If the order of inserts is not in the same order as an index then that index will have to have entries inserted 'into the middle' and this is likely to cause page-splits (modulo there being sufficient space left unallocated by setting a fill-factor as mentioned above) and also 'index fragmentation'

anyway, the answer -- as always -- is to conduct some experiments and measure elapsed time and IO activity. You also might want to look at the optdiag output.

pjjH