Hi,

Ok, a little bit more information. I looked at the Scheduler History, and I noticed that it's trying to send an e-mail out that I sent out a couple of months ago. When I look at the report for that email, it shows it as completed.

Hi... Hmmm that is interesting. I would probably set the schedule to be hourly instead of every 10 minutes. I am not sure why it's still stuck on that newsletter but you might just try deleting it.

Thanks,

Chad

Thanks Chad! That worked.

Hi Chad,

We start getting the same SQL timeout error when we've sent emails to about 14K out of 22K users. This has happened with a couple of emails we've sent. With the first email, we reindexed various tables, including DataSprings_OptInEmailPreviousDistribution, and that seemed to enable the scheduler to restart sending the rest. But with our second email, it happened again. We tried reindexing that table again, but to no avail. Should we try and purge some of the historical data there? There are >300K rows in that table right now.

Thanks in advance.

Norman

Hi Norman - It is probably not a bad idea to truncate that history table from time to time but I am not directly sure this is impacting and causing the timeout error now.

Can you tell me approx how many you are sending at a time? For example, are you sending 500 every hour or 1000 every hour etc? Just curious so we can start to compare performance issues.


Thanks,

Chad

Chad,

I'm following up on this for Norman. First some general information, we have around 22K rows in our DataSprings_OptinInEmail table, and we had around 380K rows in our DataSprings_OptInEmailPreviousDistribution table. We're using SQL Server 2008 R2 Express. We are sending 1000 emails every 5 minutes right now. Things run fine for a while, but after roughly 14K emails sent, we have several times ended up running into the timeout issue. I did remove a number of rows from DataSprings_OptInEmailPreviousDistribution to reduce the table down to about 180K rows, and this seemed to make the problem go away.

In hopes of solving the timeout issues, I had first looked over the DataSprings specific tables, and noted that several of the tables did not have indexes on the columns that were foreign keys. I added indexes to the OptInEmailID and PreviousNewsletterID columns of DataSprints_OptInEmailPreviousDistribution. I also added an index on the OptInEmailID column of DataSprings_OptInEmailCategory table. This seemed to help a bit with a prior email send, but we ended up still having problems with a more recent newsletter send.

Just guessing, it seems like the problems probably arise from the stored procedure DataSprings_OptInEmail_GetPendingOptInPreviousDistribution. I did a test of the query plan, and it also recommended including the OptInEmailID as an included column in my index of PreviousNewsletterID on the DataSprints_OptInEmailPreviousDistribution table.

Looking at the query itself in this stored procedure:

SELECT *
FROM easycgi_dnn_DataSprings_OptInEmail
INNER JOIN easycgi_dnn_DataSprings_OptInEmailCategory
ON easycgi_dnn_DataSprings_OptInEmailCategory.OptInEmailID = easycgi_dnn_DataSprings_OptInEmail.OptInEmailID
WHERE easycgi_dnn_DataSprings_OptInEmailCategory.OptInCategoryID = @optinCategoryId
AND easycgi_dnn_DataSprings_OptInEmailCategory.InActive = 0
AND easycgi_dnn_DataSprings_OptInEmail.InActive = 0
AND easycgi_dnn_DataSprings_OptInEmail.IsVerified = -1
AND easycgi_dnn_DataSprings_OptInEmailCategory.OptIn = -1
AND easycgi_dnn_DataSprings_OptInEmailCategory.OptInEmailID NOT IN
(SELECT easycgi_dnn_DataSprings_OptInEmailPreviousDistribution.OptInEmailID FROM easycgi_dnn_DataSprings_OptInEmailPreviousDistribution
WHERE easycgi_dnn_DataSprings_OptInEmailPreviousDistribution.[PreviousNewsletterID] = @previousNewsletterID)

The likely slow down is the NOT IN clause at the end of the query. One alternate method that should improve performance in this case would be to create a NOT EXISTS clause instead of NOT IN. It could be done something like this:

SELECT *
FROM easycgi_dnn_DataSprings_OptInEmail
INNER JOIN easycgi_dnn_DataSprings_OptInEmailCategory
ON easycgi_dnn_DataSprings_OptInEmailCategory.OptInEmailID = easycgi_dnn_DataSprings_OptInEmail.OptInEmailID
WHERE easycgi_dnn_DataSprings_OptInEmailCategory.OptInCategoryID = @optinCategoryId
AND easycgi_dnn_DataSprings_OptInEmailCategory.InActive = 0
AND easycgi_dnn_DataSprings_OptInEmail.InActive = 0
AND easycgi_dnn_DataSprings_OptInEmail.IsVerified = -1
AND easycgi_dnn_DataSprings_OptInEmailCategory.OptIn = -1
AND NOT EXISTS
(SELECT easycgi_dnn_DataSprings_OptInEmailPreviousDistribution.OptInEmailID FROM easycgi_dnn_DataSprings_OptInEmailPreviousDistribution
WHERE easycgi_dnn_DataSprings_OptInEmailPreviousDistribution.[PreviousNewsletterID] = @previousNewsletterID
and easycgi_dnn_DataSprings_OptInEmailPreviousDistribution.OptInEmailID = easycgi_dnn_DataSprings_OptInEmailCategory.OptInEmailID)

I ran a few queries, and this does include the same results as the first query in the stored procedure. Since the not exists clause doesn't require as much IO to load all the OptInEmailIDs from the query, its a bit faster. The query plan seems to confirm this.

Anyways, I just wanted to confirm my analysis of the issues, and see if I'm looking at things in the right way, and also see if there's any other suggested ways to help improve the module's performance with large email lists.

Thanks,
Steve Wranovsky

Hi Steve - I greatly appreciate you taking the time to provide this detailed feedback. I agree with your analysis and I think this is a great enhancement/fix for performance issues.

I have added this into our JIRA bug / enhancement tracking system and we will add this to the next patch or hot fix.

For future reference you can review this here:
https://datasprings.atlassian.net/browse/OIE-6


Thanks!

-Chad

Chad,

Thanks for followup up and tracking the issue. We look forward to an "official" update with the changes, if you end up confirming them.

Steve