Posted by Thomas R. Hummel on 07/14/06 14:09

Erland Sommarskog wrote:
> Thomas R. Hummel (tom_hummel@hotmail.com) writes:
> > Thanks for the interesting solution Erland. In the real life case of
> > course, I will have many more columns than just my_id and line_number.
> > I realize that everything is dependent on the specific data, etc., but
> > in general, would you expect this to perform better than:
>
> I will have to admit not having done any benchmark on the two solutions.
> But my gut feeling is that the solution with MIN and GROUP BY generally
> is better. The other seems to give more chances for the optimizer to go
> astray.

As promised... I had a chance today to run some tests with each method.
When I used a smaller table for the Main_Table (~17M rows) both had the
same estimated cost for their query plans, although the multiple joins
method ran a bit faster (3m49s vs. 5m11s). With a larger Main_Table
(~39M rows) the estimated cost for the query plan with the group by was
a bit higher than the multiple join method. In that case the group by
method ran 20m23s vs. 12m45s for the multiple join method.

Of course, this may vary depending on server memory, IO speed, data
frequencies, etc., etc., but for my case it looks like doing multiple
joins works out best.

Thanks,
-Tom.

[Back to original message]