Reputation: 20854
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)
Upvotes: 2
Views: 218
Reputation: 425863
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)
Upvotes: 2
Reputation: 20121
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
Upvotes: 0
Reputation: 185703
You may want to try running the SQL Server Database Tuning Advisor.
Upvotes: 0