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

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

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

hard time understanding the execution plans

SQL server 2005 has a really complicated execution plans that I am really unable to compare two execution plans with each other cumulative.

I can easliy see by eye that one execution plan is slower than the other but is there anyway to compare two execution plans in sql 2005?

Hi,

I'm afraid there is no comparison tool. Here is a reference to the items in the Execution Plan: http://msdn2.microsoft.com/en-us/library/ms175913.aspx. Hope it helps.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||True, either you can export the text based to a file for a comparison or search for any third party tools on web in this regard.|||

my problem is query execution plan does not help me understand the bottlenecks in the system as I can't compare with any realistic thing against.

how do u guys solve this problem? How do u decide that this is a decend query or say this is a bad one?

|||Here are some tips:
Remember that the thickness of arrows hints how many rows are transferred|||

thanks for the tips they are GREAT.

I will look into statistics IO

Halting Execution On Error

I have a simple SSIS package split into two parts; validation and processing. I want to be able to stop execution on any package errors (such as file not found, etc) using the OnError event handler. Is this at all possible?

No, if you want to stop execution when a task fails then make sure there are no OnCompletion or OnFailure precedence constraints leading from it.

As long as you don't have any concurrent execution paths then the package will stop.

-Jamie

|||Ah. Perfect. I've been using expressions and noticed the expression and constraint option but it didn't hit me to use that until you said something.

Thanks Jamie, for helping out a greenfoot here.

halt execution and wait for parameters

Hello,
does anybody know of a way to to have RS wait to execute (on access) until
the parameter fields have been filled?
thanks,
gregI've noticed this was a problem when I had default values for every
parameter. Try to remove default values.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> Hello,
> does anybody know of a way to to have RS wait to execute (on access) until
> the parameter fields have been filled?
> thanks,
> greg|||Thanks for the response. That is the same problem I'm having. I have to leave
defaults (8 fields) but the users change certian fields. thanks again good
to see others are having the same issues.
Greg
"Jason" wrote:
> I've noticed this was a problem when I had default values for every
> parameter. Try to remove default values.
> "greg" <greg@.discussions.microsoft.com> wrote in message
> news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> > Hello,
> > does anybody know of a way to to have RS wait to execute (on access) until
> > the parameter fields have been filled?
> >
> > thanks,
> > greg
>
>

Hack to Prevent Report From Running Automatically

