Are there any plans to inckude some kind of BulkInsert operation for Ormlite?
This method is very useful if you are inserting multiple rows towards the database in a very speedy manner. It is high-performant in nature as it is using the real bulk operation natively from ADO.NET (via SqlBulkCopy class).
If you are working to insert range of rows from 1000 or more, then use this method.
See example here:
BulkInsert | RepoDB
SqlBulkCopy is SQL Server only. Other databases like Postgres, each have their own way of doing large bulk inserts. Bulk insert is also one of those things where even different native approaches like local CSV vs piped STD out can impact runtimes depending on how large the insert is.
The InsertAll method uses a single transaction, but if that is proving too slow, would be worth first testing with native file import options like Postgres copy or scripted to get a best case scenario speed to see if that is possible or if other steps like looking at hardware or parallel ingestion is worth looking at.
Previously for regular large inserts (2-300k rows every 10 minutes) I’ve used InsertAll partitioned into 5k chunks run parallel across a cluster worked well. This also enabled the ability to stagger imports to avoid overwhelming the DB instance.
Can you add the feature request to https://servicestack.net/ideas so we don’t forget about it.
In the meantime there’s some examples of performing BulkInserts at:
In case it helps, we’ve settled into a pattern of using what mssql calls “Table-Valued Parameters” for bulk inserts & updates. The gist is defining a UDT which represents the multiple fields you wish to upload at once and then defining a stored procedure parm as that UDT type. Within the stored procedure implementation, that UDT parm can then be joined to just like any other table in t-sql. Lots of docs and samples for that out there or just let me know if you’d like me to provide one.
We like the flexibility of using other database layers in tandem with OrmLite for specific needs. In this case we implement specific service stack request services with a custom stored procedure class we’ve evolved. The proc class handles marshalling the request DTO properties into stored proc parms, including creating ado.net datatables to upload bulk rows via table valued parameters, via reflecting over the DTO and mapping object properties to proc parms by name… we’re pleased to see several thousand rows perform very well this way.
In our environment we have a big legacy oracle and some newer mssql databases that interplay to achieve the overall system that we’re iteratively modernizing. So we’ve also created an equivalent oracle stored procedure class that uses their very similar “Nested Table” collection parameter type to provide an identical stored proc api for either platform. Being able to code typescript clients that deal in arrays of objects and forgetting about all the details between posting those and receiving them in sql land on either database platform is a satisfying low cognitive overhead to work in. Since you’re doing mssql, i won’t elaborate on oracle specifics here but thought it was worth mentioning from the cross platform standpoint since that’s a big draw for many folks coming to frameworks like OrmLite in the first place.
to round it out, here’s how simple the request implementation can look with everything else in place:
public bool Put(SamplesReceiveUpdate receive)
{
using var proc = OracleProcWithUserName("Samples_Receive_u", receive).ExecuteNonQuery();
return true; //nothing to report back to UI since they all simply get removed from receiving screen to indicate successful completion
}
bulk upload request DTO definition - notice List<> property
public class SamplesReceiveUpdate : IReturn<bool>
{
public string To_Prefix { get; set; }
public List<ReceiveContainer> Containers { get; set; }
}
ReceiveContainer DTO definition:
public class ReceiveContainer
{
public string SampleNum { get; set; }
public DateTime? CollectDate { get; set; }
public string ContainerGroup { get; set; }
public int? LabelID { get; set; }
public int BottleNum { get; set; }
public int ContainerTypeID { get; set; }
public bool ReceiveMethod { get; set; }
}
pertinent intro of the oracle stored proc definition:
create or replace PROCEDURE Samples_Receive_u (
IN_UserName in varchar2,
IN_To_Prefix in varchar2 := 'L', -- L = receive, P = unreceive, receive by default
IN_Containers in ReceiveContainers -- this is UDT, declaration shown next below
) as
begin
-- something like:
insert into mytable (SampleNum, CollectDate, ...)
select SampleNum, CollectDate, ...
from table (IN_Containers);
end;
/
oracle UDT declaration (t-sql syntax is actually very similar)
create or replace type ReceiveContainer FORCE as object (
SampleNum varchar2(20),
CollectDate date,
ReceiveMethod number(1), -- 1 = scanned, 0 = manual
ContainerGroup varchar2(30),
LabelID number,
BottleNum number,
ContainerTypeID number
);
/
create or replace type ReceiveContainers as table of ReceiveContainer;
/Support for RDBMS Bulk Inserts have been added for all supported RDBMS behind OrmLite’s new BulkInsert API in the latest ServiceStack v6.10 Release:
db.BulkInsert(rows);
Please see the ServiceStack v6.10 Release Notes for more info.
Which also includes a number of benchmarks we ran measuring the performance vs Single Inserts in the Which RDBMS has the fastest Bulk Insert implementation? Blog Post.