Luis Cazares (3/31/2015)
As previously said, you can use a cross apply.I'm leaving both options. Note that the join needs to calculate all the values before joining and that's why it can be slow.
SELECT TOP 5
x.Result
FROM dbo.MyTable1 t1
CROSS APPLY (SELECT SUM(lt2.col3) AS Result
FROM dbo.MyTable2 lt2
WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) x;
SELECT TOP 5
x.Result
FROM dbo.MyTable1 t1
JOIN (SELECT lt2.col1, lt2.col2, lt2.id,
SUM(lt2.col3) AS Result
FROM dbo.MyTable2 lt2
GROUP BY lt2.col1, lt2.col2, lt2.id ) x ON x.col1 = lt.col1 AND x.col2 = lt.col2 AND x.id = lt.id;
True, forgot that, just saw it via SQL Sentry. Thanks.
Tried as a cross apply too, and it basically performs the same. Now I wonder if , on my particular case, a window function may perform better. But not very good with window functions. I will try though.
I'm afraid I will have to take a look on the current Indexes, but I was trying to improve the TSQL as a 1st option.