Re: Function OpenConn

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Aaron Bertrand [MVP] (aaron_at_TRASHaspfaq.com)
Date: 04/28/04 

Date: Tue, 27 Apr 2004 21:46:29 -0400

If the function is in an include file (or at the top of every page), you
could see a marginal increase in performance for those pages that don't use
a database connection at all, and therefore don't need to call the function.
(Of course, you could just as easily put the straight connection code into
its own include, and likewise decide whether to bother including the code at
all.)

However, I've found that abstracting this code into a function makes it very
tempting to wrap all database activity like this:

call openConn()
openConn.execute "INSERT ... "
call closeConn()

...other code...

call openConn()
openConn.execute "UPDATE ... "
call closeConn()

While logic will tell us that opening the connection and closing it as soon
as possible is the most efficient use of resources, this will rarely be the
case in reality. Opening and closing that connection multiple times in a
single page almost guarantees that your performance will suffer.

I'll be the first to admit that I use an #include file that unconditionally
opens a connection object at the top of the page, and another that closes
and destroys it at the bottom. That may seem wasteful, but keep in mind
that in 99% of the ASP applications I write, every page touches the
database. (For example, my navigation/menus are almost always driven by the
database.) 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim Bowman" <james@jbassoc.biz> wrote in message 
news:518801c42cbf$9539b7e0$a601280a@phx.gbl...
> My Authorware training piece sends user scores to a
> Microsoft Access database through an .asp page.  In
> opening the connection to the database, I use this code:
>
> function OpenConn()
>
> strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=" & Server.MapPath(".") & "\open_database.mdb;"
> set OpenConn=Server.CreateObject
> ("ADODB.Connection")
> OpenConn.open strConn
>
> end Function
>
> In an IIS example, I see the three interior lines of
> code, just as I have them, without the function code
> ("function OpenConn" and "end Function") in the first and
> last lines.  Is there any value in using the function
> code, e.g. does it improve reliability or performance?
>
> Thanks.
>
> Jim Bowman

Relevant Pages

  • Re: SQL CE 3.5 and Data Loss on SD Card
    ... with a database on SD card I would be greatful. ... devices we have experienced only one corrupted SDF database. ... I attribute our success to opening a connection just before every DB access ...
    (microsoft.public.sqlserver.ce)
  • Project ODBC Performance
    ... doing a File Open and Save As when using a MS SQL Server database? ... The problem seems to lie in network utilization or ODBC ... have no issue opening the 5MB file but none of them has been successful ... Is there a way to optimize the ODBC connection between the ...
    (microsoft.public.project.vba)
  • Project ODBC Performance
    ... doing a File Open and Save As when using a MS SQL Server database? ... The problem seems to lie in network utilization or ODBC ... have no issue opening the 5MB file but none of them has been successful ... Is there a way to optimize the ODBC connection between the ...
    (microsoft.public.project.standard_and_server)
  • Re: chances of getting error no 87, hr = 2147221000 (CO_E_DLLNOTFOUND on SQL CE 2.0)
    ... Following is the code I am getting error, here I am opening the database ... Open method for opening the connection to the database. ... of such a static routine provides the alternate means to do the same. ...
    (microsoft.public.sqlserver.ce)
  • Function OpenConn
    ... Microsoft Access database through an .asp page. ... opening the connection to the database, ... ("function OpenConn" and "end Function") ...
    (microsoft.public.inetserver.asp.db)