Very slow insert using ssis package

Question

Very slow insert using ssis package

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
February 24, 2011 at 10:57 am

#388931

I count not figure out the issue that why Insert in one particular table is very slow. I use the same ssis package for other table and it did inserted 45 million rows in 10 Minutes but for one specific table it is inserting 1.5 million rows in one hour.
Source and destination tables are on the same server. There is no trigger and no indexes on this table.
Any idea ?

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

balbirsinghsodhi (2/24/2011)
I count not figure out the issue that why Insert in one particular table is very slow. I use the same ssis package for other table and it did inserted 45 million rows in 10 Minutes but for one specific table it is inserting 1.5 million rows in one hour.
Source and destination tables are on the same server. There is no trigger and no indexes on this table.
Any idea ?

What are the destination adaptors in SSIS? Did you use the SQL Server Destination or the OLE DB Destination with the fast load option.

You should make sure that there is some sort of bulk insert into the tables, otherwise the inserts are row by row.

Furthermore, check the recovery model of the databases. Simply versus bulk-logged versus full recovery means a whole lot of difference in logging, which can slow down insert operations.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 2

And don't forget the possibility out-of-date statistics. While most people only think about them when it comes time to read, my opinion is that bad stats can also affect inserts (if I'm wrong, someone will be around shortly to correct me).

Have you tried doing a straight insert in SSMS just to see what the execution plan is? And if the bottleneck shows there as well?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 3

Yes, I was not using the fast load option. That is why it is inserting row by row.

Thanks guys..

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 4

Glad you got it working.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.