Okay, well here's everything I guess is pertinent for full troubleshooting of this..

View Definition

CREATE VIEW [dbo].[IREP_V_POSITION_STANDARDS] AS

SELECT SS.SERVICE_STANDARD, PS.SERVICE_STANDARD AS POSITION_SERVICE_STANDARD,

WFI.WS_INSTANCE_ID, WFI.WF_STOP, POS.JOB_POSITION, TTYP_TITLE,

WFI.WF_EFF_DATE, WFI.POLICY_NUMBER, DGRP_TITLE,DGRP.DGRP_ID,

WFI.WF_START,WS_DAYS_TOTAL,PD_DAYS_TOTAL,PD_COUNT,STATEDESC

FROM

IREP_T_WF_INFO...

As mentioned later in my post, it's a view.

I've read the Forum Etiquette but not sure how that applies to my question ..... unless you just always paste that into...

Okay.

Was able to get acceptable times on the original query I was working with but now I'm working on a different one and am running into the same problem.

Following y'all's...

Modified * to column list and performance of results does not increase. Not really sure why you demanded that for what we are discussing.

My contention is simply that Distinct...

Following what Lynn is saying ...... I believe you all are looking at this solely from the role of a DBA who only cares about the processing time at a...

forgive me for being blunt by why would anyone do a distinct that is going to return the same number of rows as the full SELECT?

The whole point (at least...

GilaMonster (2/20/2009)

Post a reproduction please? It'll be the first case I've ever seen of that.

Adding distinct to a query adds work. It requires that SQL sort or hash the resultset...

Grant Fritchey (2/20/2009)

Really? DISTINCT has consistently improved your performance? That's incredibly unique. Search through old posts. It's got a well-deserved bad reputation.

I'm just saying what I see the execution plan...

Grant Fritchey (2/20/2009)

Then that's good luck on your part. I've seen DISTINCT simply destroy performance. If you look at your two execution plans, you can see it at work. It's...

To address the concerns on why I'm using a join .....

The view is a source to a report. The application works in this manner....

1. User selects report type

2....

I also ended up creating a view to be able to see the DF constraints the same way I can see any other constraint via Information Schema views.

It doesn't have...

It appears to be not handling foreign keys properly and scripting them up as Primary keys.

For the X table, AGENCY_LICENSE_ID and LICENSE_ID work like a 2 column key and one of them will always be not null. The table is only ever populated via...

Are the names of your tables actually table1, table2, table3 and table4?

If that is the case, and your select statement is set up correctly, then it should work.

To test the...

INSERT INTO TABLE3 (col1, col2, col3, col4, col5)

SELECT T1.col1, T1.col2, T2.col1, T2.col2, T2.col3 FROM TABLE1, TABLE2

Put something like that into a SQL task and it should...