Subject RE: [firebird-support] Blob Parsing Stored Procedure
Author Alan McDonald
> Hello all,
> I've got a lot of small inserts to do and its just taking much longer
> than I'm willing to wait for (even with an embedded DB). Basically
> these inserts are small records (roughly 4 BIGINT's) that have a FK to
> another larger table.

I think you're assuming (incorrectly) that the bottleneck is the passing of
data to the server.
A parametised insert query would not be any different in transport time
Versus the passing of a blob which then requires a parsing process.
The real bottleneck is the writing of the records (I think).

> If I'm reading everything correctly, Firebird does NOT support bulk
> inserts and anything close to it is kind of a hack. I was able to
> create a stored procedure that took 10x the parameters and did 10
> inserts at once. I saw a decent performance gain from this.
> What I would really like to do is to send a single BLOB to a stored
> procedure. This blob would be a Binary data structure that would
> contain N 32 byte blocks for this example. The first 32 byte block
> would be the first 4 BIGINT's to insert into the table, the second
> would be the next 4 BIGINT's, etc.
> Can a stored procedure be written to parse the blob in this manner?
> Basically I want to pass in an arbitrarily sized blob (I can pass in
> size as a parameter also if necessary...) and break it up into the 8
> byte blocks (32 bytes per row = 8bytes * 4 values) that actually
> represent the data to be stored in the database.
> OR - Can I pass in the values as text and parse it in the same manner.
> I could convert the 64bit numbers to 20 bytes of text and grab the
> numbers off in 20 byte chunks if necessary.
> Thanks in advance,
> Luke