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
 SQL and Subqueries

Author  Topic 

rogier.vs
Starting Member

3 Posts

Posted - 2012-11-26 : 12:18:58
Could somebody make this code below efficient? Is it possible to this query one time like:


SELECT * FROM user_target WHERE uid=3 LIMIT 1


store the result in a variable and use "user_target.m10" to calculate.

The storage that I tried @user_target doesn't allow more than one column.


SELECT
id,
((m10/population*(SELECT m10 FROM user_target WHERE uid=3))+
(m20/population*(SELECT m20 FROM user_target WHERE uid=3))+
(m30/population*(SELECT m30 FROM user_target WHERE uid=3))+
(m40/population*(SELECT m40 FROM user_target WHERE uid=3))+
(m50/population*(SELECT m50 FROM user_target WHERE uid=3))+
(m60/population*(SELECT m60 FROM user_target WHERE uid=3))+
(m70/population*(SELECT m70 FROM user_target WHERE uid=3))+
(m80/population*(SELECT m80 FROM user_target WHERE uid=3))+
(m90/population*(SELECT m90 FROM user_target WHERE uid=3))+
(m100/population*(SELECT m100 FROM user_target WHERE uid=3)))
FROM data_population


Anybody?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 13:01:31
[code]SELECT
a.m10/a.population*b.m10 +
....
a.m100/a.population*b.m100
FROM
data_population a
CROSS APPLY
(
SELECT
m10,
....
m100
FROM
user_target
WHERE
uid = 3
)b
[/code]
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-11-26 : 13:08:18
Would a CROSS JOIN be better than a CROSS APPLY?

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 13:33:04
Unsubstantiated intuition tells me that both cross apply and cross join may perform the same - I wouldn't be surprised if even the query plans are the same. Certainly, cross join is easier to read and understand, but my mind has become polluted by influence of people like Visakh that I think of cross apply before I even consider cross join.
Go to Top of Page

rogier.vs
Starting Member

3 Posts

Posted - 2012-11-26 : 18:40:33
Thanks for your reply! I had to use JOIN because CROSS APPLY is an MSSQL function. My fault i did not specify. Thanks.
Go to Top of Page
   

- Advertisement -