Merge join with empty inputs

Hi all,

Does anyone have suggestions for ways to deal with the chance that a merge join might receive empty inputs?

I've noticed that when this happens the transformation seems to hang. I changed the MaxBuffersPerInput to zero and this seems to cure the problem but I'm not sure it's the best way to deal with it.

Would it be a good idea to test the row counts with a conditional split before such a join?

Cheers,

Andrew

Are you certain that both inputs are generating EORs when they are complete. If so, then this is likely a bug. If not, then there is no way for the MergeJoin to know that it won't be getting buffers so it is not hung it is just waiting for pending input.

HTH,

Matt

|||

I'm pretty sure they did when I ran the package in a cmd window.

Presumably they did after I changed the MaxBuffersPerInput to fix the problem and the data hadn't changed between runs.

I'll need to recreate the state to confirm this which will take a while.

Andrew

|||

Finally got the problem to recur.

In this case it's a simple Inner Join on one column. The left input has no rows coming in, the right has 582 as shown below.

DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Left Input" (1627)
End DataFlow
DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) will receive 582 rows on input "Merge Join Right Input" (1628)
End DataFlow
DataFlow: 2006-07-11 16:06:51.10
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Right Input" (1628)
End DataFlow

The package runs in BIDS but hangs with the above as the last output when run through DTEXEC. I also tried switching the left/right inputs just in case but the result was the same.

Any suggestions for a clean way to handle this? I'm trying to work out a consistent way to handle merge joins to deal with this sort of situation.

Andrew

|||

Looks like this may have been resolved by a hotfix:

FIX: When you call a SQL Server 2005 Integration Services package from a SQL Server Agent job step, the package may stop responding

CAUSE: This problem occurs when the SSIS package contains a Merge Join transformation.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B928243&sd=rss&spid=2855

|||

Did you install the hotfix? Or did you continue to use the 0 MaxInputBuffers property? I just discovered this issues in one of my packages and I'm just curious if that hotfix did fix the problem. The description is just really vuage on the hotfix.

Merge join with empty inputs

Hi all,

Does anyone have suggestions for ways to deal with the chance that a merge join might receive empty inputs?

I've noticed that when this happens the transformation seems to hang. I changed the MaxBuffersPerInput to zero and this seems to cure the problem but I'm not sure it's the best way to deal with it.

Would it be a good idea to test the row counts with a conditional split before such a join?

Cheers,

Andrew

Are you certain that both inputs are generating EORs when they are complete. If so, then this is likely a bug. If not, then there is no way for the MergeJoin to know that it won't be getting buffers so it is not hung it is just waiting for pending input.

HTH,

Matt

|||

I'm pretty sure they did when I ran the package in a cmd window.

Presumably they did after I changed the MaxBuffersPerInput to fix the problem and the data hadn't changed between runs.

I'll need to recreate the state to confirm this which will take a while.

Andrew

|||

Finally got the problem to recur.

In this case it's a simple Inner Join on one column. The left input has no rows coming in, the right has 582 as shown below.

DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Left Input" (1627)
End DataFlow
DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) will receive 582 rows on input "Merge Join Right Input" (1628)
End DataFlow
DataFlow: 2006-07-11 16:06:51.10
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Right Input" (1628)
End DataFlow

The package runs in BIDS but hangs with the above as the last output when run through DTEXEC. I also tried switching the left/right inputs just in case but the result was the same.

Any suggestions for a clean way to handle this? I'm trying to work out a consistent way to handle merge joins to deal with this sort of situation.

Andrew

|||

Looks like this may have been resolved by a hotfix:

FIX: When you call a SQL Server 2005 Integration Services package from a SQL Server Agent job step, the package may stop responding

CAUSE: This problem occurs when the SSIS package contains a Merge Join transformation.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B928243&sd=rss&spid=2855

|||

Did you install the hotfix? Or did you continue to use the 0 MaxInputBuffers property? I just discovered this issues in one of my packages and I'm just curious if that hotfix did fix the problem. The description is just really vuage on the hotfix.

Merge join with empty inputs

Hi all,

Does anyone have suggestions for ways to deal with the chance that a merge join might receive empty inputs?

I've noticed that when this happens the transformation seems to hang. I changed the MaxBuffersPerInput to zero and this seems to cure the problem but I'm not sure it's the best way to deal with it.

Would it be a good idea to test the row counts with a conditional split before such a join?

Cheers,

Andrew

