Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SQL Server, T-SQL, BI and more...
Browse by Tags · View All
ssis 26
SSIS 26
SQLServerPedia Syndication 19
Business Intelligence 14
T-SQL 13
ssrs 10
SSRS 10
General 9
SSAS 8
sqlpass 4

Archive · View All
January 2012 14
April 2012 5
March 2012 5
February 2012 5
November 2012 3
October 2012 3
June 2012 3
May 2012 3
August 2012 2
July 2012 2

Sam Vanga's Blog

Simple Tips To Speed Up Your SSIS Data Flow

Apr 10 2012 12:00AM by Samuel Vanga   

There are a few design choices that can make your SSIS packages run super fast when loading data from table A to table B.

SQL Command at Source

OLE DB source editor allows you to choose either a table or a view from a drop down if data access mode is Table or View. This will execute a SELECT * command on the data source. As a best practice, it is always better to list the columns even when you are selecting all of them. Choose SQL Command and type in the query.

SQL Command Data Access Mode

Table or View – Fast Load at Destination

As the name suggests, Table or View – Fast Load is the fastest way to load data to destination. It applies a table lock on the destination table and performs bulk insert. It is in arguably the fastest way to insert data.

My package on a machine with 8GB RAM and 4 processors ran for 58 seconds to load 7.9 million records. Compare this to 50 minutes when using Table or View as the data access mode. This option works similar to a cursor – inserts one record at a time. Clear evidence for it’s poor performance.

imageimage

50 minutes? hell No! But 58 seconds to load about 8 million records isn’t bad. Right?

Let’s dig a little deeper to see if we can tune this even more.

Default Buffer

SSIS relies heavily on buffer. A set of records are read into the buffer, and from buffer they are written to the destination. This process continues until all rows are written to destination. For instance, in this example, buffer is carrying 9,216 rows at a time. You can see that by using data viewers.

image

This number is controlled by two properties DefaultMaxBufferRows and DefaultMaxBufferSize. The values are 10,000 rows and 10MB by default, whichever comes first. MaxBufferSize is 100MB – it’s the maximum size of rows that can be fitted in buffer.

EstimatedRowSize is another property that is calculated based on the metadata of our result. This is 32 bytes for my data set. (BIGINT = 8B + INT = 4B + DATETIME = 8B + INT = 4B + MONEY = 8B) = 32 Bytes.

image

Buffer reached the default maximum allowed with 9,216 rows, that amounts to a size of 9,216 (rows) * 32 (bytes for each row) = 294912 Bytes which is less than 1MB, remember maximum is 100MB. There is a lot of free space left on the buffer that can be used. Filling this will result in shorten trips thus increasing performance.

For this test, I left DefaultMaxBufferSize as 10MB, but increased DefaultMaxBufferRows to 30,000 rows. The package now runs in less than 30 seconds.

image

Changing the settings to DefaultMaxBufferSize = 90MB and DefaultMaxBufferRows = 60,000 rows resulted in the package to execute in 15 seconds.

Wrap Up…

Understanding the internals sometimes will yield great performance without the need for additional hardware. This package is running on my local machine and data is being moved within the same database. Off course results might vary depending on your environment.

~Sam.



Republished from Sam Vanga's Blog [16 clicks].  Read the original version here [2 clicks].

Samuel Vanga
549 · 0% · 68
4
 
0
Lifesaver
 
0
Refreshed
 
1
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Simple Tips To Speed Up Your SSIS Data Flow" rated 5 out of 5 by 4 readers
Simple Tips To Speed Up Your SSIS Data Flow , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]