Query Efficiency

Tbl_Contact
Contact_ID
FirstName
LastName
Tbl_Contact_Detail
Contact_ID
Question_ID
The combination of Contact_ID and Question_ID is unique in
Tbl_Contact_Detail. There are millions of records in this table. Basically
I need to write an optimized query for returning the Contact_ID, FirstName
and LastName of all contacts that have records in Tbl_Contact_Detail where
Question_ID = 44 and a separate record where Question_ID = 45. What's the
fastest approach?
Thanks a tonThe best approach depends on the right indexing on Tbl_Contact_Detail.
It also probably depends on how what percentage of the contacts have
those questions, and how many questions there are per contact.
Here are some things you could try.
If there are no indexes on Tbl_Contact_Detail this might still perform
reasonably. If there is a clustered index on Question_ID it might
also perform well.
SELECT *
FROM Tbl_Contact as A
JOIN (SELECT Contact_ID
FROM Tbl_Contact_Detail
WHERE Question_ID IN (44, 45)
GROUP BY Contact_ID
HAVING COUNT(distinct Question_ID) = 2) as B
ON A.Contact_ID = B.Contact_ID
This query would not work well unless there is an index on
(Contact_ID, Question_ID), or the reverse. If there are very few
questions per Contact_ID it might not perform too badly if there is
just an index on Contact_ID. In this case non-clustered indexes might
work better than clustered, particularly if Tbl_Contact_Detail has
more columns than were shown.
SELECT *
FROM Tbl_Contact as A
WHERE EXISTS
(SELECT *
FROM Tbl_Contact_Detail as B
WHERE A.Contact_ID = B.Contact_ID
AND B.Question_ID = 44)
AND EXISTS
(SELECT *
FROM Tbl_Contact_Detail as B
WHERE A.Contact_ID = B.Contact_ID
AND B.Question_ID = 45)
Of course the thing to do is run some tests and see how they go.
Roy Harvey
Beacon Falls, CT
On Tue, 25 Sep 2007 16:40:30 -0400, "James" <neg@.tory.com> wrote:
>Tbl_Contact
>Contact_ID
>FirstName
>LastName
>Tbl_Contact_Detail
>Contact_ID
>Question_ID
>The combination of Contact_ID and Question_ID is unique in
>Tbl_Contact_Detail. There are millions of records in this table. Basically
>I need to write an optimized query for returning the Contact_ID, FirstName
>and LastName of all contacts that have records in Tbl_Contact_Detail where
>Question_ID = 44 and a separate record where Question_ID = 45. What's the
>fastest approach?
>Thanks a ton
>|||Hi James
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data. You would need to try each different solution to see if it is
using your indexes and of the performance is ok
try:
SELECT C.Contact_ID, C.FirstName, C.LastName
FROM dbo.tbl_contract C
JOIN dbo.tbl_Contact_Detail D1 ON C.Contact_ID = D1.Contact_ID AND
D1.Question_ID = 44
JOIN dbo.tbl_Contact_Detail D2 ON C.Contact_ID = D2.Contact_ID AND
D2.Question_ID = 45
or
SELECT C.Contact_ID, C.FirstName, C.LastName
FROM dbo.tbl_contract C
JOIN ( SELECT Contact_ID
FROM dbo.tbl_Contact_Detail
WHERE Question_ID = 44
OR Question_ID = 45
GROUP BY Contact_ID
HAVING COUNT(*) = 2 ) D ON C.Contact_ID = D.Contact_ID
John
"James" wrote:
> Tbl_Contact
> Contact_ID
> FirstName
> LastName
> Tbl_Contact_Detail
> Contact_ID
> Question_ID
> The combination of Contact_ID and Question_ID is unique in
> Tbl_Contact_Detail. There are millions of records in this table. Basically
> I need to write an optimized query for returning the Contact_ID, FirstName
> and LastName of all contacts that have records in Tbl_Contact_Detail where
> Question_ID = 44 and a separate record where Question_ID = 45. What's the
> fastest approach?
> Thanks a ton
>
>