I have the following query i'm running, and i want to see if i can speed it up.
Here is the situation:
I have a temp table (#temp), which holds the results of a number of queries.
The calculation i am performing is based off of data from three tables, all of which contain a couple hundred thousand records.
For the purposes of this question here are some basics:
Table1 - Main table. Has 1 primary key guid column
- Has a field, Limit, which we subtract from our calculation.
Table2 - Secondary table. Has a foreign key relationship with table1
- Has a field, Amount, which we want to find the sum of.
Table3 - Secondary table. Has a foreign key relationship with table1
- Has a field, Amount2, which we want to find the sum of.
- Very large table. Also need to filter this to records for the next 7 days based on a date field
Here is the query:
Set CalculatedField =
(Select Sum(t2.Amount) - Sum(t1.Limit) +
(Select Sum(t3.Amount) from table3 t3
where date <= dateadd(day,7,getdate())
and ID = #temp.ID)
from table1 t1
left join table2 t2 on t1.ID = t2.ID
where t1.ID = #temp.ID)
EDIT: I should mention that the reason i have the subquery is purely for the filtering by 7 days records. I didn't have this subquery in the past, i had the table joined in. But i haven't been able to figure out how to filter the results from that table without a subquery.
Any advice anyone can provide is most appreciated!!