[gymratz]

Below I have a query that took a long time to complete when I had only 100 entries.
Now that I have thousands of records it doesn't complete after 3 minutes (time out period).

Can you please help me look at the code and figure out areas that I could utilize views to make this more efficient?

Select
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	count(distinct Total_All.Client_ID) as Total_Clients,
	count(distinct Active_O.Client_ID) as Active_Ongoing,
	count(distinct InActive_O.Client_ID) as Inactive_Ongoing,
	count(distinct Total_O.Client_ID) as Total_Ongoing,
	count(distinct Active_R.Client_ID) as Active_Retros,
	count(distinct InActive_R.Client_ID) as Inactive_Retros,
	count(distinct Total_R.Client_ID) as Total_Retros,
	count(distinct Total_FW_Comps.Composite_ID) as Total_FW_Comp,
	count(distinct Total_PE_Comps.Composite_ID) as Total_PE_Comp,
	count(distinct Ongoing_FW_Comps.Composite_ID) as Ongoing_FW_Comp,
	count(distinct Ongoing_PE_Comps.Composite_ID) as Ongoing_PE_Comp,
	count(distinct Retro_FW_Comps.Composite_ID) as Retro_FW_Comp,
	count(distinct Retro_PE_Comps.Composite_ID) as Retro_PE_Comp
FROM Engagements as E
	INNER JOIN Clients as C on C.ID = E.Client_ID
	INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
	INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)'
			) as Retro_FW_Comps on C.ID = Retro_FW_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True'
			) as Retro_PE_Comps on C.ID = Retro_PE_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null AND Comp.Perf_Exam = 'True'
			) as Total_PE_Comps on C.ID = Total_PE_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)'
			) as Ongoing_FW_Comps on C.ID = Ongoing_FW_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True'
			) as Ongoing_PE_Comps on C.ID = Ongoing_PE_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null
			) as Total_FW_Comps on C.ID = Total_FW_Comps.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') AND E.Inactive IS NULL
		) As Active_O on C.ID = Active_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') AND E.Inactive IS NOT NULL
		) As InActive_O on C.ID = InActive_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NULL
		) As Active_R on C.ID = Active_R.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NOT NULL
		) As InActive_R on C.ID = InActive_R.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)')
		) As Total_R on C.ID = Total_R.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)')
		) As Total_O on C.ID = Total_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL
		) As Total_All on C.ID = Total_All.Client_ID
WHERE (EmpMe.Status = 'Partner' or EmpMe.Status = 'Sr. Manager' or EmpMe.Status = 'Manager' or EmpMe.Status = 'Lead Verifier') and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By MyStatus, TheName

[baavgai]

Wow. Just wow.

Well, here's how I'd do half of it. See if this gives you any ideas.

