Query Optimization Assistance w/ Joins

[Kenneth Courville]

I have a couple of tables that look like this (not excactly but close
enough):

[Contact]
id int
fname varchar(50)
lname varchar(50)

[ContactPhoneNum ber]
id int
number varchar(15)
ext varchar(6)
contact_id int
priority int (indicates primary, secondary... numbers)
type int (indicates type of number: fax, cell, land line)

I'm looking for a more optimized method of displaying this information
in this format:

fname, primary business phone

Using a derived column like this works, but seems to be slow with many
records, despite tuning indexes:

SELECT c.fname AS [First Name],
( SELECT TOP 1
number
FROM ContactPhoneNum ber cpn
WHERE cpn.type = 1
AND cpn.contact_id = c.id
ORDER BY cpn.priority) AS Number
FROM Contact c

I can get the same results using a join, and it's a lot faster. But I'm
not sure how to select only the primary phone number this way...
basically the first phone number whose priority is either NULL or 1.

Any suggestions?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ross Presser]

Re: Query Optimization Assistance w/ Joins

On 12 Jul 2004 16:05:51 GMT, Kenneth Courville wrote:

[snip]
[color=blue]
> I can get the same results using a join, and it's a lot faster. But I'm
> not sure how to select only the primary phone number this way...
> basically the first phone number whose priority is either NULL or 1.[/color]

Select TOP 1 c.fname AS [First Name], cpn.Number as Number
FROM Contact c
INNER JOIN ContactPhoneNum ber cpn
ON cpn.contact_id = c.id
WHERE cpn.type=1 AND (cpn.priority = 1 OR cpn.priority IS NULL)
ORDER BY cpn.ID

Since you didn't specify what "first" means if there are multiple phone
numbers matching (priority=1 or priority is null), I took the hint that
cpn.ID might be an autonumber, meaning that lower numbers mean entered into
the table earlier, so the lowest ID would be the first.

[Kenneth Courville]

Re: Query Optimization Assistance w/ Joins

Yes... "first" means if there are multiple phone
numbers matching (priority=1 or priority is null)... then just return
the top one in the list... no matter what the order is... the ORDER BY
cpn.id isn't necessary.

I guess the other thing I should've mentioned is that I'm displaying
contacts that are assigned to a particular client location.

The problem with your modifications is it would only return the first
contact.... whereas the output can be:

First Name Number
John 555-1234
Sally 555-7891
Jill 555-9713

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Query Optimization Assistance w/ Joins

Kenneth Courville (krcourville@-nospam-msn.com) writes:[color=blue]
> Using a derived column like this works, but seems to be slow with many
> records, despite tuning indexes:
>
> SELECT c.fname AS [First Name],
> ( SELECT TOP 1
> number
> FROM ContactPhoneNum ber cpn
> WHERE cpn.type = 1
> AND cpn.contact_id = c.id
> ORDER BY cpn.priority) AS Number
> FROM Contact c
>
> I can get the same results using a join, and it's a lot faster. But I'm
> not sure how to select only the primary phone number this way...
> basically the first phone number whose priority is either NULL or 1.[/color]

SELECT c.fname AS "First Name", cpn.Number
FROM Contact c
JOIN (SELECT contact_id, MIN(Number)
FROM ContactPhoneNum ber a
JOIN (SELECT contact_id, priority = MIN(priority)
FROM ContactPhoneNum ber
WHERE type = 1
GROUP BY contact_id) AS b
ON a.contact_id = b.contact_id
AND a.priority = b.priority
-- (OR a.priority IS NULL AND b.priority IS NULL)
WHERE a.type = 1
GROUP BY contact_id) AS cpn
ON c.id = cpn.contact_id

I think this will cut it, but with CREATE TABLE statements and INSERT
statements with sample data, it is difficult to test. (Hint, hint!)

If priority can be NULL, you should uncomment the commented line.
I have assumed that two numbers can have equal priority.

Here I am using a derived table, actually even two. I have found that in
many cases this gives better performance than correlated subqueries in the
SELECT list.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ross Presser]

Re: Query Optimization Assistance w/ Joins

On 12 Jul 2004 19:15:51 GMT, Kenneth Courville wrote:
[color=blue]
> Yes... "first" means if there are multiple phone
> numbers matching (priority=1 or priority is null)... then just return
> the top one in the list... no matter what the order is... the ORDER BY
> cpn.id isn't necessary.
>
> I guess the other thing I should've mentioned is that I'm displaying
> contacts that are assigned to a particular client location.
>
> The problem with your modifications is it would only return the first
> contact.... whereas the output can be:
>
> First Name Number
> John 555-1234
> Sally 555-7891
> Jill 555-9713
>[/color]
Ah. My brain appears to have conflated your question with someone else's.
Sorry.

How about this:

Select c.fname AS [First Name], Min(cpn.Number) as Number
FROM Contact c
INNER JOIN ContactPhoneNum ber cpn
ON cpn.contact_id = c.id
WHERE cpn.type=1 AND (cpn.priority = 1 OR cpn.priority IS NULL)
GROUP BY c.ID, c.Fname

I know you said you explicitly want the "first" one, but since the order
isn't well defined, SQL server is free to return them in any random order
it wishes ... which means that the "first" one won't necessarily be the
same twice in a row. So maybe you can get by with Min?

[Kenneth Courville]

Re: Query Optimization Assistance w/ Joins

duh.. This should help.

Hadn't thought of a derived table.... still kind of new to them.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!