July 13, 2013 at 5:05 am
Hi,
We have daily schedule to move around 20 tables which have huge data to another Database. The issue is The task is taking more time, i.e, around 1.5 hours, which previously it was taking half an hour..
I tried few trial and errors and also change Database Recovery Model to BULKED-LOGGED.
Please suggest how to improve performance..
🙂
July 16, 2013 at 3:05 pm
Hi
If you have indexes on destination tables, please drop them before transfer data and create again after.
Keep Identity - by default this setting is unchecked which means the destination table (if it has an identity column) will create identity values on its own. If you check this setting, the dataflow engine will ensure that the source identity values are preserved and same value is inserted into the destination table.
Keep Nulls - this setting is unchecked which means default value will be inserted during insert into the destination table if NULL value is coming from the source for that particular column. If you check this option then default constraint on the destination table’s column will be ignored and preserved NULL of the source column will be inserted into the destination.
Table Lock - by default this setting is checked and the recommendation is to let it be checked unless the same table is being used by some other process at same time. It specifies a table lock will be acquired on the destination table instead of acquiring multiple row level locks, which could turn into lock problems.
Check Constraints - this setting is checked by default, it's highly recommended to uncheck it if you are sure that the incoming data is not going to violate constraints of the destination table. This setting specifies that the dataflow pipeline engine will validate the incoming data against the constraints of target table. If you uncheck this option it will improve the performance.
Br
Mike
July 16, 2013 at 3:39 pm
What destination you are using?
I recommend using OLEDB destination with fast load.
This will use Bulk Insert which eventually improve performance.
SQLCAT team published really nice paper about SSIS best practises .Worth reading
Hope it helps!
P
July 28, 2013 at 1:43 pm
It's also worth experimenting with the buffer size and rows settings of your Data Flow:
http://msdn.microsoft.com/en-us/library/ms141031(v=sql.105).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply