SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Code:
    <%
    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...

    Code:
    	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

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Quote Originally Posted by webber123456 View Post
    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

  4. #4
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webber123456 View Post

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

  5. #5
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    check with the folks in the database forum - they may already have solved this problem

  8. #8
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ben_uk View Post
    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

  12. #12
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem. That's what I do and have never had a problem, windows scheduler is quite reliable on Server 2008 I've found

  13. #13
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ben_uk View Post
    windows scheduler is quite reliable on Server 2008 I've found
    unfortunately, we're using windows server 2003.
    makes me nervous.

  14. #14
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    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..

    Code ASP:
     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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  15. #15
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Quote Originally Posted by DaveMaxwell View Post
    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.

  16. #16
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    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...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  17. #17
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [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

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="./include/functions-ssl.asp"-->
    
    
    <%
    Call openDB
    
    
     call sendPostedJobsAutoServise()
    'sendPostedJobs 121,123,15
    %>

  18. #18
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  19. #19
    SitePoint Enthusiast
    Join Date
    Apr 2010
    Location
    Dana Point, CA
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  20. #20
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •