2

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)
Improve this question
7
  • Can't do it off-the-cuff, but you may find you can remove the subselect and use an outer join on tbl_request_to_contact combined with a GROUP BY clause and the HAVING qualifier (e.g., HAVING COUNT(request_id) = 0) may help. Commented Feb 11, 2010 at 15:21
  • 1
    In SQL Server, LEFT JOIN performs worse than IN, to say nothing of the GROUP BY / HAVING (which can be easily replaced by mere IS NULL in the WHERE clause). Commented Feb 11, 2010 at 15:24
  • What is the request_id = '124' for? Other request id's don't matter? Commented Feb 11, 2010 at 15:26
  • Could you please post the complete execution plan? Just run SET SHOWPLAN_TEXT ON \n GO \n SELECT … Commented Feb 11, 2010 at 15:28
  • @Quassnoi: You're quite right, in this case if you used GROUP BY, the HAVING COUNT(request_id) = 0 would be better expressed with WHERE request_id IS NULL in the WHERE clause. Interesting about SQL Server and LEFT JOIN vs subselects, do you have a reference for that? Commented Feb 11, 2010 at 15:32

3 Answers 3

Reset to default
2

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Agree on the first index. Care to explain on the second? Would add INCLUDE (con_name)
you know what? i deleted top 1 from select statement and it started work much faster, how can u explain it? my i use another approach for selecting only one row?
@Frank: the second just helps to filter the groups. con_name most probably belongs to tbl_contact, not tbl_group_to_contact.
True, who knows with a doubt from the given query. :-)
0

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

Improve this answer

Comments

0

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
Improve this answer

6 Comments

explainextended.com/2009/09/15/…
i cannot use request_id IS NULL, i need specific request_id
@Quassnoi: regarding your article... is there any way to contact you for a question or two?
@msony: request_id IS NULL applied to the LEFT JOIN does the same as the NOT IN you originally used. However, in SQL Server, NOT IN behaves same or better than LEFT JOIN, so there is no point in rewriting the query.
Quassnoi, now that's pretty interesting.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.