ssis loads slower than sql server

Question

ssis loads slower than sql server

scottichrosaviakosmos

SSCommitted

Points: 1889
More actions
August 28, 2017 at 3:52 am

#323203

I have a view which uses a select query(with lot of transformation) from a huge table. when I use ssis to load table from this view it takes 5-6 mins for the load but take only 1 min when i use sql insert .
Is there any way that we can load the data in same time as sql insert do?
(I am using oledb source and destination for load).

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tom_Hogan Hall of Fame Points: 3903 More actions · Answer 1

What option are you using for the data access drop down? There are performance issues if you pick either of the "Table or view" options (I forget the specifics as to why). You want to choose SQL command and put your SELECT query in the text box. Don't do SELECT *, specify which columns you need from your source.

scottichrosaviakosmos SSCommitted Points: 1889 More actions · Answer 2

Thanks.
I am using select columns (not *) and using sql command.
I tried to load data in raw file to see if its works fine. the load into raw file is fast(obviously) but still no match to sql insert .
one more thing to point is that the ssis package is running on local machine while sql query on server(network io overhead).
Still I am trying to look for some alternative to atleast reach near to sql(but it seems no way).

Phil Parkin SSC Guru Points: 246978 More actions · Answer 3

scottichrosaviakosmos - Monday, August 28, 2017 7:57 AM
Thanks.
I am using select columns (not *) and using sql command.
I tried to load data in raw file to see if its works fine. the load into raw file is fast(obviously) but still no match to sql insert .
one more thing to point is that the ssis package is running on local machine while sql query on server(network io overhead).
Still I am trying to look for some alternative to atleast reach near to sql(but it seems no way).

So can you try running the package on the server instead, to remove the potential bandwidth issue?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

scottichrosaviakosmos SSCommitted Points: 1889 More actions · Answer 4

Phil Parkin - Monday, August 28, 2017 8:08 AM
scottichrosaviakosmos - Monday, August 28, 2017 7:57 AM
Thanks.
I am using select columns (not *) and using sql command.
I tried to load data in raw file to see if its works fine. the load into raw file is fast(obviously) but still no match to sql insert .
one more thing to point is that the ssis package is running on local machine while sql query on server(network io overhead).
Still I am trying to look for some alternative to atleast reach near to sql(but it seems no way).
So can you try running the package on the server instead, to remove the potential bandwidth issue?

Thanks again.
I tried to run the ssis package on server but same result. ssis server and database are located on 2 different servers.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 5

If your source and destination tables are both on the same machine (which your initial post suggests), it would probably be a better idea to write a stored proc to move the data and to call that from your SSIS package.
Otherwise the data is going to your SSIS server and then back again, and maybe this is the reason for the additional time taken.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel