Batching API feedback and testing

[Jiří Činčura]

Hi *,

I merged the initial implementation of batching with Firebird 4 (https://github.com/FirebirdSQL/NETProvider/issues/878). I'd like to ask you to try alpha3 (https://www.nuget.org/packages/FirebirdSql.Data.FirebirdClient/9.0.0-alpha3) of provider and get me some feedback how the API feels and what constructs might be cumbersome (feel free to include your ideas of how to fix it).

To get initial idea of how to start, you can check the tests https://github.com/FirebirdSQL/NETProvider/blob/master/src/FirebirdSql.Data.FirebirdClient.Tests/FbBatchCommandTests.cs (at the moment huge batches might not work https://github.com/FirebirdSQL/NETProvider/blob/master/src/FirebirdSql.Data.FirebirdClient.Tests/FbBatchCommandTests.cs#L226).

Thanks.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Mason Wheeler]

There are plenty of use cases for batching, but the big obvious one is bulk loading.  While ADO.NET does not have a base class defining a Bulk Copy API a la DbCommand, DbConnection, etc, the implementations used by various providers remains pretty consistent: the ones for MySQL, Postgres, Oracle, etc all look a whole lot like SQL Server's SqlBulkCopy class in the basic shape of their API.

IMO the best starting place for user-facing batch APIs would be the creation of a FirebirdBulkCopy class matching this same API shape.

[Jiří Činčura]

The bulk copy is something that could be done on top of batching API, but it's not the primary goal of this API. Mostly because batching can do a bit wider range of scenarios than a general bulk copy.

[Mason Wheeler]

Sure, but right now the lack of a bulk copy class is a very noticeable pain point.  A bulk insert that takes about 5 minutes on other databases takes closer to 6 hours on Firebird.  This is a missing feature that's expected to not be missing, and having it unavailable could easily influence people to choose a different database rather than Firebird.

[Kjell Rilbe]

Fwiw, bulk copying data using SQL inserts is in my experience very slow
on all database engines. MySQL & MariaDB have support for select to and
from CSV files, with rather flexible format options and abilities to do
things with the data on the fly.

Firebird has external tables which are very efficient for import and
export of data. The problem with them is that they actually have a
binary format that's neither easy to prepare for import into FIrebird,
nor to handle after export from Firebird. The best option is to use (non
var-) char fields with a fixed-size character set, but that implies
limitations and/or workarounds when you would really prefer to just use
a regular UTF-8 encoded CSV file.

My recommendation regardless of batching and/or bulk copy features in
the .NET provider would be to implement CSV import/export support in the
Firebird engine. To my surprise I didn't find any such suggestion the FB
tracker, so I added one:
https://github.com/FirebirdSQL/firebird/issues/7110

Mvh,
Kjell

> --
> You received this message because you are subscribed to the Google
> Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to firebird-net-pro...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/firebird-net-provider/4c699a31-3606-4841-845c-b9eefe145fecn%40googlegroups.com
> <https://groups.google.com/d/msgid/firebird-net-provider/4c699a31-3606-4841-845c-b9eefe145fecn%40googlegroups.com?utm_medium=email&utm_source=footer>.