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
Showing posts with label sort. Show all posts
Showing posts with label sort. Show all posts
Wednesday, March 21, 2012
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 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
"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
sql
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
"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
sql
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 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"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
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"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
Hash join
In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regards
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regards
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards
Hash join
In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regardssql
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regardssql
Hash join
In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards
Subscribe to:
Posts (Atom)