Are you certain that both inputs are generating EORs when they are complete. If so, then this is likely a bug. If not, then there is no way for the MergeJoin to know that it won't be getting buffers so it is not hung it is just waiting for pending input.

HTH,

Matt

|||

I'm pretty sure they did when I ran the package in a cmd window.

Presumably they did after I changed the MaxBuffersPerInput to fix the problem and the data hadn't changed between runs.

I'll need to recreate the state to confirm this which will take a while.

Andrew

|||

Finally got the problem to recur.

In this case it's a simple Inner Join on one column. The left input has no rows coming in, the right has 582 as shown below.

DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Left Input" (1627)
End DataFlow
DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) will receive 582 rows on input "Merge Join Right Input" (1628)
End DataFlow
DataFlow: 2006-07-11 16:06:51.10
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Right Input" (1628)
End DataFlow

The package runs in BIDS but hangs with the above as the last output when run through DTEXEC. I also tried switching the left/right inputs just in case but the result was the same.

Any suggestions for a clean way to handle this? I'm trying to work out a consistent way to handle merge joins to deal with this sort of situation.

Andrew

|||

Looks like this may have been resolved by a hotfix:

FIX: When you call a SQL Server 2005 Integration Services package from a SQL Server Agent job step, the package may stop responding

CAUSE: This problem occurs when the SSIS package contains a Merge Join transformation.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B928243&sd=rss&spid=2855

|||

Did you install the hotfix? Or did you continue to use the 0 MaxInputBuffers property? I just discovered this issues in one of my packages and I'm just curious if that hotfix did fix the problem. The description is just really vuage on the hotfix.

sql

Merge Join

I am trying to understand mege joins. In BOL it reads "The merge join
requires that both inputs be sorted on the merge columns, which are defined
by the equality (WHERE) clauses of the join predicate."
What does it mean "both inputs must be sorted"?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
> What does it mean "both inputs must be sorted"?
That you on each table either have a clustered index on the columns over which you do the join, or a
non-clustered index that covers the data that is needed from the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:5d9898960f213@.uwe...
>I am trying to understand mege joins. In BOL it reads "The merge join
> requires that both inputs be sorted on the merge columns, which are defined
> by the equality (WHERE) clauses of the join predicate."
> What does it mean "both inputs must be sorted"?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1

Merge Join

I am trying to understand mege joins. In BOL it reads "The merge join
requires that both inputs be sorted on the merge columns, which are defined
by the equality (WHERE) clauses of the join predicate."
What does it mean "both inputs must be sorted"?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1> What does it mean "both inputs must be sorted"?
That you on each table either have a clustered index on the columns over whi
ch you do the join, or a
non-clustered index that covers the data that is needed from the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:5d9898960f213@.uwe...[vbcol
=seagreen]
>I am trying to understand mege joins. In BOL it reads "The merge join
> requires that both inputs be sorted on the merge columns, which are define
d
> by the equality (WHERE) clauses of the join predicate."
> What does it mean "both inputs must be sorted"?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1[/vbcol]sql

Merge Join

Hello all,

I have a package where I use merge join for two sorted inputs and the output is stored in a raw file.

In another package, the raw file from above package is again merge joined with another sorted input. Now my question is....do we need to sort again the raw file from first package? or is it OK to set the isSorted property to True and define the sort keys?

Thank you.

As long as you know the input is sorted, you can use the issorted property.

If it turns out not to be sorted, you could miss joins.

BobP

|||

Thank you for the reply...

So here in my case I am using a left join (in merge join)....how would I know if the raw file output from first package i sorted or not?

There is no problem to sort it again but really takes a lot of time(as the size is too big).

|||

If you are sorting it and then writing it out, it should stay sorted.

BobP

|||Ok....if it stays sorted then OK.......many Thanks

Merge Join

I am trying to understand mege joins. In BOL it reads "The merge join
requires that both inputs be sorted on the merge columns, which are defined
by the equality (WHERE) clauses of the join predicate."
What does it mean "both inputs must be sorted"?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1> What does it mean "both inputs must be sorted"?
That you on each table either have a clustered index on the columns over which you do the join, or a
non-clustered index that covers the data that is needed from the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5d9898960f213@.uwe...
>I am trying to understand mege joins. In BOL it reads "The merge join
> requires that both inputs be sorted on the merge columns, which are defined
> by the equality (WHERE) clauses of the join predicate."
> What does it mean "both inputs must be sorted"?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1