bulk insert optimization

Hi,
I need to use the BULK INSERT for pushing data into two of the tables
(PK=datetime + int + int). I had read about the optimizations that can be
done for this. But since my tables will keep growing with every inserts (in
GBs), droping & creating the indexes (clustered and non-clustered) itself
can be time comsuming. Are there other options for optimizations?
TIA
AjeyOne thing you can do is to have the file sorted in the same order as the
primary key or clustered index, and then specify the ORDER parameter of BULK
INSERT command. Have you read about it?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ajey" <ajey5@.hotmail.com> wrote in message
news:%235f4bHtsFHA.3080@.TK2MSFTNGP15.phx.gbl...
Hi,
I need to use the BULK INSERT for pushing data into two of the tables
(PK=datetime + int + int). I had read about the optimizations that can be
done for this. But since my tables will keep growing with every inserts (in
GBs), droping & creating the indexes (clustered and non-clustered) itself
can be time comsuming. Are there other options for optimizations?
TIA
Ajey|||Yes.
Instead of bulk inserting the data directly to the target table what i am
planing to do is:
- copy the file to the target sql server
- bulk insert into a temp table (same schema but no indexes)
- insert into the target table from the temp table
will this be efficient?
TIA
- Ajey
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:upHv44tsFHA.3040@.TK2MSFTNGP14.phx.gbl...
> One thing you can do is to have the file sorted in the same order as the
> primary key or clustered index, and then specify the ORDER parameter of
> BULK
> INSERT command. Have you read about it?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:%235f4bHtsFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I need to use the BULK INSERT for pushing data into two of the tables
> (PK=datetime + int + int). I had read about the optimizations that can be
> done for this. But since my tables will keep growing with every inserts
> (in
> GBs), droping & creating the indexes (clustered and non-clustered) itself
> can be time comsuming. Are there other options for optimizations?
> TIA
> Ajey
>
>|||Ajey <ajey5@.hotmail.com> wrote:
> Yes.
> Instead of bulk inserting the data directly to the target table what
> i am planing to do is:
> - copy the file to the target sql server
> - bulk insert into a temp table (same schema but no indexes)
> - insert into the target table from the temp table
> will this be efficient?
You should measure it for a definite answer. Personally I don't believe
this will be faster than a direct bulk load but you never know.
robert
> TIA
> - Ajey
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:upHv44tsFHA.3040@.TK2MSFTNGP14.phx.gbl...

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
>

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
>