Sql Server query perfomance

Question

I have contacts that can be in more than one group and have more than one request. I need to simply get contacts for a specific group that have no specific requests.

How do I improve the performance of this query:

SELECT  top 1 con_name ,
        con_id
FROM    tbl_group_to_contact gc
        INNER JOIN tbl_contact c ON gc.con_id = c.id
WHERE   group_id = '81'
        AND NOT c.id IN ( SELECT    con_id
                          FROM      tbl_request_to_contact
                          WHERE     request_id = '124' )

When I run that query with Explanation plan it shows that this query:

SELECT    con_id
                          FROM      tbl_request_to_contact
                          WHERE     request_id = '124'

is expensive with using an index seek.

 |--Top(TOP EXPRESSION:((1)))
       |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([c].[id]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([gc].[con_id], [Expr1006]) WITH UNORDERED PREFETCH)
            |    |--Clustered Index Scan(OBJECT:([db_newsletter].[dbo].[tbl_group_to_contact].[PK_tbl_group_to_contact_1] AS [gc]),  WHERE:([db_newsletter].[dbo].[tbl_group_to_contact].[group_id] as [gc].[group_id]=(81)) ORDERED FORWARD)
            |    |--Clustered Index Seek(OBJECT:([db_newsletter].[dbo].[tbl_contact].[PK_tbl_contact] AS [c]), SEEK:([c].[id]=[db_newsletter].[dbo].[tbl_group_to_contact].[con_id] as [gc].[con_id]) ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((1)))
                 |--Clustered Index Seek(OBJECT:([db_newsletter].[dbo].[tbl_request_to_contact].[PK_tbl_request_to_contact] AS [cc]), SEEK:([cc].[request_id]=(124)),  WHERE:([db_newsletter].[dbo].[tbl_contact].[id] as [c].[id]=[db_newsletter].[dbo].[tbl_request_to_contact].[con_id] as [cc].[con_id]) ORDERED FORWARD)

Answer 1

Your query is ok, just create the following indexes:

tbl_request_to_contact (request_id, con_id)
tbl_group_to_contact (group_id, con_id)

Since the tables seem to be the link tables, you want to make these composites the primary keys:

ALTER TABLE tbl_request_to_contact ADD CONSTRAINT pk_rc PRIMARY KEY (request_id, con_id)
ALTER TABLE tbl_group_to_contact ADD CONSTRAINT pk_gc (group_id, con_id)

, making sure that request_id and group_id go first.

Also, if your request_id and group_id are integers, pass the integers as the parameters, not strings:

SELECT  con_name, con_id
FROM    tbl_group_to_contact gc
JOIN    tbl_contact c
ON      c.id = gc.con_id
WHERE   group_id = 81
        AND c.id NOT IN
        (
        SELECT  con_id
        FROM    tbl_request_to_contact
        WHERE   request_id = 124
        )

, or an implicit conversion may occur rendering the indexes unusable.

Update:

From your plan I see that you miss the index on tbl_group_to_contact. Full table scan is required to filter the groups.

Create the index:

CREATE UNIQUE INDEX ux_gc ON tbl_group_to_contact (group_id, con_id)

Answer 2

You may want to try running the SQL Server Database Tuning Advisor.

Answer 3

I agree with @Quassnoi with the indexes. Plus you can use a left join to only show users who don't have requests. This usually has better performance than a sub-query.

What is the request_id = '124' for? Other request id's don't matter?

SELECT  con_name ,
        con_id
FROM    tbl_group_to_contact gc
        INNER JOIN tbl_contact c ON gc.con_id = c.id
        LEFT JOIN tbl_request_to_contact rtc ON gc.con_id = rtc.con_id
WHERE   group_id = '81' and rtc.request_id IS NULL