What is the best way to merge two really large tables (7,000,000 rows each,
and very wide)?
The table definitions are the same for both tables.
Right now I am using a insert into statement with a selection from one table
at a time, but it takes way too long. I don't need it to be logged. A all
or nothing result is fine for me. I don't think DTS is an option because I
need to run this from a C# app. I think my only other option is using the
BCP api to select the data and load it into the new table, but this just
seems like the wrong way to go.
Any other ways to go with this?
ShawnSelect *
into [NewTable]
From
Select * [Table1]
Union All
Select * [Table2]
"Shawn Meyer" <me@.me.com> wrote in message
news:O4GhS6QQFHA.580@.TK2MSFTNGP15.phx.gbl...
> What is the best way to merge two really large tables (7,000,000 rows
> each,
> and very wide)?
> The table definitions are the same for both tables.
> Right now I am using a insert into statement with a selection from one
> table
> at a time, but it takes way too long. I don't need it to be logged. A all
> or nothing result is fine for me. I don't think DTS is an option because
> I
> need to run this from a C# app. I think my only other option is using the
> BCP api to select the data and load it into the new table, but this just
> seems like the wrong way to go.
> Any other ways to go with this?
> Shawn
>
Friday, March 30, 2012
Merge Join's poor performance
Hello All,
I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.
Maybe someone had a little more luck with it?
Liran R wrote:
Hello All,
I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.Maybe someone had a little more luck with it?
If I were you I would use the OLE DB Source component to do the join - there is absolutely nothing wrong with doing that. If you have a super-performant relational databsae engine at your disposal - why not use it?
Donald Farmer talks around this a little in his OVAL webcast. If you only watch one SSIS webcast in your life then it should be this one.
Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)
-Jamie
|||Thanks, I'll take a look. does the merge join takes place in the cache?|||Can you elaborate as to what you mean by "the cache"?
-Jamie
|||
Sure. When I'm using a Lookup component to make the join, The lookup table is been cached by default. When I want to join to a large table (with several millions) I understood It's best practice to use the Merge Join Component, but as I see also this component caches the records in the machines memory, so I don't understand the benefit...
|||There's a different sort of memory usage going on here.
Yes, the LOOKUP has an area of memory that we call the cache and is used for storing a lookup set.
MERGE JOIN stores data in memory (as do all asynchronous components) but its a different kind of memory and we don't refer to it as a cache. it is just the component's working area - more commonly termed a buffer. Also, this working area will change as MERGE JOIN does its work whereas the LOOKUP cache is static.
It is also worth saying that the MERGE JOIN can spool data to disk if it s running out of memory. LOOKUP cannot do that with its cache.
-Jamie
|||
Hi SSIS friend,
I remember asking a similar question a couple of months back. I think I had 15+ Merge Join components in a test package and the performance was awful. Jamie's advice then and now is pretty sound.
During my short period experimenting with SSIS, I came to realise that in order to create efficient packages, I had to utilise the power of both the SQL Server and SSIS engines. Each one is good at performing certain tasks better than the other. It takes time and I'm still learning, but the more you play around with it, the easier it gets to choose which engine should be used.
|||Thanks guys.
sqlMerge join: nr of output rows unchanged when amount of input changes
Dear all,
I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.
Situation as follows.
The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.
The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.
The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.
Then both flows are merged and other steps are performed.
If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.
When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.
Any help will be greatly appreciated.
Kind regards,
Albert.
If you could post the full error output here, that would probably be helpful. Sometimes it is the "big view" that helps point you at the cause of the problem, especially since many of SSIS's error messages are not particularly transparent. The earlier errors often show what triggered the later errors, even if they do not appear directly related.
|||Can you also include details of the next task in the pipeline, the one that accepts the ~9000 rows. It sounds like it's failing on the first buffer it receives as input.|||Ok, for the big picture: I feel like a fool.
Solving one of the other errors solved the reported issue as well.
Apologies for bothering you.
Merge Join's poor performance
Hello All,
I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.
Maybe someone had a little more luck with it?
Liran R wrote:
Hello All,
I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.Maybe someone had a little more luck with it?
If I were you I would use the OLE DB Source component to do the join - there is absolutely nothing wrong with doing that. If you have a super-performant relational databsae engine at your disposal - why not use it?
Donald Farmer talks around this a little in his OVAL webcast. If you only watch one SSIS webcast in your life then it should be this one.
Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)
-Jamie
|||Thanks, I'll take a look. does the merge join takes place in the cache?|||Can you elaborate as to what you mean by "the cache"?
-Jamie
|||Sure. When I'm using a Lookup component to make the join, The lookup table is been cached by default. When I want to join to a large table (with several millions) I understood It's best practice to use the Merge Join Component, but as I see also this component caches the records in the machines memory, so I don't understand the benefit...
|||There's a different sort of memory usage going on here.
Yes, the LOOKUP has an area of memory that we call the cache and is used for storing a lookup set.
MERGE JOIN stores data in memory (as do all asynchronous components) but its a different kind of memory and we don't refer to it as a cache. it is just the component's working area - more commonly termed a buffer. Also, this working area will change as MERGE JOIN does its work whereas the LOOKUP cache is static.
It is also worth saying that the MERGE JOIN can spool data to disk if it s running out of memory. LOOKUP cannot do that with its cache.
-Jamie
|||Hi SSIS friend,
I remember asking a similar question a couple of months back. I think I had 15+ Merge Join components in a test package and the performance was awful. Jamie's advice then and now is pretty sound.
During my short period experimenting with SSIS, I came to realise that in order to create efficient packages, I had to utilise the power of both the SQL Server and SSIS engines. Each one is good at performing certain tasks better than the other. It takes time and I'm still learning, but the more you play around with it, the easier it gets to choose which engine should be used.
|||Thanks guys.
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
Subscribe to:
Posts (Atom)