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 WFI
INNER JOIN
IREP_T_WF_POSITION_INFO PINFO ON WFI.ID = PINFO.WFI_ID
INNER JOIN
IREP_T_DOCTYPE_GROUP_REL DGRL ON WFI.DOCTYPE_CD = DGRL.DCTP_ID
INNER JOIN
IREP_T_DOCTYPE_GROUP DGRP ON DGRL.DGRP_ID = DGRP.DGRP_ID
INNER JOIN
IREP_T_TRANSACTION_TYPES_DOCTYPES TTDT ON TTDT.DGRP_ID = DGRP.DGRP_ID
INNER JOIN
IREP_T_TRANSACTION_TYPES TTYP ON TTDT.TTYP_ID = TTYP.TTYP_ID
INNER JOIN
IREP_T_STANDARDS_DOC_TYPES SS ON (WFI.STATE_CODE=SS.STATE_ID) AND (SS.DGRP_ID=DGRP.DGRP_ID)
INNER JOIN
IREP_T_POSITION_STANDARDS_DOCTYPES PS ON DGRP.DGRP_ID = PS.DGRP_ID AND WFI.STATE_CODE = PS.STATE_ID AND PINFO.POSITION_ID = PS.POSITION_ID
INNER JOIN
IREP_T_POSITIONS POS ON PINFO.POSITION_ID = POS.ID
INNER JOIN
IREP_T_WF_STATUS SU ON WFI.WF_STATUS = SU.ID
INNER JOIN
FFG_STATES ST ON WFI.STATE_CODE = ST.STATEID
WHERE SU.WF_STATUS = 'Completed'
GO
Tables that make up that view....
CREATE TABLE [dbo].[IREP_T_DOCTYPE_GROUP](
[DGRP_ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_TITLE] [varchar](255) NOT NULL DEFAULT (''),
PRIMARY KEY CLUSTERED
(
[DGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_DOCTYPE_GROUP_REL](
[DGRL_ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_ID] [int] NOT NULL,
[DCTP_ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[DGRL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_ID] [int] NOT NULL,
[POSITION_ID] [int] NOT NULL,
[STATE_ID] [int] NOT NULL,
[SERVICE_STANDARD] [decimal](8, 2) NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_POSITIONS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[JOB_POSITION] [char](50) NULL,
CONSTRAINT [PK_IREP_T_POSITIONS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_STANDARDS_DOC_TYPES](
[ID] [int] IDENTITY(1,1) NOT NULL,
[STATE_ID] [int] NOT NULL,
[SERVICE_STANDARD] [decimal](5, 2) NULL,
[DGRP_ID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_TRANSACTION_TYPES](
[TTYP_ID] [int] IDENTITY(1,1) NOT NULL,
[TTYP_TITLE] [varchar](500) NOT NULL DEFAULT (''),
[WTYP_ID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[TTYP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_TRANSACTION_TYPES_DOCTYPES](
[TTDT_ID] [int] IDENTITY(1,1) NOT NULL,
[DGRP_ID] [int] NOT NULL,
[TTYP_ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[TTDT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_WF_INFO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WF_START] [datetime] NULL,
[WF_STOP] [datetime] NULL,
[WF_STATUS] [int] NULL,
[WS_INSTANCE_ID] [char](11) NOT NULL,
[WF_ID] [int] NULL,
[WF_MD_ID] [char](11) NULL,
[POLICY_NUMBER] [char](15) NULL,
[COMPANY_ID] [int] NULL,
[ASSIGNED_TO] [int] NULL,
[DOCTYPE_CD] [int] NULL,
[WF_EFF_DATE] [datetime] NULL,
[AGENCY_CODE] [varchar](50) NULL,
[STATE_CODE] [int] NULL,
[DBA_NM] [varchar](1000) NOT NULL DEFAULT (''),
CONSTRAINT [PK_IREP_WF_INFO] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_WF_POSITION_INFO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WS_INSTANCE_ID] [char](11) NOT NULL,
[POSITION_ID] [int] NOT NULL,
[WS_DAYS_TOTAL] [int] NOT NULL,
[WS_COUNT] [int] NULL,
[PD_DAYS_TOTAL] [int] NULL,
[PD_COUNT] [int] NULL,
[WF_WS_DAYS_TOTAL] [int] NULL,
[WF_PD_DAYS_TOTAL] [int] NULL,
[DAY_COMPLETED] [datetime] NULL,
[WFI_ID] [int] NULL,
CONSTRAINT [PK_IREP_T_WF_POSITION_INFO] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[IREP_T_WF_STATUS](
[ID] [int] NOT NULL,
[WF_STATUS] [char](50) NULL,
CONSTRAINT [PK_IREP_WF_STATUS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[IREP_T_STANDARDS_DOC_TYPES] WITH CHECK ADD CONSTRAINT [FK_IREP_STANDARDS_DOC_TYPES_IREP_STANDARDS_DOC_TYPES] FOREIGN KEY([DGRP_ID])
REFERENCES [dbo].[IREP_T_DOCTYPE_GROUP] ([DGRP_ID])
ALTER TABLE [dbo].[IREP_T_STANDARDS_DOC_TYPES] CHECK CONSTRAINT [FK_IREP_STANDARDS_DOC_TYPES_IREP_STANDARDS_DOC_TYPES]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_INBOXES] FOREIGN KEY([ASSIGNED_TO])
REFERENCES [dbo].[IREP_T_INBOXES] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_INBOXES]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DESC] FOREIGN KEY([WF_ID])
REFERENCES [dbo].[IREP_T_WF_DESC] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DESC]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DOCTYPES] FOREIGN KEY([DOCTYPE_CD])
REFERENCES [dbo].[IREP_T_WF_DOCTYPES] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_DOCTYPES]
ALTER TABLE [dbo].[IREP_T_WF_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_STATUS] FOREIGN KEY([WF_STATUS])
REFERENCES [dbo].[IREP_T_WF_STATUS] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_INFO] CHECK CONSTRAINT [FK_IREP_WF_INFO_IREP_WF_STATUS]
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_POSITIONS] FOREIGN KEY([POSITION_ID])
REFERENCES [dbo].[IREP_T_POSITIONS] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] CHECK CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_POSITIONS]
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] WITH CHECK ADD CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_T_WF_INFO] FOREIGN KEY([WFI_ID])
REFERENCES [dbo].[IREP_T_WF_INFO] ([ID])
ALTER TABLE [dbo].[IREP_T_WF_POSITION_INFO] CHECK CONSTRAINT [FK_IREP_T_WF_POSITION_INFO_IREP_T_WF_INFO]
Indexes on those tables
CREATE NONCLUSTERED INDEX [IX_IREP_T_DOCTYPE_GROUP_REL_DCTP_ID] ON [dbo].[IREP_T_DOCTYPE_GROUP_REL]
(
[DCTP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_DOCTYPE_GROUP_REL_DGRP_ID] ON [dbo].[IREP_T_DOCTYPE_GROUP_REL]
(
[DGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_POSITION_STANDARDS_DOCTYPES_STATE_ID] ON [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES]
(
[STATE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_POSITION_STANDARDS_DOCTYPES_POSITION_ID] ON [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES]
(
[POSITION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_POSITION_STANDARDS_DOCTYPES_DGRP_ID] ON [dbo].[IREP_T_POSITION_STANDARDS_DOCTYPES]
(
[DGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_STANDARDS_DOC_TYPES_DGRP_ID] ON [dbo].[IREP_T_STANDARDS_DOC_TYPES]
(
[DGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_STANDARDS_DOC_TYPES_STATE_ID] ON [dbo].[IREP_T_STANDARDS_DOC_TYPES]
(
[STATE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_TRANSACTION_TYPES_DOCTYPES_DGRP_ID] ON [dbo].[IREP_T_TRANSACTION_TYPES_DOCTYPES]
(
[DGRP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_TRANSACTION_TYPES_DOCTYPES_TTYP_ID] ON [dbo].[IREP_T_TRANSACTION_TYPES_DOCTYPES]
(
[TTYP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO] ON [dbo].[IREP_T_WF_INFO]
(
[STATE_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_1] ON [dbo].[IREP_T_WF_INFO]
(
[AGENCY_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_2] ON [dbo].[IREP_T_WF_INFO]
(
[ASSIGNED_TO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_WF_STATUS] ON [dbo].[IREP_T_WF_INFO]
(
[WF_STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_INFO_WS_INSTANCE_ID] ON [dbo].[IREP_T_WF_INFO]
(
[WS_INSTANCE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_IREP_T_WF_POSITION_INFO_POSITION_ID] ON [dbo].[IREP_T_WF_POSITION_INFO]
(
[POSITION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
FFG_STATES is actually a linked view from a different server and its source table looks like..
CREATE TABLE [dbo].[FFG_STATES](
[STATEID] [int] NOT NULL,
[STATEDESC] [char](100) NOT NULL,
[STATEABBR] [char](4) NOT NULL,
[COMPANY_0_VALID] [int] NOT NULL,
[COMPANY_1_VALID] [int] NOT NULL,
[COMPANY_2_VALID] [int] NULL,
[BA_VALID] [int] NOT NULL,
[BOP_VALID] [int] NOT NULL,
[CPP_VALID] [int] NOT NULL,
[FP_VALID] [int] NOT NULL,
[HP_VALID] [int] NOT NULL,
[PA_VALID] [int] NOT NULL,
[PUL_VALID] [int] NOT NULL,
[RV_VALID] [int] NOT NULL,
[WC_VALID] [int] NOT NULL,
[YAC_VALID] [int] NOT NULL,
[CUL_VALID] [int] NULL,
CONSTRAINT [PK_FFG_STATES] PRIMARY KEY CLUSTERED
(
[STATEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
If you feel you need data I can try to write something to generate it but we're talking millions of rows and quite a few tables so that will take a while.
To me, the question is more abstract than you're trying to make it. It all comes down to a full SELECT running and returning results much faster than a GROUP BY or DISTINCT all on the same chunk of data. The change in performance should be able to be tracked within the SQL plan but it's beyond my ability to see where. I'm not sure the "my query is performing quickly ... help me" sentiment really applies as much here but if you feel you can dig more efficiently with the structure then there it is.