SQL Server Performance Forum – Threads Archive

Too many subqueries

Hello! I got a table design that I cannot change, so either I need a new way to write my query or maybe add some other tables to help. For example precalculated tables. The problem I have is that with the current design I will need to do about 100 subqueries and 95% is from the same table but with one change. What I am doing here is to sum the points that the function returns, based on "my own" and the other points. SELECT
(select dbo.CalculateDifference(@mypoints1, points) from pointstable where id = 1 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints2, points) from pointstable where id = 2 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints3, points) from pointstable where id = 3 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints4, points) from pointstable where id = 4 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints5, points) from pointstable where id = 5 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints6, points) from pointstable where id = 6 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints7, points) from pointstable where id = 7 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints8, points) from pointstable where id = 8 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints9, points) from pointstable where id = 9 and userId = u.userId)
+ (select dbo.CalculateDifference(@mypoints10, points) from pointstable where id = 10 and userId = u.userId)
FROM
…
This example show only 10 subqueries but I am sure you get the picture. I was thinking about a table where all the points has its own column, so that I only need to query once per row instead of 100 times per row. But that would be hard to maintain, so maybe a view would be better, but I wonder if you can improve speed by just adding a view? Any suggestions? Thank you!

---

What is the function CalculateDifference doing ?
KH

---

That function first calculates the difference between the two points that were sent in, and then calculates and returns a score based on the difference. But if the difference is within some certain range the calculation is not done, instead a fixed value is returned.

---

Try this . . . select sum(dbo.CalculateDifference
(case when id = 1 then @mypoints1
when id = 2 then @mypoints2
. . .
end), points)
from yourtable
KH

---

Thank you that works, any other suggestions, or is this the best way?

---

Is mypoint values coming from a table or can be stored in a table with the userID? Is there an implicit relation between mypoint and the ID? May the Almighty God bless us all!
www.empoweredinformation.com

---

]]>