ADO Recordset


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: ADO Recordset

  1. #1
    deepa george Guest

    ADO Recordset


    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.


  2. #2
    Chris Hylton Guest

    Re: ADO Recordset


    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.
    >



  3. #3
    Merijn Boom Guest

    Re: ADO Recordset

    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.
    >




  4. #4
    Chris Hylton Guest

    Re: ADO Recordset


    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.
    >>

    >
    >



  5. #5
    Paul Clement Guest

    Re: ADO Recordset

    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)

  6. #6
    Guy Smith Guest

    Re: ADO Recordset


    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)



  7. #7
    Paul Clement Guest

    Re: ADO Recordset

    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)

  8. #8
    Guy Smith Guest

    Re: ADO Recordset


    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.




  9. #9
    Chris Hylton Guest

    Re: ADO Recordset


    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.

    >
    >



  10. #10
    Paul Clement Guest

    Re: ADO Recordset

    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)

  11. #11
    Guy Smith Guest

    Re: ADO Recordset


    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


  12. #12
    Chris Hylton Guest

    Re: ADO Recordset


    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
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center