troubleshooting Question

A Difficult Query / Performance issue

Avatar of AJS_Developer
AJS_Developer asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
2 Comments1 Solution307 ViewsLast Modified:
Hi There,

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:

Update #temp
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!!


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros