Improve OutboxRecord delete performance

[SzymonPobiega]

Raised via GG https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/particularsoftware/nj0wMiucx_k/9AsTSTziDQAJ

Possibly related to #144

[SzymonPobiega]

@WilliamBZA I've created this issue to track the perf problem the user reported.

[WilliamBZA]

The outbox concept should be re-evaluated. In high throughput scenarios the Outbox table doesn't scale and the delete isn't able to keep up and results in table locks - especially with multiple services using a shared enpoint.

Potential solutions that may help (need a test harness first)

• Split the outbox table into two, where each table represents the de-dup and the processing aspects. This would mean that the delete is working on a smaller set of data for each type and would involve less of a lock.
• Update the data column to null once the message has been dispatched. This might make batch deletes faster
• Change the delete SQL to batch deletes (see below for an example)
• Have smarter cleanup logic involving different configurations for master/slaves where the master becomes primarily responsible for frequent cleanups

Example of batched delete statement (would need to convert to NHQL)

WHILE 1 = 1
BEGIN
    WITH t AS 
    ( 
        SELECT    TOP 1000 * 
          FROM    MyTable
         WHERE    MyField = 'abc123'
    )
    DELETE    t

    IF @@ROWCOUNT < 1000 BREAK
END

[ramonsmits]

@WilliamBZA This cannot be translated to NHQL as this is not ANSI SQL is it?

This also relates to #143

In high throughput environments we should run the purging query in a much higher interval. The default of a minute really does not make sense.

[WilliamBZA]

@ramon Probably not. But the whole query doesn't need to be NHQL. We could do the while loop in C# and the delete in NH.

[ramonsmits]

@WilliamBZA Doing to the loop in c# means you need to query for ID's and then do a batched delete on those ID's.

The autosensing behavior that I just put in a comment of #143 is probably the best solution when using NHibernate.

[WilliamBZA]

@ramonsmits why would you have to query the ID's?

A SQL CommandReader (I don't know NHibernate well enough) would look like:

int recordsDeleted = 0;
do {
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "DELETE TOP 1000 FROM MyTaable Where MyField = 'abc123'";
        recordsDeleted = command.ExecuteScalar();
    }
} while (recordsDeleted > 0);

[ramonsmits]

@WilliamBZA That is not using NHibernate, you are now by passing it and you are using TSQL specific TOP query which cannot be used with for example Oracle.

Bypassing is usually not an issue when using ANSI SQL but in this case it is.

[WilliamBZA]

@ramonsmits Ah, following you now.

[SzymonPobiega]

An alternative could be to have a fixed-size outbox for each endpoint. Then we would use UPDATE instead of INSERT/DELETE which I think is more SQL Server friendly.

[SzymonPobiega]

Unfortunately HQL does not allow limited deletes so this idea can't be implemented.