SQLExecute running extremely slow

[Ken Brubaker]

Using DB2 8.1 FP5 on Win2000

After creating and configuring a new database, we stop db2 and start it
again. We then launch our application and the SQLExecute calls start slowly
and get progressively slower. Our application basically looks like this:

get an environment handle
get a connection handle
connect to the database
while we have more data to process
read and prepare 1000 rows of data
get a statement handle and prepare it
SQLExecute( the statement )
commit the changes
Each time through this loop, the SQLExecute call takes about 1 second longer
than the previous time.

The really odd thing about this is that is we stop our application and then
restart it, the slow down does not occur and each SQLExecute call takes less
than 1 second to run.

This application has been running under DB2 V2.1 for OS/2 and then DB2 V5.2
for Windows NT and Windows 2000 since 1996 with no problem. Even when we
recompile to the V8.1 libraries, the problem persists.

Can anyone explain this? It is possible that we are missing a configuration
option that will resolve this issue, but there are no obvious changes that
we can see.

[Knut Stolze]

Ken Brubaker wrote:

Using DB2 8.1 FP5 on Win2000

After creating and configuring a new database, we stop db2 and start it
again. We then launch our application and the SQLExecute calls start
slowly
and get progressively slower. Our application basically looks like this:

get an environment handle
get a connection handle
connect to the database
while we have more data to process
read and prepare 1000 rows of data
get a statement handle and prepare it
Do you allocate a new statement handle here?
SQLExecute( the statement )
commit the changes

Do you free the statement handle somewhere?

You might want to allocate the statement handle before the while-loop and
free it afterwards. Inside the loop, you can use the handle with a
different statement. But maybe the statement doesn't even change (except
some parameters), then preparing the statement once before the loop and
just binding different values to the parameter markers could be even
better.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Ken Brubaker]

We allocate, prepare and free the statement handle inside the while loop.

With some further investigation, I have discovered that if I put the
connection allocation/connect/disconnect inside the loop, the application
runs at full speed on its own. If, however, I have one of our other
applications holding an open connection to the same database, even if it is
not currently doing anything, the first application slows down. If I stop
the second application, the first one speeds up again. The application does
not seem to be affected by a command line connection to the database.
"Knut Stolze" <st****@de.ibm. com> wrote in message
news:c5******** **@fsuj29.rz.un i-jena.de...

Ken Brubaker wrote:

Using DB2 8.1 FP5 on Win2000

After creating and configuring a new database, we stop db2 and start it
again. We then launch our application and the SQLExecute calls start
slowly
and get progressively slower. Our application basically looks like this:
get an environment handle
get a connection handle
connect to the database
while we have more data to process
read and prepare 1000 rows of data
get a statement handle and prepare it

Do you allocate a new statement handle here?

SQLExecute( the statement )
commit the changes

Do you free the statement handle somewhere?

You might want to allocate the statement handle before the while-loop and
free it afterwards. Inside the loop, you can use the handle with a
different statement. But maybe the statement doesn't even change (except
some parameters), then preparing the statement once before the loop and
just binding different values to the parameter markers could be even
better.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

Ken Brubaker wrote:

We allocate, prepare and free the statement handle inside the while loop.

Why do you do that? It already "sounds" slow hearing it.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Ken Brubaker]

There may be historical reasons for doing things that way that I am not
aware of. I inherited the code in 2000 but it was originally written in '95
by a sub-contractor. Still, on modern computers under DB2 v5.2, it is
capable of commiting well over 1,000,000 transactions per hour while leaving
time for the computer to do other operations concurrently. It is only with
the move to 8.1 that our problems cropped up.

To make things a little clearer, here is the sequence of function calls we
are performing:

SQLAllocHandle( Environment )
SQLAllocHandle( Connection )
SQLConnect( )
main loop executes while data exists, or waits 30 seconds and looks for data
again
If there is data to process for table 1
create a statement with parameters
SQLAllocHandle( Statement )
prepare our data arrays
SQLPrepare( Statement )
SQLParamOptions ( for arrays of data )
SQLBindParamete r( once for each column )
SQLExecute( Statement )
SQLEndTran( commit the data )
SQLFreeHandle( Statement )
If there is data to process for table 2
create a statement with parameters
SQLAllocHandle( Statement )
prepare our data arrays
SQLPrepare( Statement )
SQLParamOptions ( for arrays of data )
SQLBindParamete r( once for each column )
SQLExecute( Statement )
SQLEndTran( commit the data )
SQLFreeHandle( Statement )
SQLDisconnect( only if the application is shutting down )
SQLFreeHandle( Connection )
SQLFreeHandle( Environment )
end program

"Knut Stolze" <st****@de.ibm. com> wrote in message
news:c5******** **@fsuj29.rz.un i-jena.de...

Ken Brubaker wrote:

We allocate, prepare and free the statement handle inside the while

loop.
Why do you do that? It already "sounds" slow hearing it.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

Ken Brubaker wrote:

There may be historical reasons for doing things that way that I am not
aware of. I inherited the code in 2000 but it was originally written in
'95
by a sub-contractor.
Well, I would definitively change the logic to avoid all the unnecessary
work that is done inside the loop. I really don't see a point arguing
about performance if the basic rules are discarded.
Granted, the degradation in V8 is not good either way. And I don't see a
reason why such a behavior as the one you noticed should occur.
Still, on modern computers under DB2 v5.2, it is
capable of commiting well over 1,000,000 transactions per hour while
leaving
time for the computer to do other operations concurrently. It is only
with the move to 8.1 that our problems cropped up.
Have you run an event monitor to see what the system is doing? Is an usual
amount of paging/swapping occurring? Did you follow the usual
performance-related steps (RUNSTATs, etc.)?
To make things a little clearer, here is the sequence of function calls we
are performing:

SQLAllocHandle( Environment )
SQLAllocHandle( Connection )
SQLConnect( )
main loop executes while data exists, or waits 30 seconds and looks for
data again
If there is data to process for table 1
create a statement with parameters
SQLAllocHandle( Statement )
prepare our data arrays
SQLPrepare( Statement )
SQLParamOptions ( for arrays of data )
SQLBindParamete r( once for each column )
I would move the above 6 lines outside the loop (except "prepare our data
arrays" maybe) and the SQLFreeHandle() below as well.
SQLExecute( Statement )
SQLEndTran( commit the data )
SQLFreeHandle( Statement )
If there is data to process for table 2
create a statement with parameters
SQLAllocHandle( Statement )
prepare our data arrays
SQLPrepare( Statement )
SQLParamOptions ( for arrays of data )
SQLBindParamete r( once for each column )
SQLExecute( Statement )
SQLEndTran( commit the data )
SQLFreeHandle( Statement )
Same as above for "table1".
SQLDisconnect( only if the application is shutting down )
SQLFreeHandle( Connection )
SQLFreeHandle( Environment )
end program

--
Knut Stolze
Information Integration
IBM Germany / University of Jena