ASP Processing - Performance Issues

Discussion in 'ASP General' started by JP SIngh, Jul 29, 2005.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    Just wondering if you anyone can give me some tips on speeding up the asp
    page which has about 50 fields and 10 drop downs.

    The page loads up very slowly.

    Also I have couple of questions in relation to the performance of ASP Pages

    1. I open up the SQL Server connection in a file call dbconn.asp

    set conn = server.createobject("ADODB.Connection")
    conn.open "PROVIDER=SQLOLEDB;DATA
    SOURCE=MYSQLSRV;UID=User;PWD=SQLPa55;DATABASE=NEWDB"

    and include this file on top of every page.

    Is this a good practice? Or shall I be open the connection on every page and
    closing it on the page itself.

    2. The page has 10 dropdowns menus each of which can have about 500 odd
    records. Is there an alternative design solution so i don't have to open up
    11 recordsets 1 for the main page and 10 for the drop downs.

    Thanks in advance for your help

    Regards
     
    JP SIngh, Jul 29, 2005
    #1
    1. Advertising

  2. JP SIngh

    McKirahan Guest

    "JP SIngh" <> wrote in message
    news:...
    > Hi All
    >
    > Just wondering if you anyone can give me some tips on speeding up the asp
    > page which has about 50 fields and 10 drop downs.
    >
    > The page loads up very slowly.
    >
    > Also I have couple of questions in relation to the performance of ASP

    Pages
    >
    > 1. I open up the SQL Server connection in a file call dbconn.asp
    >
    > set conn = server.createobject("ADODB.Connection")
    > conn.open "PROVIDER=SQLOLEDB;DATA
    > SOURCE=MYSQLSRV;UID=User;PWD=SQLPa55;DATABASE=NEWDB"
    >
    > and include this file on top of every page.
    >
    > Is this a good practice? Or shall I be open the connection on every page

    and
    > closing it on the page itself.
    >
    > 2. The page has 10 dropdowns menus each of which can have about 500 odd
    > records. Is there an alternative design solution so i don't have to open

    up
    > 11 recordsets 1 for the main page and 10 for the drop downs.
    >
    > Thanks in advance for your help
    >
    > Regards



    Are the contents of the drop-downs relatively static?

    You could pre-build and store them in application variables in the
    global.asa.

    Hava a separate page than rebuilds the variables whenever the data changes.
     
    McKirahan, Jul 29, 2005
    #2
    1. Advertising

  3. "JP SIngh" <> wrote in message
    news:...
    > Hi All
    >
    > Just wondering if you anyone can give me some tips on speeding up the asp
    > page which has about 50 fields and 10 drop downs.
    >
    > The page loads up very slowly.
    >
    > Also I have couple of questions in relation to the performance of ASP
    > Pages
    >
    > 1. I open up the SQL Server connection in a file call dbconn.asp
    >
    > set conn = server.createobject("ADODB.Connection")
    > conn.open "PROVIDER=SQLOLEDB;DATA
    > SOURCE=MYSQLSRV;UID=User;PWD=SQLPa55;DATABASE=NEWDB"
    >
    > and include this file on top of every page.
    >
    > Is this a good practice? Or shall I be open the connection on every page
    > and
    > closing it on the page itself.



    No it is not good pracitce. Only open it when you need a connection to the
    database and close it asap.You can keep the connection string in an include
    file though, that way you only need to change it in one place. Or you could
    build a wrapper function for the connection and recordset opens, and have
    that in an include file, but never leave connections open when you don't
    need them.

    Also, you might find that using the server's IP address in the connection is
    quicker than using its name

    --
    John Blessing

    http://www.LbeHelpdesk.com - Help Desk software priced to suit all
    businesses
    http://www.room-booking-software.com - Schedule rooms & equipment bookings
    for your meeting/class over the web.
    http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
     
    John Blessing, Jul 29, 2005
    #3
  4. JP SIngh wrote:
    > Hi All
    >
    > Just wondering if you anyone can give me some tips on speeding up the
    > asp page which has about 50 fields and 10 drop downs.
    >
    > The page loads up very slowly.
    >
    > Also I have couple of questions in relation to the performance of ASP
    > Pages
    >
    > 1. I open up the SQL Server connection in a file call dbconn.asp
    >
    > set conn = server.createobject("ADODB.Connection")
    > conn.open "PROVIDER=SQLOLEDB;DATA
    > SOURCE=MYSQLSRV;UID=User;PWD=SQLPa55;DATABASE=NEWDB"
    >


    ahem, I hope these aren't really the uid and pwd for your database ...

    > and include this file on top of every page.
    >
    > Is this a good practice? Or shall I be open the connection on every
    > page and closing it on the page itself.


    The second. See John's reply.
    As to whether the IP or the server name is "quicker" I doubt that this is
    the bottleneck.

    >
    > 2. The page has 10 dropdowns menus each of which can have about 500
    > odd records. Is there an alternative design solution so i don't have
    > to open up 11 recordsets 1 for the main page and 10 for the drop
    > downs.
    >


    Caching is the obvious answer. How often do the options in the selects
    change? Do you really need to go back to the database for them every time?
    You can use Application, Session, or even local files to cache the options
    and avoid the trip to the database. I often use this technique for static
    lists:

    RegionDropDown.asp:
    <%
    Function RegionDropdown(pName, pDefault,pAllowAll)
    dim cn,rs,strsql,sOptions, sHTML, i
    sHTML=Application("Reg_HTML")
    if len(sHTML) = 0 then
    set cn = createobject("ADODB.CONNECTION")
    set rs=CreateObject("adodb.recordset")
    cn.open "provider=xx;data source=xx;user id=xx;password=xx"

    strsql="SELECT DISTINCT " & _
    "'<OPTION value=""' + CAST(RegionID AS varchar(2))" & _
    " + '"">' + RegDesc + '</option>' as [option] " & _
    "FROM dbo.COMPANYREGION " & _
    "ORDER BY [option]"

    set rs = cn.Execute(strsql,,1)
    if not rs.eof then sOptions= rs.GetString(,,"",vbCrLf)
    rs.Close:set rs=nothing
    cn.Close:set cn=nothing
    'Response.Write sOptions
    sHTML="<SELECT style=""font-family:Lucida Console"">" & _
    vbCrLf & sOptions & vbCrLf & "</SELECT>"

    'cache the results
    Application("Reg_HTML")=sHTML
    end if

    'Insert the name of the dropdown:
    sHTML=replace(sHTML,"<SELECT", _
    "<SELECT name=""" & pName & """")

    if pAllowAll then
    if InStr(sHTML,"value=""ALL""") = 0 then
    i=instr(sHTML,"<OPT")
    if i>0 then
    sHTML=left(sHTML,i-1) & _
    "<OPTION value=""ALL"">ALL</OPTION>" & vbCrLf & _
    mid(sHTML,i)
    end if
    end if
    elseif InStr(sHTML,"""ALL""") > 0 then
    sHTML=replace(sHTML, _
    "<OPTION value=""ALL"">ALL</OPTION>" & vbCrLf,"")
    end if
    if len(pDefault) > 0 then
    if InStr(sHTML,pDefault & """ SELECTED") = 0 then
    sHTML=replace(sHTML," SELECTED","")
    sHTML=replace(sHTML,"value=""" & pDefault & """>", _
    "value=""" & pDefault & """ SELECTED>")
    end if
    else
    if InStr(sHTML," SELECTED") > 0 then
    sHTML=replace(sHTML," SELECTED","")
    end if
    end if
    RegionDropdown=sHTML
    end function
    %>

    In the page where I want a Region dropdown:

    <!--#include virtual="/scripts/RegionDropDown.asp"-->
    <html><body><form ... >
    ....
    <%=RegionDropdown("P1","01",false)%>
    ....
    </form></body></html>

    HTH,
    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 [MVP], Jul 30, 2005
    #4
    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. JMaelstrom

    IIS 6 vs IIS 5 ASP.NET Performance Issues

    JMaelstrom, Dec 9, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    4,708
    shan420
    Apr 30, 2010
  2. smoody

    Performance Issues / ASP.NET

    smoody, Feb 20, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    579
    Kevin Spencer
    Feb 20, 2004
  3. Ram
    Replies:
    6
    Views:
    7,684
    codezilla94
    Nov 13, 2007
  4. =?Utf-8?B?U3VoYXMgVmVuZ2lsYXQ=?=

    ASP.Net Validators and performance issues

    =?Utf-8?B?U3VoYXMgVmVuZ2lsYXQ=?=, Jul 24, 2006, in forum: ASP .Net
    Replies:
    5
    Views:
    726
    Roland
    Jul 27, 2006
  5. Woody

    ASP performance issues

    Woody, Jun 5, 2004, in forum: ASP General
    Replies:
    10
    Views:
    220
Loading...

Share This Page