Dave, You can use ConvertRecord or JoltTransformRecord right before PutDatabaseRecord to remove the unindexed / unnecessary fields, then the generated statement should only include the necessary fields and be able to take advantage of any indexes.
Regards, Matt On Fri, Sep 20, 2019 at 4:55 PM David Gallagher <[email protected]> wrote: > > Hi – I’m looking to remove records from a table using the results returned > from a QueryDatabaseTable processor. I want to do this in batches because > it’s going to require a lot of deletes, and flowfile per record would be too > slow. I’ve tried using the PutDatabaseRecord processor, but the DELETE option > in StatementType builds a statement that uses every field in the AVRO record. > That would ignores the index on the table and do a full table scan per > delete, which obviously isn’t going to scale well. Is there a way I’m not > thinking of to get the optimized delete statements I’m looking for? The trick > is, my plan is to delete based on the key and then to insert the record, as > I’m just using this as a means of getting around the lack of UPSERT in my > database. > > > > Thanks, > > > > Dave
