Data is inserted into this table daily and identified with a date. Of course each new set of data has a higher date than previous sets.
One designer used a combination key with the date and ID (autonumber) columns. He also created a unique index on the autonumber column.
The primary key combo was set with a sort order of ascending for both component columns. Also, the Date was indexed separately as well.
First, by making the date the first component column of the combo, is that really going to improve either query or insert performance ?
Wouldn't just using the ID as primary key and the date column indexed be just as efficient ? Heck, it would reduce the number of indexes by two, and that would certainly help the INSERT performance.
Finally, wouldn't the thrashing thru the index be reduced if the sort order of the combo key components was set to descending ?
Just looking for some feedback/ideas here.
One designer used a combination key with the date and ID (autonumber) columns. He also created a unique index on the autonumber column.
The primary key combo was set with a sort order of ascending for both component columns. Also, the Date was indexed separately as well.
First, by making the date the first component column of the combo, is that really going to improve either query or insert performance ?
Wouldn't just using the ID as primary key and the date column indexed be just as efficient ? Heck, it would reduce the number of indexes by two, and that would certainly help the INSERT performance.
Finally, wouldn't the thrashing thru the index be reduced if the sort order of the combo key components was set to descending ?
Just looking for some feedback/ideas here.
