Dreaded database bloat - Append queries that bounce off indexes?

[chrisleng]

A couple of projects recently I've used a unique index to save having to do outer joins to only append new records (based on an IS NULL for example) it's quicker just to throw all the records at the table and let the index bounce 99.9% of them and only add any new ones,



However this seems to cause massive DB size bloat as this code is run regularly and each cycle is a good few thousand records, I notice the autonumber does increment, is this a known issue?

[Minty]

Assuming you are not using dbFailOnError, it will attempt to create the records, and fail. I would guess this will use up the autonumber ID's, it certainly does in SQL Serevr.
I would have assumed it would be more efficient to create criteria or suitable joins to only insert the required records ?

[Micron]

I have done this before as well because it's a lot simpler. However, I chose to run an actual query but you have to turn off warnings and ensure you have an error handler that will turn them back on. I can't recall if auto nums were generated, but I think not. That would be because as Minty says, .Execute method will roll back changes if it fails, but I couldn't have told you whether or not it uses autonumbers. But so what if it did? Those numbers are meaningless beyond uniquely identifying a record, and if you're concerned about them being contiguous, you're concerned about nothing. Unless you think this operation will cause you to soon exceed the auto number field size I wouldn't worry about it even if running the query generates numbers (but I don't think it will).