ADO Recordset

[deepa george]

Is there any ways to speed up my application in VB
Iam using VB as Front-end and Oracle as Back-end.
Using ADO for Database Connection.

[Chris Hylton]

what is slow about it...more specifics would be helpful...

chris

"deepa george" <geo_deepa@yahoo.com> wrote:
>
>Is there any ways to speed up my application in VB
>Iam using VB as Front-end and Oracle as Back-end.
>Using ADO for Database Connection.
>

[Merijn Boom]

Not even nowing what drivers you use to connect, try to use the oracle odbc
drivers instead of using the microsoft because this seems to work mutch
better (and faster).

An other thing of performance is recreating your queries (get only the
necisarry data) and try to handle as mutch as possible on the database
server (so rather write pl\sql scripts then vb code)

Good luck,

--
Merijn Boom
Senior Developer
Con-X-Com
Connecting Computers


"deepa george" <geo_deepa@yahoo.com> schreef in bericht
news:3d5d6a20$1@10.1.10.29...
>
> Is there any ways to speed up my application in VB
> Iam using VB as Front-end and Oracle as Back-end.
> Using ADO for Database Connection.
>

[Chris Hylton]

If you are using ADO, you really shouldn't even be using ODBC. The OLEDB
providers from both Microsoft and Oracle are both faster than anything you
can pull through ODBC. ODBC is a bottleneck in today's world of OLEDB data
access, it's just another layer of junk in the way of the data getting through
the pipe.

If you are using ODBC, eliminate it.

A couple of years back, the Oracle OLEDB provider did offer a bit more functionality
over the Microsoft version for Oracle...like the ability to return resultsets
from a stored proc (Oracle OLEDB had this functionality and MS did not)...not
sure if MS has added that functionality or not. You have to have the Oracle
client anyway...so I'd opt for the Oracle OLEDB provider anyway.

Chris

"Merijn Boom" <m.boom@con-x-com.nl> wrote:
>Not even nowing what drivers you use to connect, try to use the oracle odbc
>drivers instead of using the microsoft because this seems to work mutch
>better (and faster).
>
>An other thing of performance is recreating your queries (get only the
>necisarry data) and try to handle as mutch as possible on the database
>server (so rather write pl\sql scripts then vb code)
>
>Good luck,
>
>--
>Merijn Boom
>Senior Developer
>Con-X-Com
>Connecting Computers
>
>
>"deepa george" <geo_deepa@yahoo.com> schreef in bericht
>news:3d5d6a20$1@10.1.10.29...
>>
>> Is there any ways to speed up my application in VB
>> Iam using VB as Front-end and Oracle as Back-end.
>> Using ADO for Database Connection.
>>
>
>

[Paul Clement]

On 16 Aug 2002 14:09:52 -0700, "deepa george" <geo_deepa@yahoo.com> wrote:

¤
¤ Is there any ways to speed up my application in VB
¤ Iam using VB as Front-end and Oracle as Back-end.
¤ Using ADO for Database Connection.

Use connection pooling if you can by implementing the exact same connection string (this includes
the user ID and password) for each client. This will eliminate the lag time when opening new
connections under concurrent usage.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Guy Smith]

