Multi-insert statement in INSERT command

Question

I have to download the data residing in a SQLite database in my Xamarin app into a Firebird database which is on my PC on the same WiFi network and I reach with IP address. Everything works correctly, but the unloading times are very long.

This is the connection string:

connessione = @"Server=" + ip_server + @";User=sysdba;Password=XXXXXXXX;Pooling=true;Database=" + ip_server + @":C:\\FirebirdSQL\\Photo.FDB";

This is the SQL statement:

string _dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), VarGlobal.dbSQLITE);
var db = new SQLiteConnection(_dbPath);
FbCommand cmdfb = new FbCommand("INSERT INTO photo (id,rifiddir,immaginebit) VALUES (@id,@rifiddir,@immaginebit)", conn);      
cmdfb.Parameters.Add("@id", FbDbType.VarChar);
cmdfb.Parameters.Add("@rifiddir", FbDbType.VarChar);
cmdfb.Parameters.Add("@immaginebit", FbDbType.Binary);
conn.Open();
var tuttelefoto = db.Query<Photo>("SELECT id,rifiddir,immaginebit FROM Photo");
foreach (var singolafoto in tuttelefoto)
{
    cmdfb.Parameters["@id"].Value = singolafoto.Id;
    cmdfb.Parameters["@rifiddir"].Value = singolafoto.rifiddir;
    cmdfb.Parameters["@immaginebit"].Value = singolafoto.immaginebit;
    await cmdfb.ExecuteNonQueryAsync(); 
}

conn.Close();

It all works, but it takes 10 seconds for each insert. I wanted to rewrite the code in just one line:

FbCommand cmdfb = new FbCommand(" INSERT INTO PHOTO " +
" SELECT '1', 'one' FROM RDB$DATABASE " +    " UNION ALL SELECT '2', 'two ' FROM RDB$DATABASE " +    " UNION ALL SELECT '3', 'three' FROM RDB$DATABASE " +    " UNION ALL SELECT '4', 'four' FROM RDB$DATABASE ", conn);

The photo size is max 1,5 Mb. The WiFi is 166 Mbps.

How can I insert a byte[] field in text format? Could this solve my problems? Many of you will tell me not to connect directly to the database, but to use a web service. I know. But the app only works when the phone and PC are on the same network.

Answer 1

Part of the problem is how blobs are transferred between the client and Firebird, and the default transfer size used by the Firebird ADO.net provider (FirebirdSql.Data.FirebirdClient).

In Firebird, blobs are sent out of band. That is, their data is not transmitted inline in an execute, but it needs to be sent separately by the driver¹. First, the driver needs to create a blob, and then send the data in blocks (a.k.a. segments) in a request/response style. This request/response style introduces additional latency².

In the case of the Firebird ADO.net provider, the segment size used to transfer blobs defaults to 8192 bytes (8 KiB), and can be controlled by the PacketSize connection property (with a maximum of 32767 bytes, or 32 KiB - 1).

For example, with a photo of 1.5 MiB, the default value of PacketSize (8192) means the photo is transferred in 192 packets. If you use the maximum of 32767, you transfer it in 49 packets. Say the roundtrip latency between the client and server is 10 milliseconds, then that is the difference between 1.9 seconds and 0.5 seconds (this ignores things like the time it takes for the data itself to be read and transferred, and the time the Firebird database might spend on storing the data).

TL;DR: Set the PacketSize connection property to 32767.

---

1. ^{Since Firebird 4.0 this can be partially addressed with batch execution, but a lot of driver don't support this yet (fully) because it makes execution a lot more complex}
2. ^{Drivers can address this by sending multiple requests before reading the response from the network, but not all drivers do}