Asp loop hangs on website while running
I have a loop on a page to refresh the access database that takes 15-20 seconds to complete. I only run it once a month, but I noticed that every time I run it, the website (IIS 6) just stops showing the pages.
After the end of the cycle, the pages begin to open again.
Here's my code:
For each Email in Emails
if Trim(Email) <> "" then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_Customers_STRING
MM_editCmd.CommandText = "UPDATE Customers SET MailingListUpdates=False WHERE Email='" & Trim(Email) & "'"
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
Response.Write "Email address " & Email & " successfully removed from the mailing list.<br>"
end if
Next
Is there anything I can do to avoid this?
The last update emails were about 700 entries.
source to share
You are probably using all available connections in the connection pool. Try this instead:
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_Customers_STRING
For each Email in Emails
if Trim(Email) <> "" then
' execute the update
MM_editCmd.CommandText = "UPDATE Customers SET MailingListUpdates=False WHERE Email='" & Trim(Email) & "'"
MM_editCmd.Execute
Response.Write "Email address " & Email & " successfully removed from the mailing list.<br>"
end if
Next
MM_editCmd.ActiveConnection.Close
Also, as a longer term task, try switching to SQL Server Express
source to share
MM_ ASP programming. Creating a command object and opening and closing a connection in each iteration may not be good for performance. String concatenation to create a new command line every time is not good for security.
Try this: -
Dim con : Set con = Server.CreateObject("ADODB.Connection")
Dim cmd : Set cmd = Server.CreateObject("ADODB.Command")
con.Open MM_Customers_STRING
Set cmd.ActiveConnection = con
cmd.CommandType = 1 // adCmdText (note SO not good at detecting VB comment)
cmd.CommandText = "UPDATE Customers SET MailingListUpdates=False WHERE Email=?"
Dim param : Set param = cmd.CreateParameter("email", 200, 1, 50) // adVarChar, adParamInput, size: 50
cmd.Parameters.Append param
Dim Email
For Each Email in Emails
Email = Trim(Email)
If Email <> "" Then
param.value = Email
cmd.Execute
End If
Next
con.Close
An index on the email box would be nice.
source to share
What is the source for the Email collection? If it's from your database, you'll get much better performance by leaving it there and attaching to it, instead of fetching and iterating over programmatically. An additional improvement would only be to update if needed, that is, the MailingListUpdates attribute is no longer false.
source to share