Here i will post one SSIS performance tip for a day...regularly i will post a performance tip
TO DAY TIP:
Avoid Select *:
The data flow task(DFT) of ssis uses a buffer oriented architecture for data transfer and transformations.when data travels from source to the destination, the data first comes in to buffer, required transformations are done in buffer itself and then written in to the destination.
The size of the buffer is dependent on several factors, one of them is estimated row size. the estimated row size is determined by summing the maximum size of all the columns in the row.so more columns in the row means less number of rows in a buffer and with more buffer requirements the result is performance degradation .Hence it is recommended to select only those columns which are required at destination.
Even if toy need all the columns from the source, you should use the columns name specifically in the select statement other wise it takes another round for the source to gather mete-data about the columns when you are using select *.
TIP: Try to fit as many rows into the buffer which will eventually reduce the number of buffers passing through pipeline engine and improve performance.
go for ssis performence tuing tip 2
TO DAY TIP:
Avoid Select *:
The data flow task(DFT) of ssis uses a buffer oriented architecture for data transfer and transformations.when data travels from source to the destination, the data first comes in to buffer, required transformations are done in buffer itself and then written in to the destination.
The size of the buffer is dependent on several factors, one of them is estimated row size. the estimated row size is determined by summing the maximum size of all the columns in the row.so more columns in the row means less number of rows in a buffer and with more buffer requirements the result is performance degradation .Hence it is recommended to select only those columns which are required at destination.
Even if toy need all the columns from the source, you should use the columns name specifically in the select statement other wise it takes another round for the source to gather mete-data about the columns when you are using select *.
TIP: Try to fit as many rows into the buffer which will eventually reduce the number of buffers passing through pipeline engine and improve performance.
go for ssis performence tuing tip 2
No comments:
Post a Comment