I have a problem with a slow running query.
I have analysed the query with the sql server profiler but I can't seem to find a good solution.
I did find why it is running slow. If ask the full dataset it returns me 150 rows in 10s, if I added the sums and calculations it returns me 18 rows but it takes 8min.
In SQL Server Profiler I found out that the full dataset does about 1 000 000 reads and the sum query about 82 000 000. But I don't know how or why he is getting some many records. Because the full dataset is the sub select from the sum query.
the below query is an example not the real query(real query also has union all's in the sub query but the sum query is basically the same)
SELECT Sum(x) * 0.1,
Sum(y),
a
FROM (SELECT x,
y
FROM tx
INNER JOIN ty ON tx.a = ty.a
WHERE x = 1 --this returns 150 rows in 10s
) sub
GROUP BY a -- sum returns 18row 8min
Can somebody help me with some more test I have to do or a solution a could test?