Skipping element creation if the query parameters limit is exceeded

[letarak]

DO 7.1.0

If insert batch query exceeded parameters limit (MSSQL ~2000) and persisted implicitly by query, elements missing in db Sample to reproduce

using System.Data.SqlClient;
using DoTest;
using Microsoft.Data.SqlClient;
using NUnit.Framework;
using Xtensive.Orm;
using Xtensive.Orm.Configuration;

internal class Program
{
    private static async Task Main(string[] args)
    {
        try
        {
            DbHelper.ExecuteNonQuery("DROP DATABASE [DO-Tests]");
        }
        catch (Exception)
        {
        }

        DbHelper.ExecuteNonQuery("CREATE DATABASE [DO-Tests]");

        var dc = new DomainConfiguration("sqlserver", new SqlConnectionStringBuilder(DbHelper.ConnectionString()).ToString());

        dc.Sessions.Add(new SessionConfiguration(WellKnown.Sessions.Default) { BatchSize = 25 });

        dc.Types.Register(typeof(TestEntity));

        dc.UpgradeMode = DomainUpgradeMode.Recreate;

        await using var d = await Domain.BuildAsync(dc);

        await using (var s = await d.OpenSessionAsync())
        await using (var t = await s.OpenTransactionAsync())
        {
            for (var i = 0; i < s.Configuration.BatchSize; i++)
            {
                var item = new TestEntity(s);

                for (var j = 0; j < 100; j++)
                {
                    item["Field" + j] = "test" + i;
                }
            }

            t.Complete();
        }

        await using (var s = await d.OpenSessionAsync())
        await using (var t = await s.OpenTransactionAsync())
        {
            // OK: created 25
            Assert.AreEqual(s.Configuration.BatchSize, s.Query.All<TestEntity>().Count());

            s.Remove(s.Query.All<TestEntity>());

            t.Complete();
        }

        await using (var s = await d.OpenSessionAsync())
        await using (var t = await s.OpenTransactionAsync())
        {
            for (var i = 0; i < s.Configuration.BatchSize; i++)
            {
                var item = new TestEntity(s);

                for (var j = 0; j < 100; j++)
                {
                    item["Field" + j] = "test" + i;
                }
            }

            await s.SaveChangesAsync();

            // OK: created 25
            Assert.AreEqual(s.Configuration.BatchSize, s.Query.All<TestEntity>().Count());

            t.Complete();
        }

        await using (var s = await d.OpenSessionAsync())
        await using (var t = await s.OpenTransactionAsync())
        {
            s.Remove(s.Query.All<TestEntity>());

            t.Complete();
        }

        await using (var s = await d.OpenSessionAsync())
        await using (var t = await s.OpenTransactionAsync())
        {
            Assert.AreEqual(0, s.Query.All<TestEntity>().Count());

            for (var i = 0; i < s.Configuration.BatchSize; i++)
            {
                var item = new TestEntity(s);

                for (var j = 0; j < 100; j++)
                {
                    item["Field" + j] = "test" + i;
                }
            }

            // Implicitly persisted
            var count = s.Query.All<TestEntity>().Count();

            // FAIL: created only 20
            Assert.AreEqual(s.Configuration.BatchSize, count);

            t.Complete();
        }
    }

    [HierarchyRoot]
    public class TestEntity : Entity
    {
        public TestEntity(Session session) : base(session)
        {
        }

        [Key] [Field(Nullable = false)] public int Id { get; set; }

        [Field] public string Field0 { get; set; }

        [Field] public string Field1 { get; set; }

        [Field] public string Field2 { get; set; }

        [Field] public string Field3 { get; set; }

        [Field] public string Field4 { get; set; }

        [Field] public string Field5 { get; set; }

        [Field] public string Field6 { get; set; }

        [Field] public string Field7 { get; set; }

        [Field] public string Field8 { get; set; }

        [Field] public string Field9 { get; set; }

        [Field] public string Field10 { get; set; }

        [Field] public string Field11 { get; set; }

        [Field] public string Field12 { get; set; }

        [Field] public string Field13 { get; set; }

        [Field] public string Field14 { get; set; }

        [Field] public string Field15 { get; set; }

        [Field] public string Field16 { get; set; }

        [Field] public string Field17 { get; set; }

        [Field] public string Field18 { get; set; }

        [Field] public string Field19 { get; set; }

        [Field] public string Field20 { get; set; }

        [Field] public string Field21 { get; set; }

        [Field] public string Field22 { get; set; }

        [Field] public string Field23 { get; set; }

        [Field] public string Field24 { get; set; }

        [Field] public string Field25 { get; set; }

        [Field] public string Field26 { get; set; }

        [Field] public string Field27 { get; set; }

        [Field] public string Field28 { get; set; }

        [Field] public string Field29 { get; set; }

        [Field] public string Field30 { get; set; }

        [Field] public string Field31 { get; set; }

