SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Fastest method for bulk loading?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anish89
Starting Member

India
4 Posts

Posted - 01/07/2013 :  00:41:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  07:07:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

India
4 Posts

Posted - 01/07/2013 :  08:13:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2019 Posts

Posted - 01/07/2013 :  08:16:52  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  08:23:20  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

India
4 Posts

Posted - 01/08/2013 :  06:06:02  Show Profile  Reply with Quote
Thanks. i ll get back to you after trying this
Go to Top of Page

anish89
Starting Member

India
4 Posts

Posted - 01/16/2013 :  00:17:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000