Repodb: Question: Error in trivial MSSQL BulkInsert scenario

[wokket]

G'day guys,

I'm investigating moving to RepoDB from Dapper, and wanted to start with a trivial bulk-insert (to benchmark against the current TVP based solution in dapper).

I've followed the installation, Bulk Operation docco and also reviewed the unit test and believe I'm doing everything required.

Below is my very simple console app:

using Leapfrog.Webjob.ChildUpdates;
using RepoDb;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Benchmarks
{
    class Program
    {
        static void Main(string[] _)
        {
            var data = new List<ChildDataModel>
            {
                new ChildDataModel
                {
                    FieldOffice = "Office",
                    Birthdate = DateTime.Today,
                    BirthDateVerified = "Y",
                    ChildID = "Child-1234",
                }
            };

            var connString = "Server=(localdb)\\mssqllocaldb;Database=Integration-Test;User ID=webjob_user;Password=9k8^QQYPD2kb!;MultipleActiveResultSets=true"; ;

            SqlServerBootstrap.Initialize();
            using var conn = new SqlConnection(connString);

            conn.BulkInsert("dbo.children", data);
        }
    }
}

Unfortunately when I run this I recieve the error:

Unhandled exception. System.InvalidOperationException: No database fields found for 'dbo.children'.
   at RepoDb.SqlConnectionExtension.BulkInsertInternal(SqlConnection connection, String tableName, DbDataReader reader, IEnumerable`1 dbFields, IEnumerable`1 mappings, Nullable`1 options, Nullable`1 bulkCopyTimeout, Nullable`1 batchSize, SqlTransaction t
ransaction)
   at RepoDb.SqlConnectionExtension.BulkInsert[TEntity](SqlConnection connection, String tableName, IEnumerable`1 entities, IEnumerable`1 mappings, Nullable`1 options, Nullable`1 bulkCopyTimeout, Nullable`1 batchSize, SqlTransaction transaction)
   at Benchmarks.Program.Main(String[] _) in D:\dev\Leapfrog\src\Benchmarks\Program.cs:line 33

I can't see a way of manually populating the DBFieldCache, passing in a collection of mappings (1:! field to database names) didn't help, and isn't used in the unit tests either.

What am I missing in order to get the data into the database?

[mikependon]

I need more information about this. Can you try and revert the following?

• What is the result of this DbFieldCache.Get<ChildDataModel>();?
• Temporary Solution: Can you try add [Map("[dbo].[Children]")] in the ChildDataModel class and simply call the connection.BulkInsert<ChildDataModel>(data); rather than the BulkInsert(TableName, Data)?
• Without doing item 2, can you use the DataEntityDataReader<T> instead?

using (var connection = new SqlConnection(connString))
{
    using (var reader = new DataEntityDataReader<ChildDataModel>(data))
    {
        connection.BulkInsert("[dbo].[Children", reader);
    }
}

[mikependon]

The tests you visited is for InsertAll not for the extended library BulkInsert. You can look the BulkInsert Test Suites here. Your scenario can be found here.

And here is for SDS suites.

[wokket]

Thanks for the quick feedback, and the pointer to the other tests, in my messing around trying to get something to work I must have got confused about which code I was looking at 👍

• Calling DbFieldCache.Get(conn, "dbo.Children", null) gives me an empty list. I couldn't see a public generic method on there.
• The temp solution made no difference. I'd tried that earlier but repeated the experiment just now with no luck.
• Same error using the DataEntityDataReader as well.

I've kept hacking on things trying to get a change in behaviour, and changing from the application account to Integrated Security=true in the connection (effectively moving to an sa account) has fixed the problem 😃

Can you let me know what database rights are required for the FieldCache to do its thing correctly and I'll repeat the test with those minimum rights? I've also confirmed that I can't see a first-chance SQL exception of any sort when it fails....

[mikependon]

Thank you for this, though I already validated this before (it seems that I need to revisit this permission thing).

The database helper of SQL Server is using this command text when extracting the database columns. It requires only a READ permission (but please do let me reverify with this again when it comes to INFORMATION_SCHEMA). I will revert back to you soon (I am just working on other request).

Btw, as I can see it, you are trying to compare the performance of.

• Dapper UDT types
• RepoDb.BulkInsert

Can you also include the RepoDb.InsertAll in your benchmarks? Ensure to benchmark the 2nd execution of InsertAll as the first execution includes the compilation (which around 1-2 seconds).

[wokket]

No rush on the permissions response, I have plenty to play with tonight now I can get data into the database.

Our current codebase is using TVP's to pass a few thousand rows to a sproc for processing (which is primarily just insert). I've used custom code to Bulk Insert previously, but not wired into the ORM.

re InsertAll: Will do. I'm using Benchmark.net for the actual tests, so all the warmup costs are accounted for :+1:

[mikependon]

Sharing this to you (though the actual site is not yet completed). You can find the entire new documentation of the library here.

Never I had tried comparing the BulkInsert from the ADO.NET/SQLServer UDT objects. Interesting to see your benchmark result as well, please do share if you have made it.

[wokket]

Benchmark results:

|                             Method |       Mean |    Error |   StdDev |      Gen 0 |      Gen 1 | Gen 2 | Allocated |
|----------------------------------- |-----------:|---------:|---------:|-----------:|-----------:|------:|----------:|
|                         Dapper_TVP |   288.3 ms |  5.70 ms | 10.99 ms |  2000.0000 |  1000.0000 |     - |  14.59 MB |
|          Manual_BulkInserterHelper |   431.6 ms |  8.52 ms |  7.55 ms |  7000.0000 |  3000.0000 |     - |  42.13 MB |
| RepoDb_BulkInsertAsync_IEnumerable |   257.4 ms |  5.10 ms |  7.15 ms |  3000.0000 |          - |     - |  13.87 MB |
|   RepoDb_BulkInsertAsync_DataTable |   214.1 ms |  4.28 ms | 12.08 ms |  4000.0000 |  1000.0000 |     - |  21.56 MB |
|       RepoDb_InsertAll_IEnumerable | 3,502.9 ms | 38.08 ms | 33.76 ms | 24000.0000 | 11000.0000 |     - | 126.25 MB |

I'm not sure why the BulkInsertHelper is allocating so much (it's all in System.Data.Listeners<>.Action<> 's) but it doesn't really matter for this excercise either :+1:

[mikependon]

@wokket - that's how fast using the UDT in the ADO.NET for SQL Server? It is almost identical with the real BulkInsert and I never expect that. Almost the same performance as BulkInsert via overriden IDbDataReader of RepoDb. Impressive!

Did you use DataTable or is it something a customized enumerable/datareader object?

CONS of using UDT: You have to manually create a UDT on each table that you would like do the BulkInsert, other than that it is great based on your benchmark.

[wokket]

We have a helper method that builds a data table from a POCO and we use dappers AsTableValuedParameter to pass that in.

Comparing the UDT/TVP solution vs RepoSB's BulkInsert for us:

• Using the UDT via TVP is consistently a touch more RAM and requires you to create (simple) and maintain (more annoying) the UDT.
• Runtime perf was effectively the same over different runs, with some runs showing TVP's faster and others bulk insert depending on what else was happening on the benchmark box
• Sprocs (via TVP's) give us the flexibility to do more than just an insert with the data, and also to pass multiple tables of data to manage full collections of parent/child data in one swoop.

It may be worth noting this is a local uncontended SQL instance on very fast storage, so code that streams more data over the network or whathaveyou will perform differently... i.e. this isn't a scientific comparison!

[mikependon]

Your situation 3 can also be done via BulkInsert, but you need to maintain a staging table and target it as your table during the process (which I think for you a much more annoying than UDT, perhaps), then do the same thing as what you were doing with UDT - including the data of both parent and child in single process. But I find it a very rare scenario and maybe as per-need-basis.

Anyway, thank you for this information. Please do not hesitate to revert if you have more questions.

[mikependon]

@wokket - hey do you have any news here mate. How is the output of your investigation as a whole?

[wokket]

Hey mate. There isn't enough of a gain for us to move the current project away from Dapper. I'll be keeping this in mind for future work though. Up to you how you want to handle the original issue (difficult to diagnose failure in Bulk Insert caused by lack of permissions).

[mikependon]

I will handle those permission thing soon, no worries. Thanks for testing the perf. In anyway, I will close this ticket for now. Cheers!