>It looks like MIN and MAX are not Rushmore optimizable, so this query is dragging along (1.1 million rows, indexes on all columns). Can anyone recommend any alternatives? I was thinking that I could nest something and use TOP 1 or something. Any ideas?
>
> SELECT cPolicySymbol, cPolicyNumber , ;
> MIN(tPolicyEffectiveDate) AS tPolicyEffectiveDate , ;
> MAX(tPolicyExpirationDate) AS tPolicyExpirationDate ;
> FROM PremPolicyXref ;
> WHERE iPremApplicationID = 1 ;
> GROUP BY cPolicySymbol , cPolicyNumber
>
It seems to me that, because of the GROUP BY, all records have to be processed anyway. Try to omit min() and max(), and see whether the query gets much faster. I guess it won't, but I am curious whether I am guessing wrong in this case.
(I am leaving in 5 minutes, so I will continue this discussion tomorrow - ca. 11:00 UTC.)
Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)