That index gives me a marked improvement.  Query time goes from 1:07 to 0:29.  But something still doesn't seem right b/c if i remove the 'AND p.DGID = std.DGID' part,...

This is the only index/key on the table now.  It seems to already contain the DGID column.

ALTER TABLE [dbo].[Partner] ADD CONSTRAINT [PK_Partner] PRIMARY KEY...

I wouldn't need an index on the EDIStdDocs DGID column?

I guess i should ask what i can do to be able to add the 'AND p.DGID = std.DGID' back to the query.  Do i need to add an index...

ChrisM@Work - Your query works great now.  Pretty sure i found the issue.  I did have to change 'COUNT(TP_PartID)' to 'COUNT(p.TP_PartID)' to fix an ambiguous name error.
The main...

Here is ddl for sent, trade, and partner tables, the ones with the joins that seem to cause issue.

The original query only takes 40 sec to complete, that query takes 56 sec.  Also, many of the returned records have an empty TP_Name field, and none of the TP_Name...

Here are the execution plans with and without the case statement.  My ultimate goal would be to improve the query.  I think the issue lies in joining the Trade table...

This is a query that, unfortunately, I'm having to help the software developer figure out the problem with.  I tried removing the NOLOCKs but didn't seem to impact performance.

peterzeke (9/14/2010)

So, they were making you volunteer?

Granted, it's hard to demand payment from friends & family, but if...

Craig-315134 (9/13/2010)

A cynic might conclude, 'no good deed goes unpunished', and 'it's not what you know, but who you know'.

But the lesson I learned was, 'no-one rides for free'. The...

Is there any masking or formatting you can do on the Data Access Page column that contains the data?  Sorry, i guess this is more of a question than an...