After reading tons of articles about how to prevent initial execution
of a report (which I do know is by design), I came up with my own
hack. WARNING: This is definatey a Hack!, but once you have gotten
past the initial shock it does work quite well. (The code was only
tested on SQLRS 2005, so it may need tinkering for other versions)
Basically, from what I can gather the only way to prevent a report
from running automatically is to have a parameter whose default value
is null but required, for instance a string parameter whose Default
Value has been set to Null, with the Allow Blank Values option
checked, and the Allow Null Values option unchecked.
This works fine, if you have a paramater like this already that you
can just set the default value to Null, but if you don't want a Null
default value then you are out of luck.
You could create an additional parameter just for the sake of
preventing the report from running, but this mucks up the interface,
and having to tell your users, to just ignore that one parameter, it's
only there to keep the report from running straight-away is pretty
rubbish.
But following on that logic, you could create one of these extra
"ghost parameters" and then hide it from view when the page is loaded,
giving you the functionality of the above scenario, but without the
ugly textbox hanging around. This is quite different from creating a
"hidden parameter" by selecting the hidden radio button on the
parameter edit screen in reporting services which for a number of
reasons cannot be used to keep to the report from running.
So to implement it depends on where you are running the report from,
if you are using the Report Manager, the nice little out-of-the-box MS
tool for viewing the reports, then you need only need to modify the C:
\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services
\ReportManager\Pages\Report.aspx file
or wherever your SQL server reporting services install is located to
look like the following:
<%@. Register TagPrefix="MSRS"
Namespace="Microsoft.ReportingServices.UI"
Assembly="ReportingServicesWebUserInterface" %>
<%@. Page language="c#" Codebehind="Report.aspx.cs"
AutoEventWireup="true"
Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage"
EnableEventValidation="false" %>
<script language="C#" runat="server">
void Page_Load(object sender, EventArgs e) {
string ScriptText = "<script language='javascript'>" +
"var x =document.getElementsByTagName('span'); " +
"for (i=0;i<x.length;i++) " +
"{ " +
"if (x[i].innerText == 'ghostparam') { " +
"var node = x[i].parentElement; " +
"if (node) " +
"node.style.visibility = 'hidden'; " +
"node = node.nextSibling; " +
"if (node) " +
"node.style.visibility = 'hidden'; " +
"} " +
"}";
ScriptText += "<" + (char)47 + "script>";
Page.RegisterStartupScript("hackit",ScriptText);
}
</script>
Note the AutoEventWireup="true" in the Page Directive has been
changed, it is set to "false" by default.
If however you are calling the Web Service directly and not from the
Report Manager and embedding it in your app the change is even easier,
just edit the C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportServer\Pages\ReportViewer.aspx file and add the
following javascript directly just after the closing </form> tag.
<script language="javascript">
var x =document.getElementsByTagName("span");
for (i=0;i<x.length;i++)
{
if (x[i].innerText == "ghostparam") {
var node = x[i].parentElement;
if (node)
node.style.visibility = "hidden";
node = node.nextSibling;
if (node)
node.style.visibility = "hidden";
}
}
</script>
There may be a better way to write this javascript, and of course it
WILL BREAK if microsoft changes the way they output the html in a
future version of Reporting Services, so you will have to modify it
accordingly,
but any modifications should be straightforward and simple just find
the ghostparam textbox in the html and hide it.
The javascript examples above do rely on you creating a textbox
parameter called "ghostparam" with a Default Value of Null, and Allow
Nulls Values set to False and Allow Blanks Set to True. The absence
of such a parameter will not cause an error, it just means unless
there is another parameter preventing it, the report will just run
automatically as usual.
This should help a few of the more "liberal developers" out there :)
AlOn Feb 26, 11:13 am, o2sim...@.gmail.com wrote:
> After reading tons of articles about how topreventinitial execution
> of a report (which I do know is by design), I came up with my own
> hack. WARNING: This is definatey a Hack!, but once you have gotten
> past the initial shock it does work quite well. (The code was only
> tested on SQLRS 2005, so it may need tinkering for other versions)
> Basically, from what I can gather the only way topreventa report
> from running automatically is to have a parameter whose default value
> is null but required, for instance a string parameter whose Default
> Value has been set to Null, with the Allow Blank Values option
> checked, and the Allow Null Values option unchecked.
> This works fine, if you have a paramater like this already that you
> can just set the default value to Null, but if you don't want a Null
> default value then you are out of luck.
> You could create an additional parameter just for the sake of
> preventing the report from running, but this mucks up the interface,
> and having to tell your users, to just ignore that one parameter, it's
> only there to keep the report from running straight-away is pretty
> rubbish.
> But following on that logic, you could create one of these extra
> "ghost parameters" and then hide it from view when the page is loaded,
> giving you the functionality of the above scenario, but without the
> ugly textbox hanging around. This is quite different from creating a
> "hidden parameter" by selecting the hidden radio button on the
> parameter edit screen inreportingserviceswhich for a number of
> reasons cannot be used to keep to the report from running.
> So to implement it depends on where you are running the report from,
> if you are using the Report Manager, the nice little out-of-the-box MS
> tool for viewing the reports, then you need only need to modify the C:
> \Program Files\Microsoft SQL Server\MSSQL.2\ReportingServices
> \ReportManager\Pages\Report.aspx file
> or wherever your SQL serverreportingservicesinstall is located to
> look like the following:
> <%@. Register TagPrefix="MSRS"
> Namespace="Microsoft.ReportingServices.UI"
> Assembly="ReportingServicesWebUserInterface" %>
> <%@. Page language="c#" Codebehind="Report.aspx.cs"
> AutoEventWireup="true"
> Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage"
> EnableEventValidation="false" %>
> <script language="C#" runat="server">
> void Page_Load(object sender, EventArgs e) {
> string ScriptText => "<script language='javascript'>" +
> "var x =document.getElementsByTagName('span'); " +
> "for (i=0;i<x.length;i++) " +
> "{ " +
> "if (x[i].innerText == 'ghostparam') { " +
> "var node = x[i].parentElement; " +
> "if (node) " +
> "node.style.visibility = 'hidden'; " +
> "node = node.nextSibling; " +
> "if (node) " +
> "node.style.visibility = 'hidden'; " +
> "} " +
> "}";
> ScriptText += "<" + (char)47 + "script>";
> Page.RegisterStartupScript("hackit",ScriptText);
> }
> </script>
> Note the AutoEventWireup="true" in the Page Directive has been
> changed, it is set to "false" by default.
> If however you are calling the Web Service directly and not from the
> Report Manager and embedding it in your app the change is even easier,
> just edit the C:\Program Files\Microsoft SQL Server\MSSQL.2\ReportingServices\ReportServer\Pages\ReportViewer.aspx file and add the
> following javascript directly just after the closing </form> tag.
> <script language="javascript">
> var x =document.getElementsByTagName("span");
> for (i=0;i<x.length;i++)
> {
> if (x[i].innerText == "ghostparam") {
> var node = x[i].parentElement;
> if (node)
> node.style.visibility = "hidden";
> node = node.nextSibling;
> if (node)
> node.style.visibility = "hidden";
> }
> }
> </script>
> There may be a better way to write this javascript, and of course it
> WILL BREAK if microsoft changes the way they output the html in a
> future version ofReportingServices, so you will have to modify it
> accordingly,
> but any modifications should be straightforward and simple just find
> the ghostparam textbox in the html and hide it.
> The javascript examples above do rely on you creating a textbox
> parameter called "ghostparam" with a Default Value of Null, and Allow
> Nulls Values set to False and Allow Blanks Set to True. The absence
> of such a parameter will not cause an error, it just means unless
> there is another parameter preventing it, the report will just run
> automatically as usual.
> This should help a few of the more "liberal developers" out there :)
> Al
Thanks for sharing this hack Al, it works great!
Oleksiy|||Hi,
Thanks for the hack. In addition to prevent report from running automatically, It helped me in placing the parameters order. I had 3 parameters and the last 2 parameters I wanted in a line & the hack helped me. Great stuff
From http://www.developmentnow.com/g/115_2007_2_0_0_937186/Hack-to-Prevent-Report-From-Running-Automatically.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com