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.|||I would consider turning off 'auto create stats' and 'auto
update stats' on your database before performing any
import. Then when the import has completed - run update
statistics manually...
I've seen this have a significant impact on some bulk
loads in the past - but you really need to test this
option to see how much it is applicable to your situation.
>--Original Message--
>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
>
>.
>|||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
>