Looping through database query to send emails is taking too long

htmanning has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Looping through database query to send emails is taking too long by GrandFather (Saint) on Jul 21, 2015 at 07:13 UTC
10 seconds is far too long for a database lookup on 100 records. It also sounds like too long to send a modest number of emails. Have you tried running the script off line to check that it really takes as long as you are reporting? If the script does take as long as reported you should put together a sample script (see I know what I mean. Why don't you? for some hints) and we'll help speed it up for you. If it turns out that the processing time is unavoidable you can always fork off a task to send the emails and complete the http transaction without waiting. See replies to Managing a long running server side process using CGI for ways to do that. Premature optimization is the root of all job security	[reply]
Re^2: Looping through database query to send emails is taking too long by afoken (Chancellor) on Jul 21, 2015 at 08:30 UTC
10 seconds is far too long for a database lookup on 100 records. Yes. Maybe missing indexes or missing primary key? htmanning, please show the `select` statement and the table definition(s) (`creat`e statements). Another problem may be the mailserver. Internal mail servers are usually very generous, unlike public mail servers. On the latter ones, intentional delays are quite usual to defend against spam bots. The usual way around this problem is to talk with the mail server admin, (s)he will know which mailserver to use, and how to login there, if needed. Adding some debug output with timestamps to the program should show very fast which part of the program is slow. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re: Looping through database query to send emails is taking too long by vinoth.ree (Monsignor) on Jul 21, 2015 at 05:30 UTC
Hi, I can smell like you are using web based application, which calls a perl script in the background when you click the send button? Are you using Perl CGI? What database you are using? Is your table has only 74 records? If there are more records, did you check your query takes time to fetch the records? or Table has index ? You can show us your script so that we can help you better. *All is well. I learn by answering your questions...*	[reply]
Re: Looping through database query to send emails is taking too long by Laurent_R (Canon) on Jul 21, 2015 at 09:25 UTC
If the mail server is delaying your process (and I know this to happen in some mailbox settings), maybe you could just build the final list of addressees while your user is waiting, and do the actual sending in the background afterwards.	[reply]
Re: Looping through database query to send emails is taking too long by BrowserUk (Patriarch) on Jul 21, 2015 at 05:32 UTC
it takes about 10 seconds or longer That doesn't seem to be such a long time to ask your users to wait? With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday' Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. I'm with torvalds on this Agile (and TDD) debunked I told'em LLVM was the way to go. But did they listen!	[reply]
Re^2: Looping through database query to send emails is taking too long by htmanning (Friar) on Jul 21, 2015 at 05:49 UTC
I wouldn't mind them waiting if I could provide a "please wait" or appropriate graphic. When they click SEND, the script takes 10 seconds to cycle through the database. It's a MYSQL database with maybe 100 records. The script loops through and sends an email to records that have notifications set to yes BTW, I just thought of something. Maybe my code is off. I have 2 email fields in each database records, (email, email2). Each email field can have a notification set or not. I'm looping through twice, maybe that's the problem. `$SQL = "select * from $resident_info_table where email!='' and email_a +lert = 'yes' order by ID desc"; &Do_SQL; while ($pointer=$sth->fetchrow_hashref) { $recipient = $pointer->{'email'}; &send_email; } $SQL2 = "select * from $resident_info_table where email2!='' and email +2_alert = 'yes' order by ID desc"; &Do_SQL2; while ($pointer2=$sth2->fetchrow_hashref) { $recipient = $pointer2->{'email'}; &send_email; }` [download] Is there a way to do both queries in one sql search?	[reply] [d/l]
Re^3: Looping through database query to send emails is taking too long by BrowserUk (Patriarch) on Jul 21, 2015 at 05:52 UTC
I wouldn't mind them waiting if I could provide a "please wait" or appropriate graphic. When they click SEND, Why not add: `[SEND] Note: this will take upto 30 seconds. Please be patient.` [download] With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday' Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. I'm with torvalds on this Agile (and TDD) debunked I told'em LLVM was the way to go. But did they listen!	[reply] [d/l]
Re^3: Looping through database query to send emails is taking too long by vinoth.ree (Monsignor) on Jul 21, 2015 at 05:53 UTC
Hmmm, then you can show some progress bar on your web application, after user clicks SEND button. *All is well. I learn by answering your questions...*	[reply] [d/l]
Re^4: Looping through database query to send emails is taking too long by htmanning (Friar) on Jul 21, 2015 at 06:07 UTC
Re^5: Looping through database query to send emails is taking too long by Anonymous Monk on Jul 21, 2015 at 07:03 UTC
Re: Looping through database query to send emails is taking too long by flexvault (Monsignor) on Jul 21, 2015 at 15:35 UTC
htmanning, With most of my cgi scripts, I make them schedule work, rather than complete the work. In your case, you could open and lock a scheduling file in append ('>>') mode and then put(print) the 'Todo' string in the scheduling file and unlock it (close). Then immediately complete the transaction for the user with a HTML page with related information. If your web-server is Apache2 on nix, then the tricky part is to have a Perl script running under the same user and group as Apache2, that does the actual work. Other web-servers may allow you to schedule another Perl script, but Apache2 doesn't without waiting for all scripts to complete, which will make the process slow again :-( You look like a hero to your users for being so fast, and you can expand the functionality of the 'Todo' script without worrying about the time to do the actual work. You can get into client/server communication, but using an external file is very fast and simple to implementation. If for some reason the transaction fails, you know the exact parameters needed to re-schedule the task. Hope this gives you some ideas for improving your users' experience! Regards...Ed "Well done is better than well said." - Benjamin Franklin*	[reply]
Re: Looping through database query to send emails is taking too long by tangent (Parson) on Jul 21, 2015 at 13:16 UTC
If this is a web page then you could send the request via AJAX and update the page with a bit of Javascript.	[reply]