Performance problem related to left join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Performance problem related to left join

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-07-09 : 10:26:28

Hi,
I have a query with a Left Join that looks like this:

select p.* from Person p
left join Awards a on a.AWA_Person_ID = p.Person_ID
where ISNULL(a.coded,0) = 0

What I want are all the Person records, but if there is an Award, I want to exclude the records where a.Coded <> 0. If there is no Award, then include the record.
I'm running into a performance problem related to checking a.Coded for records where there is no a. I hope that makes sense.

Is there a way to say:

...where ISNULL(a.coded,0) = 0 or a doesn't exist

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-09 : 10:47:06

You where condition says thats to pull the records where either A.coded=0 OR A.Coded IS NULL.

--->I want to exclude the records where a.Coded <> 0. If there is no Award, then include the record.
I think the query should be:

select p.*
FROM Person p
left join Awards a ON a.AWA_Person_ID = p.Person_ID AND (a.Coded<>0 OR a.Coded IS NULL)

Cheers
MIK

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-07-09 : 11:11:16

the above doesn't do what I'm looking for.
I made a test with customers and orders.

Customers:
name
fred
pete
bill
tom
harry

Orders:
name coded
harry NULL
tom 1
pete NULL

select c.* from customers c
left join orders o on o.name=c.name and ISNULL(o.coded,0) = 0

The above returns:
fred
pete
bill
tom
harry

I don't want it to return tom, because there is coded=1 for tom.

The following does work but it is EXTREMELY SLOW for cases where there are a lot of records:

select c.* from customers c
left join orders o on o.name=c.name 
where o.coded is null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-09 : 11:30:50

[code]/*
SELECT p.*
FROM dbo.Person AS p
LEFT JOIN dbo.Awards AS a on a.AWA_Person_ID = p.Person_ID
AND a.coded = 0;
*/
DECLARE @Person TABLE
(
PersonID TINYINT NOT NULL
);

INSERT @Person
(
PersonID
)
VALUES (1),
(2),
(3);

DECLARE @Awards TABLE
(
PersonID TINYINT NOT NULL,
Coded TINYINT NOT NULL
);

INSERT @Awards
(
PersonID,
Coded
)
VALUES (1, 1),
(1, 0),
(2, 1);

-- Take 1
SELECT p.*
FROM @Person AS p
LEFT JOIN (
SELECT PersonID
FROM @Awards
GROUP BY PersonID
HAVING MAX(CASE WHEN Coded = 0 THEN 1 ELSE 0 END) = 0
) AS a ON a.PersonID = p.PersonID
WHERE a.PersonID IS NULL;

-- Take 2
SELECT p.*
FROM @Person AS p
WHERE NOT EXISTS (SELECT * FROM @Awards AS a WHERE a.PersonID = p.PersonID AND a.Coded <> 0);[/code]

N 56°04'39.26"
E 12°55'05.63"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-09 : 11:34:06

I don't know if this will offer any better performance, but you could try the following. If that does not help, look at the execution plan (control-m in SSMS to turn on execution plan) and focus on the steps that are consuming the resources. Also, look at any missing indexes that the query plan shows (but don't rely on it exclusively - do your own analysis to see if that makes sense, and to see if it is cost-effective to add an index specifically for this query)

SELECT
	c.*
FROM
	Customers c
	LEFT JOIN orders o ON
		o.NAME = c.name 
WHERE
	o.NAME IS NULL OR o.coded = 0 OR o.coded IS NULL;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources