Matter73
asked on
SSIS Is so slow...
I am averaging about 1000 records loaded an hour. My software is:
The Windows Server is a Quad Core and has 16 Gig of Ram. This process used to take 4 hours longer, until we moved to this new server, which the 1000 records per hour still stinks. I am not a newby, but am somewhat to SSIS. This is a SQL Server to SQL Server and I feel that If I had done all this with stored procedures and server cursors I would have been better off. Are there any known performance gotchas that I should be aware of. This load time is so rediculous that I must be doing something wrong?
Windows Server 2008 R2 Standard x64
SQL Server 2008 R2 standard x64
The Windows Server is a Quad Core and has 16 Gig of Ram. This process used to take 4 hours longer, until we moved to this new server, which the 1000 records per hour still stinks. I am not a newby, but am somewhat to SSIS. This is a SQL Server to SQL Server and I feel that If I had done all this with stored procedures and server cursors I would have been better off. Are there any known performance gotchas that I should be aware of. This load time is so rediculous that I must be doing something wrong?
Last Comment
Hi,
As @reza_rad said, what component and tasks you're using, so we can direct you to the right way, a nice screen shot of your package will do.
also you could surf that
http://technet.microsoft.com/en-us/library/cc966529.aspx
it'll give you an idea about the various component that SSIS is using , whether it's Non-blocking, Semi-blocking and Fully-blocking components.
As @reza_rad said, what component and tasks you're using, so we can direct you to the right way, a nice screen shot of your package will do.
also you could surf that
http://technet.microsoft.com/en-us/library/cc966529.aspx
it'll give you an idea about the various component that SSIS is using , whether it's Non-blocking, Semi-blocking and Fully-blocking components.
Hi,
If you are not doing a truncate - load, I suggest drop all Index and recreate those after the Load.
If you are not doing a truncate - load, I suggest drop all Index and recreate those after the Load.
ASKER
I have been making heavy use of the foreach loop. I have been reading on the net that the foreach loop was not meant to act as a cursor for datasets. Other's have gone on to say that this would be painfully slow and that this should be implemented with a SQL Server server side cursor outside of SSIS. Please, any thoughts on this subject would be most appriciated.
Note: I have about 5,000 rows and it takes about 5 hours to process....
Note: I have about 5,000 rows and it takes about 5 hours to process....
Hi,
If its such a heavy process, probably you can have a script task handle it. I think that way you will
have the flexibility of using c# and getting it executed the way you want without much hassle.
If its such a heavy process, probably you can have a script task handle it. I think that way you will
have the flexibility of using c# and getting it executed the way you want without much hassle.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
what is your source type?
what is your destination type?
did you used Fast Load in destination?
do you have any transformation between source and destination?