Close connection after each call?

Discussion in 'ASP General' started by dw, Dec 16, 2003.

  1. dw

    dw Guest

    Which is the most efficient way to hit the database: 1) to open/close the
    connection for each call to the database on a page (we have Subs that do
    this); 2) or use the same connection and close it at the end? My feeling is
    that 2 is better, but just wanted to confirm with the experts. Thanks in
    advance.
     
    dw, Dec 16, 2003
    #1
    1. Advertising

  2. I use 2). However, your environment may be different... you may have more
    or less overhead when initiating a connection, you might be doing a *lot* of
    processing between calls, etc.

    When you're curious about "most efficient" - find out for yourself, rather
    than asking opinions. Almost all opinions you get will certainly leave out
    some variable you've forgotten to mention.

    Some tips on timing code:

    http://www.aspfaq.com/2092
    http://www.aspfaq.com/2245

    Stress testing:

    http://www.aspfaq.com/2139

    Measuring SQL Server performance:

    http://www.aspfaq.com/2513

    General suggestions for efficient ASP development:

    http://www.aspfaq.com/2424

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "dw" <> wrote in message
    news:OOQog7#...
    > Which is the most efficient way to hit the database: 1) to open/close the
    > connection for each call to the database on a page (we have Subs that do
    > this); 2) or use the same connection and close it at the end? My feeling

    is
    > that 2 is better, but just wanted to confirm with the experts. Thanks in
    > advance.
    >
    >
     
    Aaron Bertrand - MVP, Dec 16, 2003
    #2
    1. Advertising

  3. dw

    dw Guest

    Thanks, Aaron. Also thanks for the useful links :)

    "Aaron Bertrand - MVP" <> wrote in message
    news:%23JeB%23G$...
    > I use 2). However, your environment may be different... you may have more
    > or less overhead when initiating a connection, you might be doing a *lot*

    of
    > processing between calls, etc.
    >
    > When you're curious about "most efficient" - find out for yourself, rather
    > than asking opinions. Almost all opinions you get will certainly leave

    out
    > some variable you've forgotten to mention.
    >
    > Some tips on timing code:
    >
    > http://www.aspfaq.com/2092
    > http://www.aspfaq.com/2245
    >
    > Stress testing:
    >
    > http://www.aspfaq.com/2139
    >
    > Measuring SQL Server performance:
    >
    > http://www.aspfaq.com/2513
    >
    > General suggestions for efficient ASP development:
    >
    > http://www.aspfaq.com/2424
    >
    > --
    > Aaron Bertrand
    > SQL Server MVP
    > http://www.aspfaq.com/
    >
    >
    >
    >
    > "dw" <> wrote in message
    > news:OOQog7#...
    > > Which is the most efficient way to hit the database: 1) to open/close

    the
    > > connection for each call to the database on a page (we have Subs that do
    > > this); 2) or use the same connection and close it at the end? My feeling

    > is
    > > that 2 is better, but just wanted to confirm with the experts. Thanks in
    > > advance.
    > >
    > >

    >
    >
     
    dw, Dec 16, 2003
    #3
  4. dw

    Ray at Guest

    I'll typically use on connection.

    Sub OpenData()
    ''create ado object with global scope
    End Sub

    Sub CloseData()
    '''closes and destroys ado object
    End Sub



    OpenData
    Set rs1 = adoObject.Execute(whatever)
    ''code, code, code
    Set rs2 = adoObject.Execute(whateverElse)
    CloseData

    Ray at work


    "dw" <> wrote in message
    news:OOQog7%...
    > Which is the most efficient way to hit the database: 1) to open/close the
    > connection for each call to the database on a page (we have Subs that do
    > this); 2) or use the same connection and close it at the end? My feeling

    is
    > that 2 is better, but just wanted to confirm with the experts. Thanks in
    > advance.
    >
    >
     
    Ray at, Dec 16, 2003
    #4
  5. dw

    dw Guest

    Thanks, Ray. That's a very smart way of doing ADO.

    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:uf4VsL$...
    > I'll typically use on connection.
    >
    > Sub OpenData()
    > ''create ado object with global scope
    > End Sub
    >
    > Sub CloseData()
    > '''closes and destroys ado object
    > End Sub
    >
    >
    >
    > OpenData
    > Set rs1 = adoObject.Execute(whatever)
    > ''code, code, code
    > Set rs2 = adoObject.Execute(whateverElse)
    > CloseData
    >
    > Ray at work
    >
    >
    > "dw" <> wrote in message
    > news:OOQog7%...
    > > Which is the most efficient way to hit the database: 1) to open/close

    the
    > > connection for each call to the database on a page (we have Subs that do
    > > this); 2) or use the same connection and close it at the end? My feeling

    > is
    > > that 2 is better, but just wanted to confirm with the experts. Thanks in
    > > advance.
    > >
    > >

    >
    >
     
    dw, Dec 16, 2003
    #5
  6. dw

    Ray at Guest

    If you Dim the variable outside of the subroutine, it will have global
    scope. Example:


    Dim oADO

    Sub OpenData()
    Set oADO = Server.CreateObject("ADODB.Connection")
    oADO.Open ConnectionString
    End Sub

    Sub CloseData()
    oADO.Close
    Set oADO = Nothing
    End Sub


    Ray at work




    "Peter Foti" <> wrote in message
    news:...
    > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    > news:uf4VsL$...
    > > I'll typically use on connection.
    > >
    > > Sub OpenData()
    > > ''create ado object with global scope
    > > End Sub

    >
    > Ray, how do create it with global scope? I typically do something very
    > similar, except I pass in the variable that I want to hold the connection
    > object (because I couldn't figure out how to create the object with global
    > scope without creating it outside of the call).
    >
    >
    > > OpenData
    > > Set rs1 = adoObject.Execute(whatever)
    > > ''code, code, code
    > > Set rs2 = adoObject.Execute(whateverElse)
    > > CloseData

    >
    > Is adoObject a variable name that you created, or some intrinsic object in
    > the ADO world?
    >
    > Thanks,
    > Peter Foti
    >
    >
     
    Ray at, Dec 16, 2003
    #6
  7. dw

    dw Guest

    You can also use the Execute function to create global variables within Subs
    and Functions:

    Sub test()
    Execute("cn = Null") ' this is a bug in VBS that allows variables
    created this way to be global in scope. "cn" wasn't dimmed elsewhere.
    Set cn = Server.CreateObject(....)
    End Sub

    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:...
    > If you Dim the variable outside of the subroutine, it will have global
    > scope. Example:
    >
    >
    > Dim oADO
    >
    > Sub OpenData()
    > Set oADO = Server.CreateObject("ADODB.Connection")
    > oADO.Open ConnectionString
    > End Sub
    >
    > Sub CloseData()
    > oADO.Close
    > Set oADO = Nothing
    > End Sub
    >
    >
    > Ray at work
    >
    >
    >
    >
    > "Peter Foti" <> wrote in message
    > news:...
    > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    > > news:uf4VsL$...
    > > > I'll typically use on connection.
    > > >
    > > > Sub OpenData()
    > > > ''create ado object with global scope
    > > > End Sub

    > >
    > > Ray, how do create it with global scope? I typically do something very
    > > similar, except I pass in the variable that I want to hold the

    connection
    > > object (because I couldn't figure out how to create the object with

    global
    > > scope without creating it outside of the call).
    > >
    > >
    > > > OpenData
    > > > Set rs1 = adoObject.Execute(whatever)
    > > > ''code, code, code
    > > > Set rs2 = adoObject.Execute(whateverElse)
    > > > CloseData

    > >
    > > Is adoObject a variable name that you created, or some intrinsic object

    in
    > > the ADO world?
    > >
    > > Thanks,
    > > Peter Foti
    > >
    > >

    >
    >
     
    dw, Dec 16, 2003
    #7
  8. dw

    Ray at Guest

    Hmm. Interesting.

    Ray at work

    "dw" <> wrote in message
    news:...
    > You can also use the Execute function to create global variables within

    Subs
    > and Functions:
    >
    > Sub test()
    > Execute("cn = Null") ' this is a bug in VBS that allows variables
    > created this way to be global in scope. "cn" wasn't dimmed elsewhere.
    > Set cn = Server.CreateObject(....)
    > End Sub
     
    Ray at, Dec 16, 2003
    #8
  9. dw

    Ray at Guest

    It sounds to me like you don't use Option Explicit. Tisk tisk! :]

    Ray at work

    "Peter Foti" <> wrote in message
    news:...

    >
    > Ok, that's essentially what I do. The only difference being that I pass

    in
    > the variable as a parameter to the function. I find it to be a helpful
    > reminder to Dim the variable outside. In your case, if you forget to Dim
    > oADO, your function will create a local copy (which could yield errors

    when
    > you try to use oADO), whereas mine explicitly requires it to be passed in.
    > Otherwise, they are the same. :)
    >
    > Peter
    >
    >
     
    Ray at, Dec 16, 2003
    #9
  10. dw

    dlbjr Guest

    Class Process
    Private mConn

    Private Sub Class_Initialize()
    Set mConn = CreateObject("ADODB.Connection")
    mConn.Open "CONNECTION_STRING"
    GetData
    End Sub

    Private Sub_Terminate()
    Set mConn = Nothing
    End Sub

    Private Sub GetData()
    'Some emplimentation left out
    rs.Open strSQL,mConn,adLockReadOnly,adCmdTable
    rs.Open strSQL,mConn,adLockReadOnly,adCmdTable
    End Sub
    End Sub

    -dlbjr

    Discerning resolutions for the alms
     
    dlbjr, Dec 16, 2003
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michael
    Replies:
    2
    Views:
    4,346
    Kevin Spencer
    Dec 30, 2003
  2. Bob
    Replies:
    3
    Views:
    482
    =?Utf-8?B?TWlsb3N6IFNrYWxlY2tpIFtNQ0FEXQ==?=
    Feb 22, 2007
  3. Bob
    Replies:
    0
    Views:
    434
  4. Helene Unterwieser
    Replies:
    14
    Views:
    3,715
    Esmond Pitt
    Jul 3, 2007
  5. Iñaki Baz Castillo
    Replies:
    7
    Views:
    896
    Iñaki Baz Castillo
    Jan 12, 2010
Loading...

Share This Page