Trying to Rewrite WHERE IN Query 1

[pwilson]

I am having serious slowness issues for something that should be speedy. Basically I want to find the highest point value(pv) for a referral(excluding pv's that are smaller for duplicate account numbers)

So if the table looked like this
ID AccntNum EmpID PointValue
1..3444.....45....60
2..3444.....45....55
3..7777.....45....40

I want this returned
AccntNum EmpID PointValue
3444.....45....60
7777.....45....40

Since the two point values 60,55 are for the same account. I only want the highest.

Pulling the maximum value for that employee on that account, but also keeping other accounts maximums.

Here is what I have so far. It works, but it is slow. Even when there are only 10 records that apply to one employee (There are about 8000 in the table)

SELECT SUM(Point_Value) AS points
FROM Referrals R
WHERE R.Num IN (
	SELECT TOP 1 P.Num
	FROM Referrals P
	WHERE P.Account_Num=R.Account_Num AND
              P.EmpID=@empid
	ORDER BY Point_Value DESC)

I'm hoping that there is an easier way to write this. Its really slow for the amount of records that apply. It'd be great if someone could help.

Thanks

 

[vongrunt]

Correlated subquery inside IN...

Can you append AND R.EmpID=@empid to this query and see is there any improvement?

 

[pwilson]

Should the R.EmpID=@empid go in the SELECT TOP 1 or in the SELECT SUM()?

 

[vongrunt]

In the SELECT SUM()... at the end of query.

Some questions:
- do you want this query to run for a single EmpID only?
- what should happen when two records have max. PointValue?

 

[pwilson]

I tried both, and the SELECT SUM() really helped. Thanks a ton.

I looked at this FAQ,

http://www.tek-tips.com/faqs.cfm?fid=768

But didn't see how it applied.
Where can I look to find more information about the Query Optimizer?

 

[pwilson]

Only one employee.
With duplicate MAX's, it should use only one of them.

 

[vongrunt]

It appears that you need max. PointValue for each AcctNum... for a single EmpID:

select EmpID, AccntNum, max(PointValue) 
from Referrals 
where EmpID = @empid
group by EmpID, AccntNum

Remove WHERE stuff and results will contain data for all employees