Re: Finding a hole in a sequence using SQL vs native I/O

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Joe Pluta wrote:
> My conclusion is 
> that if your holes are interspersed throughout your file, native I/O is 
> your answer.  If your holes are consistently at the end of the sequence, 
> go with SQL.
>   

There is a better business solution to this if you don't have a lot of 
deleted records.  You can "soft-delete" the records by setting a deleted 
flag.  Then you can use an index over the deleted records to get the 
first one.  The downside is that deleted record continue to take up 
space in your database, but it may not be as much of a concern as adding 
seconds to every write.

Another option is to have a secondary file that contains only the keys 
of deleted records.  Add a record to this file when a file in the 
primary is deleted.  Read the lowest deleted key when adding a new 
record.  Pro: less space on disk.  Con: another file.

In either case, if no deleted records exist, take the highest key and 
add one.  Either of these methods will be much faster than a table scan.

Joe