Hash Match Faster Than Index Sort?

It seems logical to me that a hash match is a more efficient engine than a
sort on an index. I have several views that make up a final view. (Written
by my superior). He wants to use this view, but it takes forever. I am
using his view. It takes forever. I believe that if a single view that
creates the same recordset instead of referencing a view within a view shoul
d
be faster. My problem is showing this to be the case as the view on view
itself shows the subtree cost to be 2.7 and the single view shows a cost of
3.65 but there are no sorts involved.
What is confusing is that if I replace the view on view query with the
single query, the cost of the view remains 2.7 despite the fact that it is
the same as the one for 3.65. The time involved in this query is more than
can be afforded. Can anyone tell me whether a hash is faster than a sort o
n
a large table?
--
Regards,
Jamie"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
> (Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
> should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
> of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
> than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
> on
> a large table?
If a view is written correctly there should be no performance penalty
whether you query against the view or bypass it.
As to the hash match or sort, the question is too vague to answer. It
depends on the query, the schema and the statistics.
David|||Have you looked at covering indexes or possibly an indexed view?
Are the join columns indexed?
The view on view should have no penalty, it is more of an administrative
hassle than anything else.
As far as the Hashing goes, that depends. Table structures, index
structures, statistical data and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
(Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
on
> a large table?
> --
> Regards,
> Jamie