Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anish89
Starting Member
4 Posts |
 Posted - 2013-01-07 : 00:41:36
|
| Hi,I am new to SSIS. I have a very basic question regarding bulk loading.My aim to find a method to speed up the bulk loading process.My situation is very simple.I have a flat-file which is of the size 934 MB (when inserted table contains 4423763 rows). I am suppose to load this flat-file data into a table(containing around 60 columns) without any transformation. I loaded this using 3 methods:BCP utility - 2 hrs 51 minsT-sql bulk insert query - 2 hrs 10 minsSSIS bulk insert task with tablock - 01 hr 44 minsI know the time taken for the loading process is quite abnormal.Please suggest some tips or techniques to speed up this process.Thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-07 : 07:07:24
|
| Remove indexes from the table. Make sure the database is bulk logged or simple.Even for that you have less than 1GB of data - are you inserting from a remote location? If so it is probably the network that is the issue - copy the file somewhere more local first.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
anish89
Starting Member
4 Posts |
Posted - 2013-01-07 : 08:13:21
|
| nigelrivett,Ok let me try with disabled indexes.Pardon my ignorance. Whats the difference between bulk logged & simple databases? Which is best for bulk loading? How can i switch between them?No, i am inserting it from local source. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-01-07 : 08:16:52
|
| Disabling indexes should suffice. Re enable if necessary. Also , doublecheck your transaction log files are on a separate disks.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-07 : 08:23:20
|
| As long as it is not using full recovery model you should be ok.Simple truncates the log on checkpoint so you don't have to do tr log backups.Bulk logged allows bulk operations with very little logging but still allows log backups.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
anish89
Starting Member
4 Posts |
Posted - 2013-01-08 : 06:06:02
|
| Thanks. i ll get back to you after trying this |
|
|
|
anish89
Starting Member
4 Posts |
Posted - 2013-01-16 : 00:17:20
|
| Hi,When i disabled the clustered primary key of a table, the foreign key constraints on the tables that are referencing the disabled primary key are disabled as well. When i enable back that clustered primary key, the foreign key constraints still remain disabled.What do i do? |
|
|
|
|
|
|
Fastest method for bulk loading?