Thanks for the info, Fabio. I'm pretty sure I don't have an index on the date field right now and maybe I should? You've given me some food for thought ... thanks!

~~Bonnie



>>Hmmm ... you and Fabio disagree on this one. I wonder who's right? =)
>>
>
>Of course, the exact answer is not simple.
>
>First, if the table have correct indexes,
>EXISTS() and MAX() subquery doesn't return any data, but a logical seek T/F only.
>
>With correct indexes SHOWPLAN_TEXT retun:
>

>* not exists
>  |--Merge Join(Right Anti Semi Join, MANY-TO-MANY MERGE:([mytable].[fk])=([mt1].[fk]), RESIDUAL:([mytable].[fk]=[mt1].[fk] AND [mytable].[date]>[mt1].[date]))
>       |--Index Scan(OBJECT:([tempdb].[dbo].[mytable].[tempfk]), ORDERED FORWARD)
>       |--Index Seek(OBJECT:([tempdb].[dbo].[mytable].[tempfk] AS [mt1]), SEEK:([mt1].[fk] IsNotNull) ORDERED FORWARD)
>
>* = MAX()
>  |--Top(1)
>       |--Segment
>            |--Index Seek(OBJECT:([tempdb].[dbo].[mytable].[tempfk] AS [mt1]), SEEK:([mt1].[fk] IsNotNull),  WHERE:([mt1].[date]<>NULL) ORDERED BACKWARD)
>

>and you can look that the second it performs only a group of seek.
>
>A simple test return:
>With 10/100K rows, second query is 40/50 times faster.
>
>Segment is the special optimization for these type queries
>http://msdn2.microsoft.com/en-us/library/ms180774(SQL.90).aspx
>( Of course i found two bugs here:
>1. SQL2KSP3 support it, but trace of the operator Segment doesn't exist in the BOL2K
>2. set showplan_text on doesn't print the argument fk ( you can see it in graphical plan )
>
>Segment is conceptually equivalent to VFP's SET KEY TO code,
>and second plan is similar to:
>

>select mytable
>SET ORDER TO (FK,DATE) DESC
>
>SCAN FOR NOT ISNULL(date)
>  SET KEY TO FK
>  INSERT INTO result (mytable.*)
>  go bottom && seek
>  SET KEY TO
>ENDSCAN
>

>
>without indexes, the second is 50% faster,
>but this depends a lot on the distribution of the data inside the pages.
>
>Fabio
>
>>~~Bonnie
>>
>>
>>>>Yes, I know ... but the second example you gave seemed much simpler, so I went with it. <s> I don't know which one would be faster, but I don't have enough data to benchmark it right now.
>>>
>>>Hi Bonnie,
>>>
>>>I would expect the first one to be faster. The SQL Server is optimized for the first type of query because EXISTS subquery doesn't return any data.