Interesting stuff on indexing. So knowing this, would an index on small table PK join on large table FK cause significant speed gains?
I suppose my other question could be posted as such:
Why are is the bolded line of SQL below giving me such gains in query speed? It seems like the logic is redundant as it is already in the first piece of the sub query.
SELECT a.SubID, Min(a.OrderNumber)
(SELECT a.SubID, a.SubAmount, a.OrderNumber
WHERE a.EndDate between #1/1/2012# and #12/31/2012# and a.code = "xyz" and a.code2 = "foo" and a.code3 = "bar")
(Select a.SubID, a.OrderNumber
WHERE a.EndDate >#1/1/2012# and a.code = "xyz" and a.code2 = "foo" and a.code3 = "bar") as b
ON a.SubID = b.SubID and a.OrderNumber< b.OrderNumber
GROUP BY a.SubID