question

GayanDasanayake-3000 avatar image
0 Votes"
GayanDasanayake-3000 asked ZoeHui-MSFT commented

Bulk Insert to SQL Server gradually slows down

We are doing a basic ETL from Oracle to SQL Server using SSIS (Using a script component).

It is a basic read and bulk insert.

The insert is done in batches of 10,000.

When doing an ETL of 17million rows, we observe a steady decline in performance.
First batch taking 2 seconds but the last taking 132.

Any areas to look for solving?

Thanks

126249-image.png


sql-server-generalsql-server-integration-services
image.png (20.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The actual script part...

126322-image.png


0 Votes 0 ·
image.png (65.3 KiB)

@GayanDasanayake-3000,

Few basic questions to learn about your environment:

  • What is your SQL Server/SSIS run-time environment version?

  • What are the reasons to use a custom made SSIS Script Component instead of built-in SSIS Source and Destination Adapters?

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

What is your table definition?

It is almost certainly due to updating indexes in batches of 10,000. As your table/indexes grow, it takes longer to update.

10,000 is a very small batch. Try 100,000 to 500,000 row batch size.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Tom mentioned indexes. Having the clustered index in place can help - at least if the data loaded is being aligned to it. But it is much better to create non-clustered indexes until after the load has completed.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @GayanDasanayake-3000,

It could be many problems. See here for MSDN recommendations on optimizing bulk inserts. Try limiting the batches to say 10,000 or 100,000 rows and see how that goes. If it helps a lot then you should spend some time to figure out your best batch size.

From SSIS side, here is a similar issue you may refer this to see if it will be helpful.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GayanDasanayake-3000 avatar image
0 Votes"
GayanDasanayake-3000 answered ZoeHui-MSFT commented

We have found that when the Oracle oledb driver was changed from version 19.1 to 12.1 the issue was fixed.
However, the batch size and index-related optimizations would still improve I assume, which we are investigating further.
Thanks to all who helped.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GayanDasanayake-3000,

Glad to hear that your issue has been resolved, you could mark it as answer so other user with similar problem could see this easier. :)

0 Votes 0 ·