Reduce execution time

Question

Reduce execution time

patla4u

SSCrazy

Points: 2103
More actions
May 19, 2011 at 9:44 am

#240631

I have ssis package and in SSIs package ,,
I used truncate table and then data flow task.
In data flow task,,
I use flat file as source ,derived columna and oledb destionation.
I have only 15 columns and 700,000 records.
It will take 45 min,
Please can i know , how i can reduce time.
make any change in propery ???
Need to reduce execution time
Thanks
L

Viewing 7 posts - 1 through 6 (of 6 total)

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

heb1014 Hall of Fame Points: 3790 More actions · Answer 1

I'm certainly not an expert in SSIS, but if you have allot of indexes on the table you might want to try disabling them.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

The article list below mentions of number of steps that you can take to speed up execution time:

http://henkvandervalk.com/speeding-up-ssis-bulk-inserts-into-sql-server

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

patla4u SSCrazy Points: 2103 More actions · Answer 3

Thanks

I have used fast load in OLE DB destination and it;s very fast. It will take only 2 min.

Can i know what is exact differece between (Tables and view)

and (Table and views- fast load).

which is one better in specific area??

(I am note sure so I am asking like Is there any effect with data loading or may i lost data ??)

SanDroid SSChampion Points: 10068 More actions · Answer 4

patla4u (5/19/2011)
Thanks
I have used fast load in OLE DB destination and it;s very fast. It will take only 2 min.
Can i know what is exact differece between (Tables and view)
and (Table and views- fast load).
which is one better in specific area??
(I am note sure so I am asking like Is there any effect with data loading or may i lost data ??)

Fast Load will not cause data loss. Fast Load Inserts the source data into the data Target using larger chunks. It is better for most ETL.

For partial inserts that do not roll back on failure turn off Fast Load.

You should try to never partial load data in a well written SSIS package.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

patla4u (5/19/2011)
Can i know what is exact differece between (Tables and view)
and (Table and views- fast load).
which is one better in specific area??
(I am note sure so I am asking like Is there any effect with data loading or may i lost data ??)

A Table is a physical object and unlike a View it stores Data.

A View is a catalogued SELECT Statement.

A view can be created from one or more tables by the use of Joins

Views may created to hide some columns from the user for security reasons

Whether you want to use a view or a table depends on the situation.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Some additional options that you can set to speed up the load:

http://www.sqllion.com/2009/04/faster-extraction-loading-by-ssis

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/48e3bfea-54dd-47fc-bbe7-6f2241aa8de3

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/