Clever Geek Handbook

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.

0


source to share


4 answers


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

+2


source


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.

+2


source


I suggest taking command creation / deletion and settings from the loop and using bind variables (via a parameter set).

+1


source


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.

0


source






More articles:


All Articles