[quoted text, click to view]


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.

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

[quoted text, click to view]


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

[quoted text, click to view]

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

[quoted text, click to view]

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

[quoted text, click to view]

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"