If the application is a client-server architecture have the client open one
connection to the Oracle DB and use it for all future ADO manipulations.
Close the connection when the application closes. With client-server architecture,
if the application is designed right, there is typically no need to use pooling.
If the application uses a lot of databound grid and such (where each control
opens it's own connection) pooling is the route to go.

Guy

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>On 16 Aug 2002 14:09:52 -0700, "deepa george" <geo_deepa@yahoo.com> wrote:
>
>¤
>¤ Is there any ways to speed up my application in VB
>¤ Iam using VB as Front-end and Oracle as Back-end.
>¤ Using ADO for Database Connection.
>
>Use connection pooling if you can by implementing the exact same connection
string (this
>includes
>the user ID and password) for each client. This will eliminate the lag time
when opening
>new
>connections under concurrent usage.
>
>
>Paul ~~~ pclement@ameritech.net
>Microsoft MVP (Visual Basic)

[Paul Clement]

On 20 Aug 2002 00:37:25 -0700, "Guy Smith" <no@email.co> wrote:

¤
¤ If the application is a client-server architecture have the client open one
¤ connection to the Oracle DB and use it for all future ADO manipulations.
¤ Close the connection when the application closes. With client-server architecture,
¤ if the application is designed right, there is typically no need to use pooling.

Of course if each client has an open connection through the lifetime of the application you can chew
up a substantial number of Oracle connection resources since there is no sharing involved. This
might work in an environment where there are relatively few users but it doesn't scale well.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Guy Smith]

Paul,

Connection pooling (sharing) will only save connections and help scalability
if used in a situation where the data connections are opened from a centralized
location, such as an MTS application with the data access layer on a central
server or ASP scripts opening ADO connection from an IIS server. For instance,
an IIS server opening ADO connections to a DB will re-use it's connections
if they are pooled. With centralized data access, connection pooling saves
resources and helps scalability, as you stated.

From the initial question, I am under the impression the application is a
client server architecture and is not making use of MTS, COM+ or DCOM, which
implies no centralized data access. This means the only place to implement
pooling is on each client. If each client has pooling turned on, connections
are still opened and maintained (by the pool) back to the database. Pooling,
in this scenario, is not going to reduce the number of connections to the
database or help scalability. I don't remember how the pool grows (can you
fill me in?). It either opens connection, based on simultaneous demand,
to a max number or it opens the max number of connections immediately (i.e.
10) for re-use. If pooling automatically opens 10 connection on each client,
when only 1 is needed, then pooling will make matters worse. If pooling opens
on demand and only one is demanded, then it is the same as opening and maintaining
a single connection.

As I eluded to before, the only reason I can see to use pooling in a client-server
architecture is if many controls, simultaneously open their own connection
to the database. So, if window X opens 10 connections and window Y opens
3, then pooling will improve performance. However, connections will be unnecessarily
held open by the pool. Even when Y only needs 3, 10 will be maintained by
the pool. But I doubt anyone (other than the MS Access team) would be silly
enough to design an application in this manner.

Are we one the same page now?

Guy

>Of course if each client has an open connection through the lifetime of
the application
>you can chew
>up a substantial number of Oracle connection resources since there is no
sharing involved.
>This
>might work in an environment where there are relatively few users but it
doesn't scale
>well.

[Chris Hylton]

Guy, your points make sense, but pooling at the client can eliminate connections
to the server if you are using disconnected recordsets.

In this case, your comment about multiple connections being established by
one app (even with a single ADO connection object) can occur. But, the key
here is that once you get the data from the database, you set the recordset
active connection to nothing and .Close the connection object.

What this benefits is that when apps that don't request or execute data for
some period of time (depending on the pool timeout, which defaults 30 seconds),
Oracle drops those sessions on the server (however many your app establishes
through ADO)...but they are reestablished VERY quickly on a connection .Open
method.

I've tested this pretty extensively with Oracle and it seems to work pretty
well. I've actually added a call to my data object that handles all access
through one ADO connection to set the SPTimeout for MSDAORA and Oracle.OLEDB
providers to 5 seconds before any connections are opened. What happens when
my app opens, it has to populate multiple comboboxes and pull the initial
application data. Sometimes I see an EXE create as many as 3-5 sessions
on Oracle. But, after the data is returned, within 5 seconds or so, Oracle
drops those sessions on the server and the VB app is working with local data.
Only when I need to hit the server again do I reestablish the connection...and
only then do sessions show back up on Oracle.

If your clients run multiple EXEs against the server and you don't close
the connection each time, every app has a session open on Oracle. In my
situation, that's the case (a user will commonly be running 5 or more EXEs
at the same time). So, in my case, I could have 10 apps open on the client
with NO sessions on Oracle.

Scabibility in this case I guess depends on what's greater, the number of
multiple sessions my methology creates VS the number of sessions created
by having multiple EXEs making their own 'permanent' connection while the
EXE is open.

Chris

"Guy Smith" <no@email.com> wrote:
>
>Paul,
>
>Connection pooling (sharing) will only save connections and help scalability
>if used in a situation where the data connections are opened from a centralized
>location, such as an MTS application with the data access layer on a central
>server or ASP scripts opening ADO connection from an IIS server. For instance,
>an IIS server opening ADO connections to a DB will re-use it's connections
>if they are pooled. With centralized data access, connection pooling saves
>resources and helps scalability, as you stated.
>
>From the initial question, I am under the impression the application is
a
>client server architecture and is not making use of MTS, COM+ or DCOM, which
>implies no centralized data access. This means the only place to implement
>pooling is on each client. If each client has pooling turned on, connections
>are still opened and maintained (by the pool) back to the database. Pooling,
>in this scenario, is not going to reduce the number of connections to the
>database or help scalability. I don't remember how the pool grows (can
you
>fill me in?). It either opens connection, based on simultaneous demand,
>to a max number or it opens the max number of connections immediately (i.e.
>10) for re-use. If pooling automatically opens 10 connection on each client,
>when only 1 is needed, then pooling will make matters worse. If pooling
opens
>on demand and only one is demanded, then it is the same as opening and maintaining
>a single connection.
>
>As I eluded to before, the only reason I can see to use pooling in a client-server
>architecture is if many controls, simultaneously open their own connection
>to the database. So, if window X opens 10 connections and window Y opens
>3, then pooling will improve performance. However, connections will be
unnecessarily
>held open by the pool. Even when Y only needs 3, 10 will be maintained by
>the pool. But I doubt anyone (other than the MS Access team) would be silly
>enough to design an application in this manner.
>
>Are we one the same page now?
>
>Guy
>
>>Of course if each client has an open connection through the lifetime of
>the application
>>you can chew
>>up a substantial number of Oracle connection resources since there is no
>sharing involved.
>>This
>>might work in an environment where there are relatively few users but it
>doesn't scale
>>well.
>
>

[Paul Clement]

On 20 Aug 2002 09:33:07 -0700, "Guy Smith" <no@email.com> wrote:

Hi Guy,

¤ Connection pooling (sharing) will only save connections and help scalability
¤ if used in a situation where the data connections are opened from a centralized
¤ location, such as an MTS application with the data access layer on a central
¤ server or ASP scripts opening ADO connection from an IIS server. For instance,
¤ an IIS server opening ADO connections to a DB will re-use it's connections
¤ if they are pooled. With centralized data access, connection pooling saves
¤ resources and helps scalability, as you stated.

True. That was my assumption, which may or may not be the case in this instance. In the case of
Oracle it's a major setup and maintenance pain if the data tier is on the client.

¤
¤ From the initial question, I am under the impression the application is a
¤ client server architecture and is not making use of MTS, COM+ or DCOM, which
¤ implies no centralized data access. This means the only place to implement
¤ pooling is on each client. If each client has pooling turned on, connections
¤ are still opened and maintained (by the pool) back to the database. Pooling,
¤ in this scenario, is not going to reduce the number of connections to the
¤ database or help scalability. I don't remember how the pool grows (can you
¤ fill me in?). It either opens connection, based on simultaneous demand,
¤ to a max number or it opens the max number of connections immediately (i.e.
¤ 10) for re-use. If pooling automatically opens 10 connection on each client,
¤ when only 1 is needed, then pooling will make matters worse. If pooling opens
¤ on demand and only one is demanded, then it is the same as opening and maintaining
¤ a single connection.

On the client it's more of a performance issue. Essentially you open a single persistent connection
in your application which acts as your "connection pool". Subsequent access to the database involves
opening another connection when needed and then closing it when no longer required. Microsoft refers
to it as OLEDB session or resource pooling.

¤
¤ As I eluded to before, the only reason I can see to use pooling in a client-server
¤ architecture is if many controls, simultaneously open their own connection
¤ to the database. So, if window X opens 10 connections and window Y opens
¤ 3, then pooling will improve performance. However, connections will be unnecessarily
¤ held open by the pool. Even when Y only needs 3, 10 will be maintained by
¤ the pool. But I doubt anyone (other than the MS Access team) would be silly
¤ enough to design an application in this manner.
¤
¤ Are we one the same page now?

You bet.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Guy Smith]

To see if I understand what you are saying:

Open the connection, get the data as an ADO recordset and then disconnect
the record set. The pool or Oracle will disconnect the connection when
it times out. The connection(s) is re-opened on an as-needed basis and then
timed out again.

As you stated, this scheme limits the number of database connections. It
does however incur the expense of high processor overhead on the DB server.
The reasons for leaving a connection open (in client-server) is to save the
DB the overhead of re-establishing a new session every time a client needs
a connection. Oracle grinds a bit evertime is has to setup a new session.
So there is a trade off, total connections vs extra server load. If your
goal is to limit connections to Oracle and the server can handle the extra
load with room for whatever extra scalability is required, your approach
meets it's purpose. If not, you might want to look at using COM+/MTS, with
a centralized data tier. The centralied tier allows pooling to work as is
intended: limiting total connections without the overhead of establishing
new connections.

I am not sure why you use pooling on the client. Why not open and close
connections as needed? Keeping them open for an extra 5 seconds via the
pool is not really gaining anything, since I imagine they almost always time-out
in that five second period anyhow.

>...but they are reestablished VERY quickly on
>a connection .Open method.

The quick re-establishment of the connection is the time it takes to re-open
a new connection, assuming 5 seconds have passed.

I have never thought about multiple apps sharing an ADO connection pool.
I've never had the circumstance where many different apps open the exact
same connection to the same DB. You are saying multiple apps can share the
same pool? I cannot see why not, just never had to think about it.

Guy Smith

[Chris Hylton]

Guy, the piece I've not had the opportunity to measure is the specific one
you mention...the load on Oracle to re-open a connection everytime I need
it. Not sure what the impact is in this case. I'm also still somewhat baffled
as to how it reopens the ADO connection so quickly, even after SPTimeout
has been reached. I don't actually destroy my ADO connection object, just
close it. But, when I fire the .Open again, after it was opened and closed
the first time, the connection reopens ready for use MUCH quicker than it
did the first time. I assume the connection object is keeping some level
of information about the connection (along with the connection string) in
order to reconnect.

What's even MORE strange, is that when that connection DOES reopen, the SID
on Oracle is the same as it was the first X times it was opened. If I close
and rerun the actual EXE, which reinstantiates the ADO connection, I get
a new SID, but closing/reopening the connection again, keeps the same SID
as long as the EXE is running.

To answer your other question about multiple apps using the same connection,
that's what we haven't gotten to yet...since you can't have a VB created
object that can have GetObject performed on it (or at least I don't know
how to do it), each EXE establishes it's own connection to the database through
our data DLL.

Knowing our app structure kinda helps to explain what we are doing. The
system here is has a large number of individual VB EXEs, due to the quantity
of code and needing to break up logical business applications (departments/functionality).
Prior to this new method of closing connections after each query/process
against the database, each VB app established it's own ADO connection and
kept it open as long as the VB EXE was running. The problem (DBA gripe)
was that if a user had multiple EXEs running, each one had an open connection
on Oracle. This solution of letting an EXE timeout the connection pool objects
was the best solution given what we were allowed to do/create for this client.

Another solution would be to build the object in VC++, so we could share
a single instance of the data object across all EXEs, but we don't have VC++
experience and just not willing (to this point) to go there.

MTS isn't an option for this app due to the client's requirements (not exactly
good requirements), but that would definitely be the BEST approach here...the
reason we haven't implemented this solution was that we couldn't use MTS
and therefore had to come up with another way to get around the problem.

Thanks for the tips/thoughts, definitely need to examine the hit on the Oracle
side for what we are doing...always good to bounce these concepts off other
folks to get other ideas or identify pitfalls.

Chris

"Guy Smith" <no@email.com> wrote:
>
>To see if I understand what you are saying:
>
>Open the connection, get the data as an ADO recordset and then disconnect
>the record set. The pool or Oracle will disconnect the connection when
>it times out. The connection(s) is re-opened on an as-needed basis and
then
>timed out again.
>
>As you stated, this scheme limits the number of database connections. It
>does however incur the expense of high processor overhead on the DB server.
>The reasons for leaving a connection open (in client-server) is to save
the
>DB the overhead of re-establishing a new session every time a client needs
>a connection. Oracle grinds a bit evertime is has to setup a new session.
> So there is a trade off, total connections vs extra server load. If your
>goal is to limit connections to Oracle and the server can handle the extra
>load with room for whatever extra scalability is required, your approach
>meets it's purpose. If not, you might want to look at using COM+/MTS,
with
>a centralized data tier. The centralied tier allows pooling to work as
is
>intended: limiting total connections without the overhead of establishing
>new connections.
>
>I am not sure why you use pooling on the client. Why not open and close
>connections as needed? Keeping them open for an extra 5 seconds via the
>pool is not really gaining anything, since I imagine they almost always
time-out
>in that five second period anyhow.
>
>>...but they are reestablished VERY quickly on
>>a connection .Open method.
>
>The quick re-establishment of the connection is the time it takes to re-open
>a new connection, assuming 5 seconds have passed.
>
>I have never thought about multiple apps sharing an ADO connection pool.
> I've never had the circumstance where many different apps open the exact
>same connection to the same DB. You are saying multiple apps can share
the
>same pool? I cannot see why not, just never had to think about it.
>
>Guy Smith
>