Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My query takes a lot of time, 22 seconds and above. After 22 seconds, we get more than 900 000 records. We have created indexes on the table which are used in the query:

select * from ContactMaster 
where ContactId in (
    select  ContactId 
    from ListContactMaster 
    where ListMasterID in (816,975,945) 
    and ContactId not in (
        select  ContactId 
        from ListContactMaster 
        where ListMasterID in (0)
    ) and (
        isactive='true' 
        or isactive is null
    )
) 
and isactive = 'true' 
and (
    isdeleted='false' 
    or isdeleted is null
) 
and emailaddress not in (
    select emailaddress 
    from supresscontacts
)
and ContactMaster.ContactId not in (
    select DomineSupress.contactid 
    from DomineSupress 
    where DomineSupress.CampaignId=1186 
)

Indexes:

  • IX_ListContactMaster_ContactId,
  • IX_ListContactMaster_IsActive_ContactId,
  • IX_ListContactMaster_ListMasterID,
  • IX_ListContactMaster_ListMasterID_ContactId,
  • IX_ContactMaster_ContactId_IsActive_IsDeleted

The ContactMaster table has more than 50 fields.

share|improve this question
    
Do you have indexes on DomaineSupress(CampaignId, ContactId) and supresscontacts(EmailAddress)? –  Kenneth Fisher Jul 22 '13 at 11:42
1  
please include EXPLAIN output for your query. Also SHOW CREATE TABLE for your tables would be helpful. –  redguy Jul 22 '13 at 12:00
1  
@redguy - SQL Server doesn't use the EXPLAIN keyword to get the execution plan. swaroop you need to look at the execution plan. See How do I obtain a Query Execution Plan? and upload it somewhere if you want help interpreting it. –  Martin Smith Jul 22 '13 at 13:04
1  
Why do you have IX_ListContactMaster_ListMasterID and IX_ListContactMaster_ListMasterID_ContactId? This seems redundant and wasteful. And if IsActive is a bit, the first two indexes might overlap substantially as well. –  Aaron Bertrand Jul 22 '13 at 17:50
    
You should really include the CREATE TABLE statements for all tables included in your query –  druzin Jul 22 '13 at 19:38
add comment

1 Answer

You should use joins to reduce lookups between tables and to make better using of indexes during query the data. As expecting your query on relational tables I suggest a pattern as follows.

SELECT CM.* from ContactMaster CM 
INNER JOIN ListContactMaster LCM ON CM.ContactId=LCM.ContactId
LEFT JOIN supresscontacts SC ON CM.ContactId=SC.ContactId
LEFT JOIN DomineSupress DS ON CM.ContactId=DS.ContactId
WHERE SC.emailaddress IS NULL AND DS.contactid IS NULL AND DS.CampaignId=1186 AND LCM.ListMasterID IN (816,975,945)
AND LCM.ListMasterID <> 0 AND (LCM.isactive='true' OR LCM.isactive IS NULL)
AND CM.isactive = 'true' AND (CM.isdeleted='false' or CM.isdeleted is null)

Hope it will help you to make your query better.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.