Decreased performance when creating an object in classic asp
While trying to measure the performance of a website, I created a simple log sub
to see which parts are slow during the execution of a classic asp page.
sub log(logText)
dim fs, f
set fs = Server.CreateObject("Scripting.FileSystemObject")
set f = fs.OpenTextFile("log.txt", 8, true)
f.WriteLine(now() & " - " & logText)
f.Close
set f = Nothing
set fs = Nothing
end sub
log "Loading client countries"
set myR = Server.CreateObject("ADODB.RecordSet")
myR.ActiveConnection = aConnection
myR.CursorLocation=2
myR.CursorType=3
myR.LockType=2
myR.Source = "SELECT * FROM CC ORDER BY ccName ASC"
log "Opening db connection"
myR.open()
log "Opened db connection"
Here are the results (only showing the time portion):
...
11:13:01 - Loading client countries
11:13:06 - Opening db connection
11:13:06 - Opened db connection
...
It ADODBRecordSet
takes about 5 seconds to create and set multiple properties. This doesn't always happen, sometimes the code is fast, but usually when I reload the page after a few minutes the load time is more or less the same as in the example. Could this be a server / resource issue or should I rewrite the code? (My best option would be to write this in C#
, but I must study this code first before moving forward).
Update . I added some more code after receiving 1st comment to introduce some more codes. Explanation: Code creates combobox
(select menu) with extracted countries (it continues where the previous code snippet left off).
if not myR.eof then
clientCountries = myR.getrows
send("<option value='0'>Select country</option>")
log "Creating combobox options"
for i = 0 to ubound(clientCountries, 2)
if cstr(session("clientCountry")) <> "" then
if cstr(clientCountries(1, i)) = cstr(session("clientCountry")) then
isSelected = "selected" else isSelected = ""
end if
end if
if cstr(session("clientCountry")) = "" then
if cstr(clientCountries(1, i)) = "23" then
isSelected = "selected"
else
isSelected = ""
end if
end if
optionString = ""
optionString = clientCountries(2, i)
send("<option value='" & clientCountries(1, i) & "' " & isSelected & ">" & convertToProperCase(optionString) & "</option>")
next
log "Created combobox options"
end if
myR.Close
myR.ActiveConnection.close
myR.ActiveConnection = nothing
set myR = nothing
log "Loaded client countries"
The next two log entries look like this:
11:13:06 - Creating combobox options
11:13:06 - Created combobox options
...
So far, the request SELECT
like the rest of the page (more than 2 or 3 requests) is executed within the next second more or less. The only part slowing the page down is what you can see in the first part of the magazine. I'm not sure if I can profile the SQLServer because I only have cPanel . This is the only way I know of, if not for something else I could look at.
source to share
First, check your connection string, I've experienced slower times with certain vendors. The best provider I've found for classic asp issqloledb
Second, I would close my SQL object before doing the statement For
on the GetRows () array. You don't want to keep it open unless you have to.
Third, I would use stored procedures. You will get better performance by saving compilation time.
Fourth, I would avoid doing the statement SELECT *
at all costs, instead, I would return ONLY the columns I need. (Your code looks like it only needs 2 columns)
Fifth, I would build indexes on tables taking longer than expected.
If that doesn't solve the problem, I would consider other languages / solutions. Not sure what the dataset looks like, so can't tell if a flat file database should be considered or not.
Also, try this instead of a recordset and see what happens:
Set myR = Server.CreateObject("Adodb.Recordset")
myR.Open "SELECT * FROM CC ORDER BY ccName ASC", aConnection
If myR.RecordCount > 0 Then clientCountries = myR.GetRows()
myR.Close
Set myR = Nothing
If IsArray(myR) Then
For ....
source to share