        [Field] public string Field32 { get; set; }

        [Field] public string Field33 { get; set; }

        [Field] public string Field34 { get; set; }

        [Field] public string Field35 { get; set; }

        [Field] public string Field36 { get; set; }

        [Field] public string Field37 { get; set; }

        [Field] public string Field38 { get; set; }

        [Field] public string Field39 { get; set; }

        [Field] public string Field40 { get; set; }

        [Field] public string Field41 { get; set; }

        [Field] public string Field42 { get; set; }

        [Field] public string Field43 { get; set; }

        [Field] public string Field44 { get; set; }

        [Field] public string Field45 { get; set; }

        [Field] public string Field46 { get; set; }

        [Field] public string Field47 { get; set; }

        [Field] public string Field48 { get; set; }

        [Field] public string Field49 { get; set; }

        [Field] public string Field50 { get; set; }

        [Field] public string Field51 { get; set; }

        [Field] public string Field52 { get; set; }

        [Field] public string Field53 { get; set; }

        [Field] public string Field54 { get; set; }

        [Field] public string Field55 { get; set; }

        [Field] public string Field56 { get; set; }

        [Field] public string Field57 { get; set; }

        [Field] public string Field58 { get; set; }

        [Field] public string Field59 { get; set; }

        [Field] public string Field60 { get; set; }

        [Field] public string Field61 { get; set; }

        [Field] public string Field62 { get; set; }

        [Field] public string Field63 { get; set; }

        [Field] public string Field64 { get; set; }

        [Field] public string Field65 { get; set; }

        [Field] public string Field66 { get; set; }

        [Field] public string Field67 { get; set; }

        [Field] public string Field68 { get; set; }

        [Field] public string Field69 { get; set; }

        [Field] public string Field70 { get; set; }

        [Field] public string Field71 { get; set; }

        [Field] public string Field72 { get; set; }

        [Field] public string Field73 { get; set; }

        [Field] public string Field74 { get; set; }

        [Field] public string Field75 { get; set; }

        [Field] public string Field76 { get; set; }

        [Field] public string Field77 { get; set; }

        [Field] public string Field78 { get; set; }

        [Field] public string Field79 { get; set; }

        [Field] public string Field80 { get; set; }

        [Field] public string Field81 { get; set; }

        [Field] public string Field82 { get; set; }

        [Field] public string Field83 { get; set; }

        [Field] public string Field84 { get; set; }

        [Field] public string Field85 { get; set; }

        [Field] public string Field86 { get; set; }

        [Field] public string Field87 { get; set; }

        [Field] public string Field88 { get; set; }

        [Field] public string Field89 { get; set; }

        [Field] public string Field90 { get; set; }

        [Field] public string Field91 { get; set; }

        [Field] public string Field92 { get; set; }

        [Field] public string Field93 { get; set; }

        [Field] public string Field94 { get; set; }

        [Field] public string Field95 { get; set; }

        [Field] public string Field96 { get; set; }

        [Field] public string Field97 { get; set; }

        [Field] public string Field98 { get; set; }

        [Field] public string Field99 { get; set; }
    }
}

[alex-kulakov]

Hello @letarak,

I did investigation on this problem.

First of all, each RDBMS has its own parameter limit per batch (some of them has no limit, to be fair). In this case I assume you work with MS SQL Server, which has max parameter limit - 2100 parameters per batch. Exceeding this limit throws an exception. For example, your test in DO 5.0 ends up with such exception, because maximum number of parameters in your case is 25 * 101 = 2525. Way too much. DO 5.0 batching has only batch size as incoming parameter this is why it builds batch of 25 inserts no matter what.

On certain point we decided to make batching algorithm a bit smarter to prevent sever side exception because it is waste of time, especially when number of parameters is detectable. So starting from certain versions of 6.0, DataObjects.Net has maximum number of parameters for each provider and batching mechanism can lower number of SQL requests in batch to prevent parameters overflow. In extreme cases, it can execute batches with 1 SQL request, or throw an exception if it failed to configure even 1 SQL request batch.

You can clearly see that 20 items (or 2020 parameters) were persisted despite the fact that BatchSize is 25.

Apparently, new batching has an issue. I confirm this. It will be fixed in 6.0 and merged up to master.

During investigation I tackled the problem (it seems that, need to be tested) so PR with fix will appear soon. Merging will take a while because it has new code and needs to be tested as merged, but it will appear in newer versions.

[alex-kulakov]

It took more time to fix new batching. This is serious issue and it's my number one priority so it will be fixed for sure.

[alex-kulakov]

I finally fixed this issue and some more problems with batching in PR #348

[alex-kulakov]

@letarak, the issue must be fixed now in 6.0, 7.0 and 7.1 branches, patched versions are released. I will close the issue after your confirmation, just to be sure. I thank you for finding this serious problem.

[letarak]

Confirm, on 7.1.1 it works as expected Thank you