VB app connecting to Oracle database

I have a custom VB application that connects to an Oracle database ( It inserts records into an interface table then disconnects from Oracle. The VB app is on about 400 PCs with about 5000 inserts into the interface table daily. My question is having this app connect and disconnect so many times all day long hurting the database? Would it be more efficient for these 400 PCs to just remain connected all day without ever logging out? Any opintions? Thanks, Sharon

Answer Wiki

Thanks. We'll let you know when a new response is added.

I also do this type of transactions. I don’t think it hurts the database to connect/disconnect. I have found that you have to weigh the problems of each option. The problem with connect/disconnect is that there is a certain amount of wait time for the application to make the connection. If the wait time is quite noticable, you can multiply that time by the number of users and by the number of times they connect and you will see the amount of time wasted by connecting/disconnecting all day long. On the other hand, the problem with leaving the connection is that it is holding resources on the Oracle database. Is your database configured to handle that many connections? Does the server have enough resources to allow that many connections at once?

Hope these thoughts help


Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • SheldonLinker
    Leaving a connection open "costs" virtually nothing, in terms of system resources. Closing a connection is "cheap", too. Establishing a connection is time-consuming. Best to leave the connection open. --- Sheldon Linker Linker Systems, Inc. linkersystems.com sol@linker.com 800-315-1174 +1-949-552-1904 from outside North America
    30 pointsBadges:
  • Skovac
    Thanks for yout thoughts. What prompted this was the following chunck from my statspack report. I searched for mathing sql and found out that it something that happens every time VB makes a connection. CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 35,708,923 1,273 28,051.0 18.5 413.14 418.99 496609904 Module: WIP_Mover.exe select * from (select null TABLE_CATALOG, idx.table_owner TABLE_ SCHEMA, idx.table_name TABLE_NAME, null INDEX_CATALOG, idx.owner INDEX_SCHEMA, idx.index_name INDEX_NAME, null PRIMARY_KEY, deco de(idx.uniqueness, 'UNIQUE', 65535, 0) UNIQUE_,null CLUSTERED,nu ll TYPE, null FILL_FACTOR, idx.initial_extent INITIAL_SIZE, null
    0 pointsBadges:
  • Gcherer
    Often, the main controllable drag in this scenario is the dispatcher's allocation of stubs to handle the task. Pre-allocated stubs saves some effort at the expense of some memory. Try MTS or consider a another tier between the client and server. The new tier would keep a few more or less constant logins, but keep track of which client is doing what. Using the new security facilities in 9i makes this very slick. In your case, the actual number of clients and transactions makes it hard to cost-justify far-sighted solutions. But, you would know whether your environment is growing or staying static. g'luck.
    0 pointsBadges:
  • VB app connecting to Oracle database (Q/A) | Seek The Sun Slowly
    [...] Address: https://itknowledgeexchange.techtarget.com/itanswers/vb-app-connecting-to-oracle-database/ (0) Comments Read [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: