Assistance needed on Optimizing a view in SQL Server 2012

Question

I am running SQL Server 2012 and have constructed a query to minus results one primary from another. I have done this a few ways one being:

SELECT 
    campaignContact_id,
    nlLogID,
    emailAddress
FROM 
    sm_rel_campaign_contact rcc  
WHERE
    rcc.campaignContact_id NOT IN (SELECT campaignContact_id 
                                   FROM SM_BOUNCES) 

    AND rcc.campaigncontact_id NOT IN (SELECT campaignContact_id 
                                       FROM SM_DEFERRALS ) 
    AND rcc.campaignContact_id NOT IN (SELECT campaignContact_id 
                                       FROM SM_FAILS)
    AND rcc.campaignContact_id NOT IN (SELECT campaignContact_id 
                                       FROM SM_SENDS )

Another being :

ALTER VIEW SM_QUEUE
AS
(
   SELECT 
      campaignContact_id,
      nlLogID,
      emailAddress
   FROM 
      sm_rel_campaign_contact rcc 
   WHERE 
      NOT EXISTS (SELECT * FROM SM_BOUNCES smb 
                  WHERE rcc.campaignContact_id = smb.campaignContact_id) 
      AND NOT EXISTS (SELECT * FROM SM_DEFERRALS smd 
                      WHERE rcc.campaignContact_id = smd.campaignContact_id)
      AND NOT EXISTS (SELECT * FROM SM_FAILS smf 
                      WHERE rcc.campaignContact_id = smf.campaignContact_id)
      AND NOT EXISTS (SELECT * FROM SM_SENDS sms 
                      WHERE rcc.campaignContact_id = sms.campaignContact_ID)
)

The issue is when I run this guy after I create the view (either way):

SELECT count(*) 
FROM SM_QUEUE 
WHERE nlLogID = 81505

it's running incredibly slow! I know you can index views but I wanted to see if anyone had a better suggestion? LEFT OUTER JOIN's maybe?

Appreciate any feedback in advance.

score 2 · Answer 1 · edited May 23 '17 at 11:56

You won't be able to index this view - sub queries or outer joins make this unindexable.

Probably you are missing some useful indexes on the base tables though.

A possible different approach that may perform better is

WITH Excludes
     AS (SELECT campaignContact_id
         FROM   SM_BOUNCES
         UNION ALL
         SELECT campaignContact_id
         FROM   SM_DEFERRALS
         UNION ALL
         SELECT campaignContact_id
         FROM   SM_FAILS
         UNION ALL
         SELECT campaignContact_id
         FROM   SM_SENDS)
SELECT campaignContact_id,
       nlLogID,
       emailAddress
FROM   sm_rel_campaign_contact rcc
WHERE  NOT EXISTS (SELECT *
                   FROM   Excludes e
                   WHERE  e.campaignContact_id = rcc.campaignContact_id)

If that doesn't help edit your question and include the CREATE TABLE statements including indexes and details of sizes of the tables involved.

I thought about using CTE for this @Martin Smith. I will give it a go. Also yes casting the keys to be exact may also be a good way to go. thank you for the suggestion! — SRahmani, Feb 27 '15 at 19:07

score 0 · Answer 2 · answered Feb 27 '15 at 19:18

0

'not in' cannot typically be optimzed (you end up with full table scans) but not exists can be optimized by the query analyzer. See if you can change the not in to a not exist in the first query.

answered Feb 27 '15 at 19:18

benjamin moskovits

5,261
1
12
22

both run about the same speed(as you can see from my above statement). These are very large tables and I would venture to say I can cast some fields to proper data types and also match -convert or cast the primary key if need be. thanks for the suggestion. – SRahmani Feb 27 '15 at 19:27

score 0 · Answer 3 · answered Feb 27 '15 at 19:29

cant really try it since sql fiddle is down and i have no sql server till monday (maybe you need a few more parentheses):

WITH cte AS (
  SELECT campaignContact_id FROM   sm_rel_campaign_contact rcc
  except
  SELECT campaignContact_id FROM   SM_BOUNCES
  except
  SELECT campaignContact_id FROM   SM_DEFERRALS
  except
  SELECT campaignContact_id FROM   SM_FAILS
  except
  SELECT campaignContact_id FROM   SM_SENDS)
SELECT *
from cte 
join sm_rel_campaign_contact rcc on rcc.campaignContact_id = cte.campaignContact_id

score 0 · Answer 4 · answered Mar 01 '15 at 14:36

I would expect the second version (with the WHERE NOT EXISTS()) to run quite a bit faster than the one with the WHERE NOT IN () construction. However, this requires that you have an index on the campaignContact_id field in each of the referenced tables (SM_BOUNCES, SM_DEFERRALS,SM_FAILS & SM_SENDS). Additionally, since you say these are big tables, do you have an index on the nlLogID field to start with? It might well be that your query spends more time scanning the sm_rel_campaign_contact table than figuring out if it has relevant data in the other tables; especially since you say both versions in the question run 'about the same'. TIP: try running the query with the 'Show Execution Plan' and interpret what's going on there, or simply take a screenshot and attach it to your question above and see if we can get any insights from it.

Assistance needed on Optimizing a view in SQL Server 2012

4 Answers4