Using LLBLGen Version 2.6

I was looking to delete a large number of records (e.g. 1-2 million records) from a table called JournalEntries based on a timestamp column.

Instead of deleting the records all at one time, many have suggested deleting the records in smaller batches of say 1000 records each. I have found that it does save time when you do it that way.

So here is how I coded it in SQL Server:

declare @batchsize int
set @batchsize = 1000 
while @batchsize <> 0 
begin 
    delete top (@batchsize) from JournalEntries where OpTimeStamp < '2010'
    set @batchsize = @@rowcount 
end 

How do I code something equivalent in LLBLGen. I didn't see anything in the predicate system for doing "TOP 1000".

Thanks.

I don't know if it really speeds up the process. But if you really want this, I would opt for one of these options:

a. Fetch the IDs you want to delete. You can do this using a DynamicList to just fetch that field (pk). Then call the delete by batches. Something like this (approx):

List<int> idsToDelete = GetIdsToDelete(); // where you use DynamicList to obtain the list.
int batchSize = 500;

Queue<int> idsToDeleteQueue = new Queue<int>(idsToDelete);
while (idsToDeleteQueue.Count > 0)
{
    // don't remember if this method remove the elements from the queue, but you get the idea
    var idsToDeleteBatch = idsToDeleteQueue.Take(batchSize);

    // this will generate a WHERE Id IN (23,78,334,10, ...);
    IRelationPredicateBucket filter = new RelationPredicateBucket(MyEntityFields.Id == idsToDeleteBatch);
    adapter.DeleteEntitiesDirectly("MyEntityName", myFilter);
}

b. Use a stored procedure

c. I don't know if this will work for you, but I did something similar for UpdateEntitiesDirectly, adding a ORDER BY and LIMIT clauses. You have to extend a little bit some classes. In your case would be DeleteEntitiesDirectly and you just want to add a TOP clause. See this for more info: http://www.llblgening.com/archive/2009/08/updateentitiesdirectly-with-order-by-and-limit-mysql-specific/

Thanks daelmo.

My tests confirmed that deleting by batches does go faster than deleting it all at once. But the improvement in speed depends on the batch size. I found one batch size that was almost twice as fast as deleting the records with one call. I don't know if that batch size is the optimal size in all situations.

So it sounds like LLBLGen doesn't really have an equivalent of "DELETE TOP nnn FROM Table".

Thanks for the solutions you offered. I think I'll just use a stored procedure since that seems like a quick and simple solution.