ASP Processing - Performance Issues

[JP SIngh]

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

 

[McKirahan]

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.

 

[John Blessing]

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

 

[Bob Barrows [MVP]]

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