Most efficient way of not selecting data

Question

Most efficient way of not selecting data

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

webskater - Wednesday, May 2, 2018 12:24 PM
CREATE TABLE #tblContacts ( ContactID int, Contact varchar(100) ) GO INSERT INTO #tblContacts (ContactID, Contact) SELECT 1, 'Fred' UNION ALL SELECT 2, 'Jim' UNION ALL SELECT 3, 'Arthur' GO CREATE TABLE #tblEmails ( EmailID int, ContactID int, EmailAddress varchar(100) ) GO INSERT INTO #tblEmails (EmailID, ContactID, EmailAddress) SELECT 1, 1, 'fred@someorg.cam' UNION ALL SELECT 2, 2, 'jim@goggle.cam' UNION ALL SELECT 3, 3, 'Arthur@giggle.cam' CREATE TABLE #tblNoEmail ( EmailAddress varchar(100) ) INSERT INTO #tblNoEmail(EmailAddress) SELECT 'jim@goggle.cam' GO SELECT * FROM #tblContacts INNER JOIN #tblEmails On #tblContacts.ContactID = #tblEmails.ContactID WHERE #tblEmails.EmailAddress NOT IN (SELECT EmailAddress FROM #tblNoEmail) GO DROP TABLE #tblContacts DROP TABLE #tblEmails DROP TABLE #tblNoEmail
Jim has told us he does not want to be contacted by us but, for historical reasons, his contact record cannot be deleted. There are many dozens of places within a number of applications used by a number of different teams that can send an email to a list of people. I need to make sure no email is ever sent to his email address. I can't simply put a flag on the #tblEmails for reasons I won't bore you with. Apart from those reasons, I have been told to create #tblNoEmail and maintain a list of email addresses that must never be contacted. Whenever any email is sent from any of our applications (and there are about 20 teams using their own project planning systems over the same database) - I have to check any email address we are about to send an email to is not in 'tblNoEmail'
Something like ....
SELECT ContactID, Contact, EmailAddress FROM #tblContacts INNER JOIN #tblEmails On #tblContacts.ContactID = #tblEmails.ContactID WHERE #tblEmails.EmailAddress NOT IN (SELECT EmailAddress FROM #tblNoEmail)
should do it (although there is a syntax error in my code above that I can't find and that statement above will not run for some reason ? ). But, given there are 200,000 contact records, about 500,000 records in #tblEmails and will be a few thousand in #tblNoEmail - what's the most efficient way of always making sure that any email in a list of email addresses I am about to send an email to will not contain any of the email addresses in #tblNoEmail

FYI this is called a suppression list.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 2

Sergiy - Friday, May 4, 2018 4:26 AM
Have a DistributionList:ContactIdMediaId (lookup for email, post address, phone, messangers, etc.)(?) ExpiryDate

We have hundreds of them. Based on team and category. But, under these new GDPR rules, we have to make sure that anyone who contacts us and says 'don't contact me again' must never receive an email from us again. In a sense the NoEmail is a master 'don't contact me' list.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Just remove the record from the distribution list.

Keep all the contact details intact.

_____________
Code for TallyGenerator

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

ChrisM@Work - Friday, May 4, 2018 4:37 AM
SQL Server will apply filters and joins in whatever order is calculated to be most efficient. In the example above, moving the filter from the where clause to the join is unlikely to change the execution plan.

I believe that there's a LEFT missing on the JOIN. Moving the filter will change the results when an OUTER JOIN is used (I'm sure you know that, but I just wanted to keep it clear).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Sergiy - Friday, May 4, 2018 7:45 AM
Just remove the record from the distribution list.Keep all the contact details intact.

Sergiy - when I've worked with mailing companies, it's been standard practice to use suppression files on any (mailing)list, partly because there are multiple types of suppressions, partly because the consequences can be expensive if people are mailed when they've opted out.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

I would lean towards putting the PK and the email in the #NoEMail list. Then I'd put a token in the main list, as mentioned previously, because someone or a new process might look to email everyone on the list without checking #NoEmail.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

> been standard practice to use suppression files on any (mailing)list, partly because there are multiple types of suppressions, partly because the consequences can be expensive if people are mailed when they've opted out.

It's actually serves the opposite.

I've seen an actual occasion when an overnight process of building the suppression list failed (blown up the tempdb) and early morning emails went out with no suppressions applied at all.

Think not "all excluding suppressed", think "all that must be emailed".

_____________
Code for TallyGenerator

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 8

Sergiy - Friday, May 4, 2018 3:04 PM
> been standard practice to use suppression files on any (mailing)list, partly because there are multiple types of suppressions, partly because the consequences can be expensive if people are mailed when they've opted out.It's actually serves the opposite.I've seen an actual occasion when an overnight process of building the suppression list failed (blown up the tempdb) and early morning emails went out with no suppressions applied at all.Think not "all excluding suppressed", think "all that must be emailed".

Well usually you would do both, generate the list you want to send out, them compare it to anything that definitely shouldn't be sent. If that process is blowing something up then that needs to be fixed.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

No, I'd usually compare to what should not be sent WHILE generating the list to be sent.

And those which not to be sent never appear in the list to be sent.

So, no mistake in the emailing procedure can send emails to somebody who's not supposed to receive it.

Isn't it a bit safer that relying on suppression lists?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 10

There is a fundamental error in the design described in the op.

Every customer with an email address is considered a recipient of emails.

Except those ones which are excluded.

Having an email registered in the database should not mean receiving an email.

To receive emails a customer's email must be registered in a certain distribution list.

Which is populated according to whatever business rules are in place: "All except opted out", "having no activity last month", etc.

And population/modification of such list(s) happens asynchronously, on it's own time, so the emailing procedure can do what it's meant to do - sending out emails, not working out multiple confusing business rules for the list of recipients.

_____________
Code for TallyGenerator

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 11

I'd agree with Sergiy here. We always used a generation process to produce a list here, so that changes, exceptions, etc. could be applied. We never used the original table as the source for actual sending.