website users have to wait to long for a function to complete

[michael - ohio]

I have an ASP webpage which, on form submit,

1. saves some data to the MySQL DB,
2. reloads the page
3. calls a function which runs a query (which sends out a bunch of personalized, individual emails)
and when the function completes
4. redirects to a different page

<%
call openDB

idUser = session("idUser")

if not request.form("title")="" then
	executeSQL "INSERT INTO Postings (idUser,expDate,`title`,`description`,`datePosted`) VALUES (" _
		& idUser & ",'" & request("expDate") & " 10:00:00',""" & validSQL(request("title"),"S") & """,""" & validSQL(replace(request("description"),vbcrlf,"<br>"),"S") & """,now())"

	idPosting = getRecordSet("SELECT @@IDENTITY as id")("id")

call sendNewJob(idUser,idPosting,15)
response.redirect "view_jobs.asp?id=" & iduser

end if

%>

The problem is that the function can take quite awhile to complete before returning to the asp page for the response.redirect. (at 1 second per email it can be 5 minutes(!) to send 300 individual emails)

I can't have my wesbite users wondering what's going on before being redirected - and I don't want them to have sit watching a "wait/progress" screen...

Since ASP is not a multi-threading language, is there a way to break this up using javascript or some other voodoo so that my the ASP page does steps 1,2 and 3 and then have users sent on to the redirect while the server happily goes about its busuness of completing the function?

(Prefer NOT to change the page from ASP to PHP at this time, if possible)

Here's the function that's called.
Note that it is using variables from the ASP page...

	function sendNewJob(idUser,idPosting,radius)
		dim msgBody,oSitters,oCollection

		set oSitters = getSittersRSForJobPost(getRecordSet("SELECT Latitude FROM Users WHERE idUser=" & idUser)("Latitude"),getRecordSet("SELECT Longitude FROM Users WHERE idUser=" & idUser)("Longitude"),radius,false,0)

		set oCollection = server.createobject("Scripting.Dictionary")

		do while not oSitters.EOF

			if len(oSitters("email")) > 0 then

				if not oCollection.exists(replace(oSitters("email"),"@","+")) then

					oCollection.add replace(oSitters("email"),"@","+"), 1

					msgBody = msgHeader & _
						"text of email here" & msgFooter
					SendCDOSYS oSitters("email"),salesEmailAddress,"subject of email here",msgBody
					'response.write oSitters("email") & "<br>"
				end if

			end if

			oSitters.MoveNext
		loop
		set oCollection = nothing
		set oSitters = nothing
	end function

Thanks,

- michael

[webber123456]

there's nothing built into ASP for mass mailings. You'll likely do well to get a third party solution like

http://www.e-mail-soft.com/cet/newsl...ail-sender.htm

[michael - ohio]

If it were a mass mailing (the same email sent to a lot of email addresses) it would be easy - I'd use bcc. But this is one email sent to one email address, repeated in a for/each loop until eof (the end of the email list).

Thanks though - I'll look at the link you posted. In the meantime, I'm still trying to find a way to do this through coding... I refuse to believe it's not possible. If necessary, I'll change the page and function from ASP to a multi-threading enviornment like .Net or PHP... just trying to avoid that if possible due to the use of the already declared variables.

there's nothing built into ASP for mass mailings. You'll likely do well to get a third party solution like

http://www.e-mail-soft.com/cet/newsl...ail-sender.htm

[michael - ohio]

http://www.e-mail-soft.com/cet/newsl...ail-sender.htm

PerfectEmailer is dsigned for managing and using email lists... not for on the fly website server emailing. But thanks anyway.

[webber123456]

It's just an example because there are many others such as ASPmail or any third party component which will enable sending to a Que.

there may even be something in SQL server.

Unfortunately there's nothing in ASP - but check further into CDO or SQL server. beyond that there's the third party components.

[michael - ohio]

Thanks - I will look into those options - but sending the emails is not the problem. The problem is that our function must run up to 500 separate queries and the user has to wait for that to complete before being redicrected to another page.

It doesn't much matter to me how quickly the emails are sent - what matters is finding a way to call the query/send email function loop and have the page redirect immediately.

One option I am considering is to complete step 1 (save the data to the database) and do the redirect... and then have the server run a chron type event on a regular basis which will call the function when it finds a new entry in the database.

Another option is to, again, complete step one and then use an event handler to launch the function - or something like that.

- Michael

[webber123456]

check with the folks in the database forum - they may already have solved this problem

[michael - ohio]

here's where we are thus far:

The message to be emailed is currently saved to the DB before anything else happens - so I've added a new field to the record called "emailed" which has a default value of "N" (for no). So now, the message is saved and the user is immediately redirected to the confirmation page w/ithout having to sit around waiting for the emails to be sent by the server.

Now I need a method to trigger the email lfunction.

Since the website is hosted in a MS Windows Server 2003 environment, we can't use a Linux cron job, but we can take a different approach...

We are writing an ASP routine that will be called each time a call to the DB is made (which happens quite frequently). That call will look to the DB for the "N" condition in the record field and when found, it will

1. change the "N" flag to "P" (pending)
2. lock the current record
3. call the email function (which contains the query to get the email addr list, message header, message body and sends the emails)
4. Unlock the record
5. change the "P" flag to "Y" (yes)

Hoping this will work.

[michael - ohio]

still no luck...

There appears to be no way we can have an asp page or function spawn an independent thread.

Currently, when the user submits the form data it is saved to the DB and the 'emailed' flag/field is set to "N".

We still need a way for something to either run on a schedule (like every 15 minutes) or monitor that "N" field in the table and then run a server-side function to execute the query and send the emails.

I hate the thought of having to use Windows Scheduler...

Any help would be greatly appreciated, of course.

[ben_uk]

Windows scheduler is exactly what you need. Set a WSH script to run every 15 minutes that loops through the db records and sends the emails

[michael - ohio]

Windows scheduler is exactly what you need. Set a WSH script to run every 15 minutes that loops through the db records and sends the emails

That's what we've been working on this weekend. We'll test it tomorrow and hopefully that will be the end of this (until we re-write the entire web site to .Net or PHP).

Thanks for the input.

- michael

[ben_uk]

No problem. That's what I do and have never had a problem, windows scheduler is quite reliable on Server 2008 I've found

[michael - ohio]

windows scheduler is quite reliable on Server 2008 I've found

unfortunately, we're using windows server 2003.
makes me nervous.

[DaveMaxwell]

I use a schedule task for scheduled vbscripts on 2003 all the time. Works like a charm. The only hiccup is if there is a vbscript error - the scheduler will lock up the scheduled task.

That being said, that method is working very hard, and I can see if causing a problem if you've got 500 emails to send. Collection scans are very expensive, and in this case not really necessary with properly formed SQL statements..

 function sendNewJob(idUser,idPosting,radius)
  dim msgBody, oSitters, strLatitude, strLongitude
        ' Make one hit to the database instead of multiples...
        strSQL = "SELECT Latitude, Longitude FROM Users WHERE idUser = " & idUser
        Set oSitters = CreateObject("ADODB.Recordset")
            oSitters.Open strSQL, objConn
        If oSitters.BOF And oSitters.EOF Then
            strLatitude = ""  :   strLongitude = ""
        Else
            strLatitude = oSitters(0)  :   strLongitude = oSitters(1)
        End If
        oSitters.Close
 
        ' You'll need your radius calculation for the location portion of the WHERE
        strSQL = "SELECT DISTINCT email" & _
                 " FROM JobPost" & _
                " WHERE Len(Email) > 0 AND -- Location IS WITHIN " & radius & " OF " & strLatituderadius & ", " & strLongitude
            oSitters.Open strSQL, objConn
        If oSitters.BOF And oSitters.EOF Then
            ' Load the recordset into an array, then save the count
            arrRecSet = oSitters.getRows(-1)    :   recCount = UBound(arrRecSet, 2)
        else
            ' No record found, so set count to less than possible.
            recCount = -1
        end if
        oSitters.Close :   set oSitters = nothing
        for i = 0 to recCount
            email = arrRecset(0, i)
            msgBody = msgHeader & "text of email here" & msgFooter
            SendCDOSYS email, salesEmailAddress, "subject of email here", msgBody
        next
 end function

[michael - ohio]

Wow... Dave - Thank you. I've always thought that it would be more efficient to call the DB once and load the results into an array - but that kind of SQL is beyond even my ability to follow the code...
which is why I have someone helping in that department!

I see generally what you're doing here - and I'm sure it will be very helpful.

Thank you,

- Michael

That being said, that method is working very hard, and I can see if causing a problem if you've got 500 emails to send. Collection scans are very expensive, and in this case not really necessary with properly formed SQL statements.

[DaveMaxwell]

No problem - I tried to comment the code as clearly as possible, so hopefully y'all will be able to follow it. The only thing I couldn't cover is the distance calculation, but you should just be able to get that from the getSittersRSForJobPost method (or just alter the method so it brings back the recordset I created with my SQL call).

If you have any questions, post the code and we'll go from there...

[michael - ohio]

[QUOTE=DaveMaxwell;4781868]I use a schedule task for scheduled vbscripts on 2003 all the time. Works like a charm. The only hiccup is if there is a vbscript error - the scheduler will lock up the scheduled task.[/QUOTE]

Hi Dave - quick question from a novice:

We now have the function written and added to the 'include' file, and we've written the VBScript (below) which is in [filename].asp

What is it we need to have windows scheduler call?
Do we write a .bat or .exe file? What would be the proper syntax for the call to run [filename].asp?

Thanks!

- Michael

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="./include/functions-ssl.asp"-->


<%
Call openDB


 call sendPostedJobsAutoServise()
'sendPostedJobs 121,123,15
%>

[michael - ohio]

Found it -

It was as simple as running the scheduler from the Windows control panel and pointing to the .asp file located in the webroot.

- michael

[cemory]

I have done something similar using jQuery and AJAX. I have a button on a HTML page which calls an ASP page to send out 50 emails. If you are not familiar with either technology, I would suggest taking the time to learn them. Between reading two books on jQuery and W3Schools on AJAX it has been well worth the time.

[michael - ohio]

Thanks for the input - we're looking into using jquery or javascript and an AJAX call as a temp fix as we work on converting the whole thing to PHP.