Friday, February 24, 2012

Bulk Import Large Datafile

Just wonding if there is any optimization that can be done to import a large
file faster into SQL Server. Am using BULK INSERT with a rowsize of 100000.
The text file for the import is 159gig so this a big one. Any tips are
appreciated.
TIA> Just wonding if there is any optimization that can be done to import a
large
> file faster into SQL Server. Am using BULK INSERT with a rowsize of
100000.
> The text file for the import is 159gig so this a big one. Any tips are
> appreciated.
You can use bulk insert with a tablock. This way you could load the file
parallelly using multiple bulk loads, you could set the batch size or split
up the text file into multiple files.
Remeber to drop indexes on target table for performance.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi.
three four things to keep in mind
first put the database in bulk-logged mode ( if logs are not atall
important - make 'trunc. log on chkpt' to true)
second turn off auto update statistics/ auto create statistics
drop indexes
restrict access to dbo
also if u can split this text file in to n number of txt files. u can start
multiple bul inserts parallely.
Enjoy SQL
Regards,
Mayur
"DWinter" <dwinter@.attbi.com> wrote in message
news:%23eo8Ehn7DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Just wonding if there is any optimization that can be done to import a
large
> file faster into SQL Server. Am using BULK INSERT with a rowsize of
100000.
> The text file for the import is 159gig so this a big one. Any tips are
> appreciated.
> TIA
>

No comments:

Post a Comment