asp loop hangs web site while running

Question

I have a loop on page to update an access database that takes 15-20 seconds to complete. I only run it once a month at most but I noticed that every time I run it the web site (IIS 6) simply stops serving pages.

After the loop ends, pages begin opening 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?

Emails on the last update was around 700 records.

Answer 1

What is the source for the "Emails" collection? If it is from your database, you'd get much better performance by leaving it there and joining against it rather than retrieving and iterating programmatically. An additional improvement would be to only perform the update if it is necessary - i.e., the MailingListUpdates attribute is not already false.

Answer 2

I suggest to take a creation/deletion of Command and setup out of the loop and use bind variables (via Parameters collection).

Answer 3

You are probably using up all the 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 more long term thing try upgrading to SQL Server Express

Answer 4

MM_ the bane ASP programming. Create the command object and opening and closing a connection on every iteration can't be good for performance. Concatenating the string to create a new command string each time isn't good for security either.

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 field would be nice.