CODEWITHSUNDEEP
asp-classic
smartins
smartins

Reputation: 3888

asp loop hangs web site while running

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.

Upvotes: 0

Views: 974

Answers (4)

Martin Brown
Martin Brown

Reputation: 25310

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

Upvotes: 2

AnthonyWJones
AnthonyWJones

Reputation: 189457

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.

Upvotes: 2

Dmitry Khalatov
Dmitry Khalatov

Reputation: 4369

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

Upvotes: 1

GalacticCowboy
GalacticCowboy

Reputation: 11759

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.

Upvotes: 0

Related Questions