Wednesday, March 28, 2012
Optimizing Bulk Insert
have found from tests is:
1. I need to have the batch size set to a minimum of 4% of the source file
(otherwise the command just quits with no message).
2. I need to have a clustered index and in the bulk insert command, use the
ORDER clause with the same order as the clustered index.
3. I'm not sure if a format file makes this quicker but I am using on.
Please let me know if my findings are incorrect and if there are any other
things that I could be doing to make this go any faster.
Any help would be appreciated.
EllieCheck out this link:
http://www.mssqlcity.com/Tips/bulk_...ptimization.htm
"Ellie" <nospam@.nospam.net> wrote in message
news:ewfl2ibhGHA.5096@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the quickest way to have a bulk insert completed. What
>I have found from tests is:
> 1. I need to have the batch size set to a minimum of 4% of the source file
> (otherwise the command just quits with no message).
> 2. I need to have a clustered index and in the bulk insert command, use
> the ORDER clause with the same order as the clustered index.
> 3. I'm not sure if a format file makes this quicker but I am using on.
> Please let me know if my findings are incorrect and if there are any other
> things that I could be doing to make this go any faster.
> Any help would be appreciated.
> Ellie
>|||Ellie (nospam@.nospam.net) writes:
> 3. I'm not sure if a format file makes this quicker but I am using on.
I believe the fastest format is native format. But that requires of
course that the data comes from a source that can produce native format.
In essence, another SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Wednesday, March 21, 2012
Optimizations
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
Optimizations
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
Drew
Drew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of the
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless the
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexes
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
sql
Optimizations
doing a bulk insert from an odbc data source. If these tables have indexes
defined, will the bulk insert step make use of these? The reason I ask is
because I wasn't sure if it is worth performing optimizations (maintenance
plan) immediately after the insert. Any help is appreciated.
--
DrewDrew,
If the data to be imported is huge (a lot of rows), then it is better to
drop the indexes and recreate them after the bulk operation. Remember,
indexes are updates for each row inserted, updated or deleted.
AMB
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Depending on the amount of data being inserted, its probably better to drop
the indexes and recreate them after the bulk insert, especially if one of th
e
indexes is a clustered index.
Bulk insert is not helped by indexes of any kind, but it sure can be
hindered by them. When the table is truncated, all the pointers in the
indexes are worthless. When the new data comes in, the clustered index (if
exists) has to manuver each row into the same order on the pages as the
clustered index is configured. this will slow down the bulk insert unless th
e
data is already in the same order.
if the data set is small, then just load the data and do a dbcc indexdefrag
to reindex the table.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Bulk insert can be faster if you drop the indexes first. However, there are
other things you may want to consider (for example, database recovery mode).
You can search for "optimizing bulk copy performance" in BOL for more detail
.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew|||Thanks for your input everyone. Again, much appreciated.
"Drew" wrote:
> I currently have a nightly process that truncates a set of tables prior to
> doing a bulk insert from an odbc data source. If these tables have indexe
s
> defined, will the bulk insert step make use of these? The reason I ask is
> because I wasn't sure if it is worth performing optimizations (maintenance
> plan) immediately after the insert. Any help is appreciated.
> --
> Drew
Optimization Rules for XML Bulk Insert
I was wondering if there were any guide lines to optimize the XML Bulk
Insert component by using different XML formats. I read an article that did
cases, but can't remember where I saw it.
Thank for any help!
-Frank
I found what I was looking for.
http://msdn.microsoft.com/library/de...erformance.asp
If anyone wanted to know also.
"Frank DeLuccia" <fdeluccia-RemoveThisInsert-@.edifice-ims.com> wrote in
message news:OMsHu%239REHA.3984@.TK2MSFTNGP11.phx.gbl...
> Hello Again,
> I was wondering if there were any guide lines to optimize the XML Bulk
> Insert component by using different XML formats. I read an article that
did
> cases, but can't remember where I saw it.
> Thank for any help!
> -Frank
>
Friday, March 9, 2012
Opinons on using Delete then BULK INSERT
INSERT. It reads through the flat file first deleting rows in the target
table that if finds. It then issues the BULK INSERT command on the same flat
file. This has the effect of doing an insert or update.
The DBA reports that the tables tend to become more fragmented because of
the deletes rather than using update.
Are there any pitfalls to this approach? Does it sound OK?
Are there other approaches other than Transact SQL for inserting or updating
large amounts of flat file data?
Thanks!!
McG
[url]http://mcg
news:eudcIdGPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi. Just looking for your opinions on a technique I have seen for using
> BULK INSERT. It reads through the flat file first deleting rows in the
> target table that if finds. It then issues the BULK INSERT command on the
> same flat file. This has the effect of doing an insert or update.
> The DBA reports that the tables tend to become more fragmented because of
> the deletes rather than using update.
> Are there any pitfalls to this approach? Does it sound OK?
> Are there other approaches other than Transact SQL for inserting or
> updating large amounts of flat file data?
> Thanks!!
> --
> McG
> [url]http://mcg
>
>
It depends on how big the tables are and how big the flat file is.
Personally, I would probably BULK INSERT the flat file into a working table
first. Then using a chunking method, I would update/insert about 10,000
rows at a time until the task completed. This has the advantage of letting
SQL Server do what it does best using SET theory for determining updates and
inserts, but also doing it in small enough chunks that other processing can
continue with only slight interruption.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the pointer Rick.
McG
[url]http://mcg
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23pxvSgGPGHA.1124@.TK2MSFTNGP10.phx.gbl...
> "McG
> news:eudcIdGPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> It depends on how big the tables are and how big the flat file is.
> Personally, I would probably BULK INSERT the flat file into a working
> table first. Then using a chunking method, I would update/insert about
> 10,000 rows at a time until the task completed. This has the advantage of
> letting SQL Server do what it does best using SET theory for determining
> updates and inserts, but also doing it in small enough chunks that other
> processing can continue with only slight interruption.
> Just my .02
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
Saturday, February 25, 2012
Operating system error code 3(The system cannot find the path specified.).
I use Bulk insert to put data to myTable.
When the SQL server is in local machin, it works well. But when I put
the data in a sql server situated not locally, then I get a error
message like this:
Could not bulk insert because file 'C:\Data\2003
txtfiles\abif_20031130.txt' could not be opened. Operating system
error code 3(The system cannot find the path specified.).
BULK INSERT myTable
FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
with (
-- codepage = ' + char(39) + 'ACP' + char(39) + ',
fieldterminator = ';',
rowterminator = '\n',
keepnulls,
maxerrors=0)
Someone can explan me what the error shows up
Thanks in advance
- Loi -The location of the file is relative to the SQL Server on which the BULK
INSERT command runs. To import a file from another machine, specify an UNC
path rather than use drive letters. For example:
BULK INSERT myTable
FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'
Note that the SQL Server service account needs permissions to the share.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Loi" <votanloi@.hotmail.com> wrote in message
news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> Hi All,
> I use Bulk insert to put data to myTable.
> When the SQL server is in local machin, it works well. But when I put
> the data in a sql server situated not locally, then I get a error
> message like this:
> Could not bulk insert because file 'C:\Data\2003
> txtfiles\abif_20031130.txt' could not be opened. Operating system
> error code 3(The system cannot find the path specified.).
> BULK INSERT myTable
> FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> with (
> -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> fieldterminator = ';',
> rowterminator = '\n',
> keepnulls,
> maxerrors=0)
> Someone can explan me what the error shows up
> Thanks in advance
> - Loi -|||Hi Dan,
Thanks for your reply.
It does not work yet.
A next question:
SQL Server service account needs permissions to the share.
In my case SQL server is setup following:
In the security tab: Authentication - SQL server and windows.
Startup sevice account - system account.
Can somebody give me any hints
Regards
- Loi -
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<laEvc.20183$Tn6.12968@.newsread1.news.pas.earthlink .net>...
> The location of the file is relative to the SQL Server on which the BULK
> INSERT command runs. To import a file from another machine, specify an UNC
> path rather than use drive letters. For example:
> BULK INSERT myTable
> FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'
> Note that the SQL Server service account needs permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Loi" <votanloi@.hotmail.com> wrote in message
> news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> > Hi All,
> > I use Bulk insert to put data to myTable.
> > When the SQL server is in local machin, it works well. But when I put
> > the data in a sql server situated not locally, then I get a error
> > message like this:
> > Could not bulk insert because file 'C:\Data\2003
> > txtfiles\abif_20031130.txt' could not be opened. Operating system
> > error code 3(The system cannot find the path specified.).
> > BULK INSERT myTable
> > FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> > with (
> > -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> > fieldterminator = ';',
> > rowterminator = '\n',
> > keepnulls,
> > maxerrors=0)
> > Someone can explan me what the error shows up
> > Thanks in advance
> > - Loi -|||> Startup sevice account - system account.
Try using a domain account rather than the local system account and ensure
the account has permissions to the share.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Loi Tan Vo" <loi.tan.vo@.fnh.no> wrote in message
news:73f04281.0406040358.5a342f93@.posting.google.c om...
> Hi Dan,
> Thanks for your reply.
> It does not work yet.
> A next question:
> SQL Server service account needs permissions to the share.
> In my case SQL server is setup following:
> In the security tab: Authentication - SQL server and windows.
> Startup sevice account - system account.
> Can somebody give me any hints
> Regards
> - Loi -
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<laEvc.20183$Tn6.12968@.newsread1.news.pas.earthlink .net>...
> > The location of the file is relative to the SQL Server on which the BULK
> > INSERT command runs. To import a file from another machine, specify an
UNC
> > path rather than use drive letters. For example:
> > BULK INSERT myTable
> > FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'
> > Note that the SQL Server service account needs permissions to the share.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Loi" <votanloi@.hotmail.com> wrote in message
> > news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> > > Hi All,
> > > > I use Bulk insert to put data to myTable.
> > > When the SQL server is in local machin, it works well. But when I put
> > > the data in a sql server situated not locally, then I get a error
> > > message like this:
> > > > Could not bulk insert because file 'C:\Data\2003
> > > txtfiles\abif_20031130.txt' could not be opened. Operating system
> > > error code 3(The system cannot find the path specified.).
> > > > BULK INSERT myTable
> > > FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> > > with (
> > > -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> > > fieldterminator = ';',
> > > rowterminator = '\n',
> > > keepnulls,
> > > maxerrors=0)
> > > > Someone can explan me what the error shows up
> > > > Thanks in advance
> > > - Loi -
Monday, February 20, 2012
OPENXML for bulk inserts
I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
@.xmlOrders nText,
@.var_id int
I have this xml (@.xmlOrders ) :
<ORDER>
<ORDER>
<art_desc>blablablabla.</art_desc>
<art_code>1</art_code>
<art_units>50</art_units>
<xx>111</xx>
<yy>111</yy>
</ORDER>
<ORDER>
<art_desc>tetetetet.</art_desc>
<art_code>2</art_code>
<art_units>10</art_units>
<xx>222</xx>
<yy>222</yy>
</ORDER>
</ORDER>
I need to insert the parameter @.var_id and this fields from @.xmlOrders:
(art_desc,art_code and art_units) into a table "tbl_orders" with this
structure:
order_id int identity
art_desc varchar
art_code varchar
art_units int
var_id int
How can i modify this for work:
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
Insert Into TBL_ORDERS
SELECT art_desc,art_code,art_units
FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
WITH (art_desc varchar(100), art_code varchar(100),art_units int)
XMLOrders
EXEC sp_xml_removedocument @.hDoc
Thank you.
Hello, Oterox!
You wrote on Thu, 28 Oct 2004 15:04:20 +0200:
[Sorry, skipped]
O> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
The third parameter is the code for default mapping.
1 - attribute centerinc
2 - element centeric
Since you didn't point out the column pattern
O> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
the server use default, etc attribute centerinc mapping. This is not
correct, 'cause you don't have art_desc attribute as well as art_code and
art_units. To make this work you should change the default mapping to
element mapping:
FROM OPENXML (@.hdoc, '/ORDER/ORDER',2) --change the value to 2
or use explicit column mapping
FROM OPENXML (@.hdoc, '/ORDER/ORDER')
WITH(
art_desc varchar(100) 'art_desc',
art_code varchar(100) 'art_code',
art_units int 'art_units'
)
With best regards, Alex Shirshov.
|||If you could send me your procedure and your xml.file.And write me how you
import xml file to sql database.
my e-mail: ljag@.wp.pl
Uytkownik "Oterox" <oterox@.asp404.com> napisa w wiadomoci
news:u0EcT7OvEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi and thk for your help ;-)
> I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
> @.xmlOrders nText,
> @.var_id int
> I have this xml (@.xmlOrders ) :
> <ORDER>
> <ORDER>
> <art_desc>blablablabla.</art_desc>
> <art_code>1</art_code>
> <art_units>50</art_units>
> <xx>111</xx>
> <yy>111</yy>
> </ORDER>
> <ORDER>
> <art_desc>tetetetet.</art_desc>
> <art_code>2</art_code>
> <art_units>10</art_units>
> <xx>222</xx>
> <yy>222</yy>
> </ORDER>
> </ORDER>
> I need to insert the parameter @.var_id and this fields from @.xmlOrders:
> (art_desc,art_code and art_units) into a table "tbl_orders" with this
> structure:
> order_id int identity
> art_desc varchar
> art_code varchar
> art_units int
> var_id int
> How can i modify this for work:
> DECLARE @.hDoc int
> exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
> Insert Into TBL_ORDERS
> SELECT art_desc,art_code,art_units
> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
> XMLOrders
> EXEC sp_xml_removedocument @.hDoc
> Thank you.
>
>