Re: SQL Insert - performance

On 25-Jul-2015 01:24 -0600, D*B wrote:

... both will be fast enough in all real world scenarios and
thinking about performance of parts of an application, which are fast
enough is waste of time! <<SNIP>>
Doing it the pure ISAM way an insert operation adds a record to the
end of the table and doing this one after another allows very
efficient blocking. Doing the insert the pure SQL way, you would
reuse deleted records and an insert operation has more effort
(finding the place to insert, positioning/loading and then the insert
and blocking would be limited by the size of free blocks.

And again, "it depends" because placing records in already existing space can be much faster than placing at the end, especially if another extent to the dataspace is required when solely appending. Row reuse also enables much faster [and much more literally] concurrent inserts than when limiting all adders to append-only for which conspicuously there would be more contention across multiple writers\inserters.

The beneficial blocking of records above the MI is irrespective the amount of rows that can fit in an existing storage location. And AIUI the database tends toward utilizing existing space known to be capable of holding the entire block and will simply append a large block for which there is not easily\quickly found some existing contiguous space; there is no requirement for the DB to utilized all existing reuse-space before\instead-of appending rows, as the option enabling Reuse Deleted Records (REUSEDLT) offers the capability rather than making a demand of the DB.