Best way to call functions?

Discussion in 'ASP General' started by Jason Gates, Mar 3, 2004.

  1. Jason Gates

    Jason Gates Guest

    Dear all

    I have written a very complex web app for intranet use. It allows
    users to search a large database and then returns formatted results. I
    am currently employing a method as below:

    1. Create a global database connection
    2. Open a recordset that searches the database
    3. Loop through recordset calling the relevant function from an
    include file to display results
    4. Loop until EOF or PageSize reached.
    5. Close and destroy recordset
    6. Close and destroy connection


    Formatting function on include does the following:
    1. Create a recordset and open the record passed into the function as
    a primary key ID.
    2. Display each field in a formatted fashion.
    3. Close and destroy recordset.

    Is this efficent? There is a lot of creation and destruction of
    recordset objects (a typical search result may yield up to 400
    records) - each time a result is returned in fact. Additionally the
    database is queried twice for the same result. Once for the search
    recordset and then again for the formatting function. This has got to
    be slow and inefficient unless Microsoft have some clever thing that I
    don't understand similar to connection pooling (although I appreciate
    that this is only for ODBC connections to a database and nothing to do
    with recordsets!)

    I am aware that I could do this all inline in the main page - but the
    formatting is employed at several different locations in the database.
    What's worse is that the formatting function may call other formatting
    functions to retrieve formatted data in other tables that work in a
    similar fashion.

    Whilst this makes my code very easy to read and re-useable I am
    concerned that I am paying a very big penalty. Is there any way around
    this?

    Options I considered:
    - Recordsets declared globally - always available for use. (Although
    this won't work in many cases as lots of the functions are recursive)
    - plus this is messy!
    - Passing open recordsets by reference in the "right place" (on the
    correct record) to the function for it to manipulate. Is this
    efficient?
    - As above but passing by value? Is this just as slow?
    - Putting it all inline - will improve performance - but at the cost
    of reusable code

    Please advise - I'm pulling my hair out! Any changes will take me
    several days to do (there's a lot of this code!) so I want to make
    sure I get it right!!

    Thanks in advance
    Jason.
     
    Jason Gates, Mar 3, 2004
    #1
    1. Advertising

  2. Hi Jason,
    I read in a book and websites that is the best:
    create objects and just after your use destroy them!
    I believe that related to that I mentioned above, your application is fine.
    About the rest, let the others say something.
    bye,
    --

    ««««««««»»»»»»»»»»»»»»
    Vlmar Brazão de Oliveira
    Desenvolvimento Web
    HI-TEC


    "Jason Gates" <> escreveu na mensagem
    news:...
    > Dear all
    >
    > I have written a very complex web app for intranet use. It allows
    > users to search a large database and then returns formatted results. I
    > am currently employing a method as below:
    >
    > 1. Create a global database connection
    > 2. Open a recordset that searches the database
    > 3. Loop through recordset calling the relevant function from an
    > include file to display results
    > 4. Loop until EOF or PageSize reached.
    > 5. Close and destroy recordset
    > 6. Close and destroy connection
    >
    >
    > Formatting function on include does the following:
    > 1. Create a recordset and open the record passed into the function as
    > a primary key ID.
    > 2. Display each field in a formatted fashion.
    > 3. Close and destroy recordset.
    >
    > Is this efficent? There is a lot of creation and destruction of
    > recordset objects (a typical search result may yield up to 400
    > records) - each time a result is returned in fact. Additionally the
    > database is queried twice for the same result. Once for the search
    > recordset and then again for the formatting function. This has got to
    > be slow and inefficient unless Microsoft have some clever thing that I
    > don't understand similar to connection pooling (although I appreciate
    > that this is only for ODBC connections to a database and nothing to do
    > with recordsets!)
    >
    > I am aware that I could do this all inline in the main page - but the
    > formatting is employed at several different locations in the database.
    > What's worse is that the formatting function may call other formatting
    > functions to retrieve formatted data in other tables that work in a
    > similar fashion.
    >
    > Whilst this makes my code very easy to read and re-useable I am
    > concerned that I am paying a very big penalty. Is there any way around
    > this?
    >
    > Options I considered:
    > - Recordsets declared globally - always available for use. (Although
    > this won't work in many cases as lots of the functions are recursive)
    > - plus this is messy!
    > - Passing open recordsets by reference in the "right place" (on the
    > correct record) to the function for it to manipulate. Is this
    > efficient?
    > - As above but passing by value? Is this just as slow?
    > - Putting it all inline - will improve performance - but at the cost
    > of reusable code
    >
    > Please advise - I'm pulling my hair out! Any changes will take me
    > several days to do (there's a lot of this code!) so I want to make
    > sure I get it right!!
    >
    > Thanks in advance
    > Jason.
     
    Vilmar Brazão de Oliveira, Mar 3, 2004
    #2
    1. Advertising

  3. Jason Gates

    Bob Barrows Guest

    Jason Gates wrote:
    > Dear all
    >
    > I have written a very complex web app for intranet use. It allows
    > users to search a large database and then returns formatted results. I
    > am currently employing a method as below:
    >
    > 1. Create a global database connection


    Ignore the following if you mean that you are opening a database connection
    on each page.

    **********************************************************
    Assuming you are storing this connection in Application, this is very bad.
    ADO objects are not free-threaded, which means only one thread will be able
    to use this connection object at a time. Connections should be opened late
    and closed early in each page in which they are used.
    http://www.aspfaq.com/2053

    It is possible to use a batch file called makfre15.bat (located in
    C:\Program Files\Common Files\System\ADO on my machine) to cause a registry
    change to make the ADO objects free-threaded, allowing you to use them in
    Application. However, if any applications on that machine are connecting to
    Jet databases (Access), YOU MUST NOT DO THIS.
    **********************************************************


    > 2. Open a recordset that searches the database
    > 3. Loop through recordset calling the relevant function from an
    > include file to display results
    > 4. Loop until EOF or PageSize reached.


    Here are some alternatives that may improve your performance:
    http://www.aspfaq.com/show.asp?id=2120

    > 5. Close and destroy recordset
    > 6. Close and destroy connection


    Excellent.

    You may wish to consider using GetRows arrays to avoid all the recordset
    looping - looping through recordsets is incredibly slow compared to looping
    through arrays. Better yet, if possible, make use of GetString. Take a look
    at this:
    http://www.aspfaq.com/show.asp?id=2467

    Bob Barrows


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Mar 3, 2004
    #3
    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. Xiangliang Meng
    Replies:
    1
    Views:
    1,668
    Victor Bazarov
    Jun 21, 2004
  2. beginner
    Replies:
    7
    Views:
    572
    Alex Martelli
    Aug 19, 2007
  3. Aaron Scott

    Best way to pickle functions

    Aaron Scott, Apr 3, 2009, in forum: Python
    Replies:
    7
    Views:
    457
    azrael
    Apr 5, 2009
  4. Replies:
    17
    Views:
    167
    Thomas 'PointedEars' Lahn
    Mar 2, 2008
  5. Gelonida N
    Replies:
    0
    Views:
    246
    Gelonida N
    Mar 26, 2012
Loading...

Share This Page