People,

DB2 V8 LUW FP16.

My C++ application inserts several rows per minute in a table.

I am currently revamping the insert statements and have learned about
Array Inserts (CLI) and db2Import.

Considering large amounts of data and concurrency concerns, which one
should be the best for insert speed ?

Reference: http://tinyurl.com/6xc5x7

Thanks,

"Michel Esber" <michel@us.automatos.comwrote in message
news:41852547-7e29-49dd-b2e5-15e398de46e8@a1g2000hsb.googlegroups.com...

People,
>
DB2 V8 LUW FP16.
>
My C++ application inserts several rows per minute in a table.
>
I am currently revamping the insert statements and have learned about
Array Inserts (CLI) and db2Import.
>
Considering large amounts of data and concurrency concerns, which one
should be the best for insert speed ?
>
Reference: http://tinyurl.com/6xc5x7
>
Thanks,

Several rows per minute? DB2 can insert thousands of rows per second. Don't
worry about.

But it would help if you used a prepared statement with parameter markers
("?") or static embedded SQL. That would relieve DB2 from the task of having
to compile the SQL each time. Multiple row inserts are also useful.

Several rows per minute? DB2 can insert thousands of rows per second. Don't

worry about.
>
But it would help if you used a prepared statement with parameter markers
("?") or static embedded SQL. That would relieve DB2 from the task of having
to compile the SQL each time. Multiple row inserts are also useful.

Hi Mark,

My application currently uses multiple row inserts, but does not use
prepared statements. Question: can I use prepared statement with
parameter markers and multiple rows inserts ? Is the overhead of
binding variables to markers worth it?

In fact, given the nature of my application and limited size of HW, I
need to achieve better performance than multi-row inserts (currently
used).

What should be my best option: array insert or db2Import ?

Thanks, Michel.

Michel Esber wrote:

My application currently uses multiple row inserts, but does not use
prepared statements. Question: can I use prepared statement with
parameter markers and multiple rows inserts ? Is the overhead of
binding variables to markers worth it?

Binding is always cheaper than compiling.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

"Michel Esber" <michel@us.automatos.comwrote in message
news:87a965c5-fa32-4a66-b8e9-96293410527d@34g2000hsh.googlegroups.com...

Hi Mark,
>
My application currently uses multiple row inserts, but does not use
prepared statements. Question: can I use prepared statement with
parameter markers and multiple rows inserts ? Is the overhead of
binding variables to markers worth it?
>
In fact, given the nature of my application and limited size of HW, I
need to achieve better performance than multi-row inserts (currently
used).
>
What should be my best option: array insert or db2Import ?
>
Thanks, Michel.

If you are inserting a few rows per minute, I don't think array insert or
db2import is going to help at all.

If you are using C++, I assume you can use static SQL (pre-compile, compile
C code, bind package), which is the fastest way to do it. With static SQL
you would use host variables (which is similar in concept to parameter
markers).

If you are inserting a few rows per minute, I don't think array insert or

db2import is going to help at all.
>
If you are using C++, I assume you can use static SQL (pre-compile, compile
C code, bind package), which is the fastest way to do it. With static SQL
you would use host variables (which is similar in concept to parameter
markers).

Hello,

Several incoming connections insert approx 360k rows per minute.

Currently, my application design has threads that request a connection
to the pool and start concurrent multi-row inserts (no parameter
markers). The system is IO-Bound.

I am trying to propose a different application design, where inserts
would be made by an import batch process (maybe another thread ??)

Instead of executing several 300 row inserts, the new design should
accumulate rows in memory up to a certain limit (let´s say, 20k
rows).
After that, an import process will be started to 'flush' rows to the
DB.

I was hoping that the system will benefit from less IO bound
concurrent queries with this approach, and would perform faster. Does
that make sense ?

Or should I start only by changing my current multi-rows inserts to
use prepared statements and parameter markers?

Thanks, Michel.

Michel Esber wrote:

>
>
Hello,
>
Several incoming connections insert approx 360k rows per minute.
>
Currently, my application design has threads that request a connection
to the pool and start concurrent multi-row inserts (no parameter
markers). The system is IO-Bound.
>
I am trying to propose a different application design, where inserts
would be made by an import batch process (maybe another thread ??)
>
Instead of executing several 300 row inserts, the new design should
accumulate rows in memory up to a certain limit (let´s say, 20k
rows).
After that, an import process will be started to 'flush' rows to the
DB.
>
I was hoping that the system will benefit from less IO bound
concurrent queries with this approach, and would perform faster. Does
that make sense ?

I'm not sure that will do it. Do you know why the system is I/O bound?
It it reading, writing? What's your buffer pool hit ratio?

Or should I start only by changing my current multi-rows inserts to
use prepared statements and parameter markers?

You should do that anyway.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab