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 advice I have modified my DISTINCT to be a GROUP BY but that is not saving any time for the results to come back. Please note: the time I am worried about is the time it will take to process and return the results. I know it breaks the hearts of many DBAs out here but I am not concerned with the time it takes the database to process the actual request but how long it takes to get the data set.
Here's the queries in question ....
1. SELECT DGRP_ID,DGRP_TITLE FROM IREP_V_POSITION_STANDARDS WHERE WF_STOP BETWEEN '2/1/2009' AND '2/28/2009'
2. SELECT DGRP_ID,DGRP_TITLE FROM IREP_V_POSITION_STANDARDS WHERE WF_STOP BETWEEN '2/1/2009' AND '2/28/2009' GROUP BY DGRP_ID,DGRP_TITLE ORDER BY DGRP_ID,DGRP_TITLE
3. SELECT DISTINCT DGRP_ID,DGRP_TITLE FROM IREP_V_POSITION_STANDARDS WHERE WF_STOP BETWEEN '2/1/2009' AND '2/28/2009' ORDER BY DGRP_ID,DGRP_TITLE
# 1 runs in 2 seconds. Both # 2 and 3 run in 22 seconds.
# 1 returns 25333 rows
# 2 and 3 each return 81
For your viewing pleasure I have attached sql plans for each of the queries.
I'm quite sure that I have indexed everything possible as far as joins go in the tables that make up the view so I don't know where else to look .... and, quite frankly, am still really puzzled as to how both the GROUP BY and DISTINCT versions can return so much slower given the large difference in data returned.
Thanks for all your previous help as well as, hopefully, future help 🙂
Edit: In case anyone was wondering the effect ORDER BY would have on #1, there appears to be no effect .... at least in returning the data .... still 1-3 seconds consistently.