Subject Re: Blob Parsing Stored Procedure
Author lucas_jessee
Perhaps I should start over. My intention was not to offend you, nor
to make it seem as if I knew all the answers. Your advice has been
very helpful so far.

Let me start with this.

Here are the requirements that I have:
1. I must write a multi-database (not just Firebird) interface in the
language C++
2. I must be able to support the use of remote and "embedded" connections
3. The code must be able to compile on a variety of platforms
(Windows, *nix, Sun)
4. I must optimize the code to perform as well as it can (obviously)

As per those requirements, it seems to me that using ODBC is my
best/only choice. Perhaps this is an incorrect conclusion.

Then, as for #4, the performance, I have done the following test using
the table I described previously.

I tested doing 10000 INSERTS into the table with the code I previously
posted (single transaction, reusing statement, just doing SQLExecute
in the for loop) and it took 2.859 seconds to execute.

Next, I created a stored procedure that took the same parameters, and
just did an INSERT into the database. I executed this stored
procedure 10000 times with the same parameters in the example above.
This took 3.016 seconds.

Lastly, I created a stored procedure that took 50 parameters (instead
of just 5 for a single insert) and performed 10 INSERT's in a single
call. Thereby batching my inserts into groups of 10 (parameters 1-5
were for insert 1, parameters 6-10 were for insert 2, etc). This is
probably viewed as a silly approach, and I did it simply to test the
performance. I executed this stored procedure 1000 times (which would
insert 10000 records and all of the same data in the previous
examples). This took 1.219 seconds.

The conclusion that I drew is that by batching inserts I could achieve
a noticeable performance gain. Perhaps this is an incorrect
conclusion, though my results were fairly consistent, and scaled
linearly with the number of inserts.

In other databases you are able to do things such as
I was looking to achieve such functionality in Firebird so that I may
reap some of the performance gains that I saw in my third and fastest
test without writing the large and unwieldy stored procedure.

I am open to any suggestions that anyone can provide for improving the
performance of the described application. Note that some
pseudo-source was provided in one of my previous messages.

Thanks in advance!

--- In, Helen Borrie <helebor@...> wrote:
> I give up now, having become weary of continually answering a
> question for someone who is so sure he knows the answer before he
asks it.
> ./hb