Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Flow very slow versus Linked Server Expand / Collapse
Author
Message
Posted Thursday, June 14, 2012 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 5, Visits: 247
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
Post #1316108
Posted Thursday, June 14, 2012 2:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 5, Visits: 247
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
Post #1316211
Posted Thursday, June 14, 2012 10:21 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 3,033, Visits: 2,636
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".


Post #1316288
Posted Thursday, June 14, 2012 11:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
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
Post #1316303
Posted Friday, June 15, 2012 12:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:40 PM
Points: 882, Visits: 4,113

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
Post #1316317
Posted Friday, June 15, 2012 5:55 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 3,033, Visits: 2,636
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.



Post #1316527
Posted Friday, June 15, 2012 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 2:26 AM
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!
Post #1316623
Posted Friday, June 15, 2012 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
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


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1316636
Posted Friday, June 15, 2012 7:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 2:26 AM
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


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.
Post #1316640
Posted Friday, June 15, 2012 8:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
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


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
Post #1316654
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse