Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Flow very slow versus Linked Server


Data Flow very slow versus Linked Server

Author
Message
gjackson-671398
gjackson-671398
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 324
Hi,
I have tried the Import wizard to import data from a view on a separate SQL server. This took approximately 1 hour to load 168,261 rows.When I used a linked server and a Select into statement it took 10 seconds. This is really baffling to me, I thought SSIS would be at least equal to the linked server or a bit faster. No matter how I adjust DefaultBufferSize or BufferMaxRows the elapsed time is one hour. Can someone kindly explain this or provide a suggestion to improve performance to the SSIS data flow?

Thanks,
Greg
gjackson-671398
gjackson-671398
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 324
Update - I used the ADO data flows and it brought the time down to 2 minutes versus 1 hour. Not sure what the difference is between OLE DB and the ADO data flow but it made a huge difference in elapsed time.

Thanks,
Greg
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3566 Visits: 3110
I suspect that the performance problem is because the insert destination is committing each row as it goes. Check the insert destination- the data access mode should be something like 'Table or View - Fast Load".



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8263 Visits: 14368
Also, in general, an OLE DB destination will be faster than an ADO.NET destination. So, I would switch back to OLE DB and do as happycat suggests and make sure you're using fastload.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
crazy4sql
crazy4sql
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 4426

This took approximately 1 hour to load 168,261 rows.When I used a linked server and a Select into statement it took 10 seconds.


This should not be the case. This difference is quiet huge. Are you sure both activity is doing the same thing.

----------
Ashish
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3566 Visits: 3110
crazy4sql (6/15/2012)

This took approximately 1 hour to load 168,261 rows.When I used a linked server and a Select into statement it took 10 seconds.


This should not be the case. This difference is quiet huge. Are you sure both activity is doing the same thing.


It doesn't surprise me at all. The original SSIS package was, most likely, doing 168,261 commits, whereas the SELECT INTO using the linked server only does a single commit. That is a huge difference in the processing required. The change I suggested to the SSIS package is intended to change the default behaviour of the ole db destination transformation to be similar to the SELECT INTO - although you actually have some control on how often the it commits.



akin.akinwumi
akin.akinwumi
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 47
Your problem may be due to the way your database servers was set up and ssis performing row-by-row inserts vs mass insert using a sql query. Now what about a hybrid solution here?

Copy your linked server query into an 'Execute SQL' task in SSIS. That way you get the best of both worlds!;-)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8263 Visits: 14368
akin.akinwumi (6/15/2012)
Copy your linked server query into an 'Execute SQL' task in SSIS. That way you get the best of both worlds!;-)

In my opinion that is actually the worst of both worlds. You're bypassing the valuable functionality built into SSIS for data movement while still allowing SQL Server to SQL Server communication. Sorry, but it is hard to imagine a worse decision being made in this scenario Sick

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
akin.akinwumi
akin.akinwumi
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 47
opc.three (6/15/2012)
akin.akinwumi (6/15/2012)
Copy your linked server query into an 'Execute SQL' task in SSIS. That way you get the best of both worlds!;-)

In my opinion that is actually the worst of both worlds. You're bypassing the valuable functionality built into SSIS for data movement while still allowing SQL Server to SQL Server communication. Sorry, but it is hard to imagine a worse decision being made in this scenario Sick


We will have to agree to disagree on that point. In some scenarios based on how the database servers are set up; a linked query will produce better performance that SSIS data flow task! So if you still intend to use the performance gain of the linked query but still require that the data flow matched with your ssis package architecture? Well, SSIS provides the functionality to do that.

Use a 'EXECUTE SQL' task. ;-)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8263 Visits: 14368
akin.akinwumi (6/15/2012)
opc.three (6/15/2012)
akin.akinwumi (6/15/2012)
Copy your linked server query into an 'Execute SQL' task in SSIS. That way you get the best of both worlds!;-)

In my opinion that is actually the worst of both worlds. You're bypassing the valuable functionality built into SSIS for data movement while still allowing SQL Server to SQL Server communication. Sorry, but it is hard to imagine a worse decision being made in this scenario Sick


We will have to agree to disagree on that point. In some scenarios based on how the database servers are set up; a linked query will produce better performance that SSIS data flow task! So if you still intend to use the performance gain of the linked query but still require that the data flow matched with your ssis package architecture? Well, SSIS provides the functionality to do that.

Use a 'EXECUTE SQL' task. ;-)

All things being equal, even if you could not get an SSIS package to perform as well as a Linked Server setup (which I doubt) having SSIS push a process that does SQL Server to SQL Server communications in this manner is a ridiculous design pattern. Think about the debug scenarios and the environment maintenance required to support your proposed architecture. It is in the running for my last resort option.

You're right about two things. 1. It depends, but only because that is almost always true. 2. I disagree with you.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search