select Composite_ID, Comp.Client_ID,
			sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps,
			sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp,
			sum(case when E.Reports_Out is Null AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps,
			sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps,
			sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps,
			sum(case when E.Reports_Out is Null then 1 else 0 end) as Total_FW_Comps
	from Composites Comp
		INNER JOIN Engagements 
			as E on E.Client_ID = Comp.Client_ID

Edit, obviously you can do an optimization by putting E.Reports_Out is Null is the base where and taking it out of the cases, but I wanted to leave it as similar as possible.

This post has been edited by baavgai: 02 December 2008 - 05:24 PM

[gymratz]

I just let this run for 25 minuts and got no result.
I'm thinking it's either created some sort of loop or is causing an error.
It DID work before - with no changes to the code. However, at that time, there was only perhaps 10 clients with a total of 50-100 "Engagements."

There are now 270 clients, 900 engagements, and this will easily quadruple by the end of the week.

Just saw your response, let me look at your code.

[baavgai]

Oops, forgot the group by. That last one shouldn't have run at all

select Composite_ID, Comp.Client_ID,
			sum(case when E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps,
			sum(case when E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp,
			sum(case when Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps,
			sum(case when E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps,
			sum(case when E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps,
			count(*) as Total_FW_Comps
	from Composites Comp
		INNER JOIN Engagements 
			as E on E.Client_ID = Comp.Client_ID
	where E.Reports_Out is Null
	group by  Composite_ID, Comp.Client_ID,

Edit, fixed the where.

This post has been edited by baavgai: 02 December 2008 - 05:42 PM

[gymratz]

I think that will help me get on the right track.
I need to double check all of my logic first - I used what you gave me and I'm getting numbers that aren't making any sense - numbers in the thousands that should be in the 10's. Once I check my logic to make sure I'm thinking this through correctly then I'll continue on with your logic.

Thanks again,

Just noticed your last post - you are right I had to edit it a little bit adding the Grouping before it would run. Luckily SQL throws errors that are sometimes intelligable and let me know what was missing .

This post has been edited by gymratz: 02 December 2008 - 05:51 PM

[Trogdor]

When you are getting strange numbers, usually it is the distinct that is at fault.
See if you can get rid of it completely.
Also, be careful with NULL values, especially if they are present in fields you join on.
Check if your data contains NULLs in Comp.Client_ID or Engagements.Client_ID

[gymratz]

Just when I thought I was getting it...

Ok so I started working on this - but my problem is I do not know how to get the results I need WITHOUT using Distinct.

Here is my code:

select 
		EmpMe.Status,
		EmpMe.Full_Name,
			sum(case when E.Reports_Out is NULL then 1 else 0 end) as Total_Clients,
			sum(case when E.Classification like '%Ongoing%' AND E.Inactive is NULL then 1 else 0 end) as Active_Ongoing,
			sum(case when E.Classification like '%Ongoing%' AND E.Inactive is not NULL then 1 else 0 end) as InActive_Ongoing,
			sum(case when E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps,
			sum(case when E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp,
			sum(case when Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps,
			sum(case when E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps,
			sum(case when E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps,
			count(*) as Total_FW_Comps
	from Composites Comp
		INNER JOIN Engagements 
			as E on E.Client_ID = Comp.Client_ID
	INNER JOIN Clients as C on C.ID = Comp.Client_ID
	INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
	INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
	where E.Reports_Out is Null AND RC.Eng_ID is Null AND (EmpMe.Status = 'Partner' or EmpMe.Status = 'Sr. Manager' or EmpMe.Status = 'Manager' or EmpMe.Status = 'Lead Verifier')
	group by EmpMe.Status, EmpMe.Full_Name
	Order By EmpMe.Status, EmpMe.Full_Name

The problem is that each client may have 10-100 Engagements - If any ONE of those engagements match the criteria I need to count the client as a whole as just one.
Thus I use distinct previously...

Now that I have gotten rid of distinct - I'm getting numbers like "809" that should be closer to 60...

Can I get a slightly harder shove? (In the right direction that is)

[gymratz]

I took another stab at it and got the intended RESULTS but it is still slow...
So far I only have about half of the query - I haven't done the other half because I'm not sure if this is the best way...

It took about 6 seconds to complete so far...

Select
	EmpMe.Status,
	EmpMe.Full_Name,
	Count(distinct Counts.Client_ID) as Total_Clients,
	Count(distinct Counts.Active_Ongoing) as Active_Ongoing,
	Count(distinct Counts.Inactive_Ongoing) as Inactive_Ongoing,
	Count(distinct Counts.Retro_FW_Comps) as Retro_FW_Comps,
	Count(distinct Counts.Retro_PE_Comp) as Retro_PE_Comp,
	Count(distinct Counts.Total_PE_Comps) as Total_PE_Comps,
	Count(distinct Counts.Ongoing_FW_Comps) as Ongoing_FW_Comps,
	Count(distinct Counts.Ongoing_PE_Comps) as Ongoing_PE_Comps
From Engagements as E
INNER JOIN Clients as C on C.ID = E.Client_ID
	INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
	INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
	LEFT JOIN (
		select 
			Composite_ID,
			Comp.Client_ID,
				sum(case when E.Reports_Out is NULL then 1 else 0 end) as Total_Clients,
				sum(case when E.Classification like '%Ongoing%' AND E.Inactive is NULL then 1 else 0 end) as Active_Ongoing,
				sum(case when E.Classification like '%Ongoing%' AND E.Inactive is not NULL then 1 else 0 end) as InActive_Ongoing,
				sum(case when E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps,
				sum(case when E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp,
				sum(case when Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps,
				sum(case when E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps,
				sum(case when E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps
		from Composites Comp
			INNER JOIN Engagements 
				as E on E.Client_ID = Comp.Client_ID
		where E.Reports_Out is Null
		Group By Composite_ID, Comp.Client_ID
	) as Counts on C.ID = Counts.Client_ID
WHERE (EmpMe.Status = 'Partner' or EmpMe.Status = 'Sr. Manager' or EmpMe.Status = 'Manager' or EmpMe.Status = 'Lead Verifier') and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By EmpMe.Status, EmpMe.Full_Name

[gymratz]

I take that back - what I posted before wasn't right at all...
I think it's obvious I did it at night while I was getting tired.

I can not think of any way to get my intended results without using the initial query up top that was scoffed at .

To make it clear what I need, I need:
To count how many CLIENTS have one or more Engagements meeting a particular criteria.
I then repeat this multiple times with different criteria.

[baavgai]

Sorry, I can only work with what I see. Maybe if we take a walk through the analytical process.

Here's your core query:

select
	EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName
FROM Engagements as E
	INNER JOIN Clients 
		as C on C.ID = E.Client_ID
	INNER JOIN Rep_Client as RC 
		on RC.Client_ID = C.ID 
			and RC.Client_ID = E.Client_ID
			and RC.Eng_ID is Null
	INNER JOIN Employees as EmpMe 
		on EmpMe.ID = RC.Emp_ID
			and EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')
Group By EmpMe.Status, EmpMe.Full_Name

Not knowing what's with all the joins, it's hard to say to point of all the extra tables. Hopefully they're limitiers. What we know for sure it that the results should not exceed what we'd get with this:

select
	EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName
FROM Employees as EmpMe 
WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')
Group By EmpMe.Status, EmpMe.Full_Name

Reasonably, we'd want to boil all sub queries down to where they give us an Employee ID as a key.

gymratz, on 5 Dec, 2008 - 01:03 PM, said:

To count how many CLIENTS have one or more Engagements meeting a particular criteria.

Interesting. The base query doesn't represent this particularly well.

So, for this:

Left JOIN (
		select Distinct E.Client_ID 
			from Engagements E 
				WHERE E.Reports_Out is NULL
	) As Total_All on C.ID = Total_All.Client_ID

Did you perhaps really want something like this:

Left JOIN (
	select RC.Emp_ID, count(distinct E.Client_ID) as TotalClients
		from Engagements E 
			INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID
		WHERE E.Reports_Out is NULL
		group by RC.Emp_ID
	) As Total_All on Total_All.Emp_ID = EmpMe.ID

For something like:

select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName,
		EnClient.Total_Clients,
	FROM Employees as EmpMe 
		Left JOIN (
			select RC.Emp_ID, count(distinct E.Client_ID) as Total_Clients
				from Engagements E 
				INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID
					WHERE E.Reports_Out is NULL
				group by RC.Emp_ID
			) As EnClient on EnClient.Emp_ID = EmpMe.ID
	WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')

Flesh it out a little with some more criteria:

select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName,
		EnClient.Total_Clients,
		EnClient.Total_O as Total_Ongoing
	FROM Employees as EmpMe 
		Left JOIN (
			select RC.Emp_ID, count(distinct E.Client_ID) as Total_Clients,
					sum(case when E.Classification in ('Verification (Ongoing)','Model Exam (Ongoing)','Strategy Exam (Ongoing)','AUP (Ongoing)') then 1 else 0 end) as Total_Ongoing
				from Engagements E 
				INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID
					WHERE E.Reports_Out is NULL
				group by RC.Emp_ID
			) As EnClient on EnClient.Emp_ID = EmpMe.ID
	WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')

Wait, you want them distinct client per employee? You can do it at the case level.

select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName,
		EnClient.Total_Clients,
		EnClient.Total_O as Total_Ongoing
	FROM Employees as EmpMe 
		Left JOIN (
			select RC.Emp_ID, count(distinct E.Client_ID) as Total_Clients,
					count( distinct (case when E.Classification in ('Verification (Ongoing)','Model Exam (Ongoing)','Strategy Exam (Ongoing)','AUP (Ongoing)') then E.Client_ID else null end)) as Total_Ongoing
				from Engagements E 
				INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID
					WHERE E.Reports_Out is NULL
				group by RC.Emp_ID
			) As EnClient on EnClient.Emp_ID = EmpMe.ID
	WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')

Hope this helps.

This post has been edited by baavgai: 05 December 2008 - 01:19 PM

[gymratz]

Wow that will take some reading but was very thorough - thank you.

In the mean time here is what I did.
I broke up my queries into two separate ones for now.
The first half counts how many clients a person is working on that meet different criteria.

Select
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	count(distinct Total_O.Client_ID) + count(distinct Total_R.Client_ID) as Total_Clients,
	count(distinct Active_O.Client_ID) as Active_Ongoing,
	count(distinct InActive_O.Client_ID) as Inactive_Ongoing,
	count(distinct Total_O.Client_ID) as Total_Ongoing,
	count(distinct Active_R.Client_ID) as Active_Retros,
	count(distinct InActive_R.Client_ID) as Inactive_Retros,
	count(distinct Total_R.Client_ID) as Total_Retros
FROM Engagements as E
	INNER JOIN Clients as C on C.ID = E.Client_ID
	INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
	INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') AND E.Inactive IS NULL
		) As Active_O on C.ID = Active_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') AND E.Inactive IS NOT NULL
		) As InActive_O on C.ID = InActive_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)')
		) As Total_O on C.ID = Total_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NULL
		) As Active_R on C.ID = Active_R.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NOT NULL
		) As InActive_R on C.ID = InActive_R.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)')
		) As Total_R on C.ID = Total_R.Client_ID
WHERE (EmpMe.Status = 'Partner' or EmpMe.Status = 'Sr. Manager' or EmpMe.Status = 'Manager' or EmpMe.Status = 'Lead Verifier') and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By MyStatus, TheName

To try to explain how my tables are laid out:
Clients (No data is being pulled from this table, but I note it because it is the root of Client_ID).
Employees - The name and the status of the employee are being pulled from this table - has an Employee ID as a PK.
Engagements - this has information such as classifications, and the Client_ID to know which client an engagement belongs to.
Rep_Client - This table ties together the engagement and the employee since multiple employees can work on the same engagement, and one employee works on multiple engagements.

A client can have (and will) multiple Engagements - but an engagement only belongs to one Client.


An example may be as such.
5 Clients with a total of 100 engagements between the 5 of them.
An employee may be on 3 engagements for the first client, 10 engagements for the second client, and none for the other three.
Out of the first 3 engagments, two may be ongoing one retro.
Out of the second clients 10 engagements, 1 may be retro 9 may be ongoing.
For total retro it would show 2 clients.
For total ongoing it would show 2 clients.
For "total" it would show 4 (now being calculated by total retro + total ongoing).

All that being said - this part of the query runs in under a second currently.

Below is the second part of the query (right now broken out to be it's own entire query, although I'd like to combine it with the first one once I get it running.
This is the part I narrowed down to taking forever.
What is strange is, any SINGLE part of the query (any one of the joins) only takes about 15 seconds. But the time it takes to execute as I add them together seems to go up exponentially.

Select
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	count(distinct Total_PE_Comps.Composite_ID) as Total_PE_Comp,
	count(distinct Ongoing_FW_Comps.Composite_ID) as Ongoing_FW_Comp,
	count(distinct Ongoing_PE_Comps.Composite_ID) as Ongoing_PE_Comp,
	count(distinct Retro_FW_Comps.Composite_ID) as Retro_FW_Comp,
	count(distinct Retro_PE_Comps.Composite_ID) as Retro_PE_Comp
FROM Engagements as E
	INNER JOIN Clients as C on C.ID = E.Client_ID
	INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
	INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)'
			) as Retro_FW_Comps on C.ID = Retro_FW_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True'
			) as Retro_PE_Comps on C.ID = Retro_PE_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null AND Comp.Perf_Exam = 'True'
			) as Total_PE_Comps on C.ID = Total_PE_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)'
			) as Ongoing_FW_Comps on C.ID = Ongoing_FW_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True'
			) as Ongoing_PE_Comps on C.ID = Ongoing_PE_Comps.Client_ID
	LEFT JOIN (
			select distinct Composite_ID, Comp.Client_ID
				from Composites Comp
					INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
					WHERE E.Reports_Out is Null
			) as Total_FW_Comps on C.ID = Total_FW_Comps.Client_ID
WHERE (EmpMe.Status = 'Partner' or EmpMe.Status = 'Sr. Manager' or EmpMe.Status = 'Manager' or EmpMe.Status = 'Lead Verifier') and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By MyStatus, TheName

I'll look through what you posted though to see if I can figure this part out.
Also - as a final note.
Composites table is linked using Client_ID to the Clients table. A client will have multiple Composites - but a Composite only belongs to one client.

[gymratz]

I haven't got the whole thing working yet; however, one of your ideas has helped a lot and got the ball rolling.

					count( distinct (case when E.Classification in ('Verification (Ongoing)','Model Exam (Ongoing)','Strategy Exam (Ongoing)','AUP (Ongoing)') then E.Client_ID else null end)) as Total_Ongoing

That so far has been genius!

Thanks,