473,698 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQLExecute running extremely slow

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.
Nov 12 '05 #1
5 3262
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
Nov 12 '05 #2
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

Nov 12 '05 #3
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
Nov 12 '05 #4
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

Nov 12 '05 #5
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
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4865
by: vishal | last post by:
Hi I a am executing a stored procedure from C code using SQLExecute . It takes some time to Execute the procedure. However when I see the processor usage during that time it shows 100% cpu usage. Can anyone tell me why SQLExecute uses 100% CPU when running the stored proceudre. Thanks vishal
0
387
by: Imran Aziz | last post by:
Hello All, I have developed an ASP.net application using the latest beta release (version 2 beta 2)of dot net using Visual studio .net express edition. I have been testing the application using the debugger and built in web browser, and it works fine on it. But now that I have copied the contents to an IIS 5.1 Server the application runs dead slow, In the Build -> configuration manager I was not able to find an option for release build...
5
3245
by: garydevstore | last post by:
Hi, I have a table defined as CREATE TABLE ( IDENTITY (1, 1) NOT NULL , NULL , NULL , NOT NULL , NULL , (255) COLLATE SQL_Latin1_General_CP1_CS_AS
83
5936
by: D. Dante Lorenso | last post by:
Trying to use the 'search' in the docs section of PostgreSQL.org is extremely SLOW. Considering this is a website for a database and databases are supposed to be good for indexing content, I'd expect a much faster performance. I submitted my search over two minutes ago. I just finished this email to the list. The results have still not come back. I only searched for: SECURITY INVOKER
2
2548
by: gcmf8888 | last post by:
I use socket client to connect to a server written in C++. I found out that the C# socket connection(I didn't use TCPClient instead i used socket) is extremely slow(comparing to Java and C++ one I wrote before). I found that since my firewall will popup the alert window. Any suggestion?
3
1791
by: Oenone | last post by:
In VB6, it's possible to create a project with a class library within it. The project can then be run with F5, and it sits and waits for an external process to create an instance of one of its objects. This is extremely useful, as it allows me (for example) to create a PlugIn class running in source, and call it from a host application that is running as an executable. In VB.NET, I'm not allowed to run class library projects, I simply...
0
1732
by: Carlo Marchesoni | last post by:
I have an application that has a main.aspx page (something like a todo list). Clicking one of the items open an new window (javascript window.open) with a new aspx page. this is extremely slow. if I type the same (slow) aspx page in the address bar of the browser it appears immediately. This makes me believe that window.open together with aspx is very slow - but I don't know why and don't see the reason. Opening a html page with...
1
2578
by: Octoryia | last post by:
Our company has an access front end database that is used by 30+ people all day, five days a week. The issues that we are running into include the database running extremely slow, not responding, and displaying ODBC failed call errors messages. We have the tables housed on the SQL server wich we thought permit nurmerous users to access the data at once. But this is not working. We are having issues with a single user with 9+ connections, or...
5
10679
by: coldpizza | last post by:
I am trying to fill a sqlite3 database with records, and to this end I have written a class that creates the db, the table and adds rows to the table. The problem is that the updating process is *extremely* slow, and occasionally I get the message "database locked". I tried removing "self.con.commit()" in the add_record method, but then nothing is saved in the db. I don't know whether this has anything to do with it, but I have found...
0
8671
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9152
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9016
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8887
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5858
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2321
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1997
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.