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.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Fastest method for bulk loading?

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 mins
T-sql bulk insert query - 2 hrs 10 mins
SSIS bulk insert task with tablock - 01 hr 44 mins

I 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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

anish89
Starting Member

4 Posts

Posted - 2013-01-08 : 06:06:02
Thanks. i ll get back to you after trying this
Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -