SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Performance problem related to left join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaveBF
Yak Posting Veteran

84 Posts

Posted - 07/09/2013 :  10:26:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/09/2013 :  10:47:06  Show Profile  Reply with Quote
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
Go to Top of Page

DaveBF
Yak Posting Veteran

84 Posts

Posted - 07/09/2013 :  11:11:16  Show Profile  Reply with Quote
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

Edited by - DaveBF on 07/09/2013 11:14:22
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/09/2013 :  11:30:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
/*
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);



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 07/09/2013 :  11:34:06  Show Profile  Reply with Quote
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;		
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000