Faulty insert into sql express database

Hello All,

I have an InfoPath form that submits data to a .NET webservice. The webservice parses the infopath form and inserts the data into a database. The database is SQL Server EXPRESS.

I have however run into some problems. The InfoPath form is within SharePoint and I noticed that when I submit the data, it only shows up in the database as a new record 9 out of 10 times. That is for every 10 submissions of the form, there are only about 9 new records in the database. The ID field (an autoincrement field) also skips a number indicating the the transaction may have failed.

However, when I test the form on my local PC, it inserts a new record for every submission.

The webservice and database are on a remote server not my local.

Does anyone have any ideas? Could it be a deficiency of SQL Server Express?

Any ideas, answers or suggestions would be much appreciated.

Hi Hugo,

I would suggest looking at the log files on the remote server where SQL Express is installed to see if there is any information about the failure there. I doubt any failure is the sole responsibility of SQL Express, it is probably a combination of issues from many sources.

Mike

Fatal System Error

Whats happening:

Error#: 40002
Error Item: 01000: [Microsoft...][ODBC...][SQL...]The Statement has
been terminated. > GETID 'INSERT INTO Table(Column1, Column2, Column 3,
Column4, Column5, Column6) VALUES
("","Value1","Value2","Value3","Value4","Value5")'

Error Message: 01000: Microsoft...][ODBC...][SQL...]The statement has
been terminated.

Active Form: frmForm
Form Name:

--
GETID Stored Procedure:
--

CREATE PROCEDURE GETID @.strsql text AS

PRINT cast(@.strsql as varchar(4000))

EXEC(" "
+@.strsql + " select @.@.identity as id")

GO
--

Anyone know what could be causing it? The database has changed - the
frontend code hasn't, so it has to be a database problem. What
happened was a few tables became incorrect so we restored them from a
backup. Everything else seems to work, apart from adding a new record
to this table.
You can edit current records etc..just not add.

Thanks in advance,[posted and mailed, please reply in news]

(lbillson@.netexperts.co.uk) writes:
> Whats happening:
> Error#: 40002
> Error Item: 01000: [Microsoft...][ODBC...][SQL...]The Statement has
> been terminated. > GETID 'INSERT INTO Table(Column1, Column2, Column 3,
> Column4, Column5, Column6) VALUES
> ("","Value1","Value2","Value3","Value4","Value5")'
> Error Message: 01000: Microsoft...][ODBC...][SQL...]The statement has
> been terminated.
>...
> Anyone know what could be causing it? The database has changed - the
> frontend code hasn't, so it has to be a database problem. What
> happened was a few tables became incorrect so we restored them from a
> backup. Everything else seems to work, apart from adding a new record
> to this table.

It appears that one error message has been lost somewhere. "The statement
has been terminated" is an accompanying message to the real error message.

I don't know what sort of client code you are using, but it seems that
the error handling needs a review. When working with SQL Server it's
essential to get all error message. And particularly the first error
message.

As for what have happened, it seems that the definition of the table has
changed, so that the statement is no longer correct. May be there is one
more column which is not nullable. You could also be running into a
key violation. Or, since you had restored some tables from backup, maybe
you are inserting a non-valid foreign key.

Your best bet is probably to run the query from Query Analyzer.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Fastest way to insert?

Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Thanks for the opinions. I'll definitely investigate.
*** Sent via Developersdex http://www.codecomments.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.
4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode onl
y
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled u
p
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in tim
e
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.
4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
>

Fastest way to insert?

Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for the opinions. I'll definitely investigate.
*** Sent via Developersdex http://www.codecomments.com ***
|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>
|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.
|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>

Fastest way to insert?

Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
indexes is faster (or slower) than doing a SELECT INTO and then creating
the indexes on the table created.
The table in question contains around a million records.
Thanks
*** Sent via Developersdex http://www.developersdex.com ***The only way to know is to test it both ways in the exact conditions and
hardware etc. that you will be using.
--
Andrew J. Kelly SQL MVP
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||And how many are you inserting? How many indexes and how large. How much
time does it need to create indexes? Hardware specs? Columns specs?
It would probably best to drop indexes and recreate them if you can, but I
dont see the problem even if you dont drop them. Only, try to see if you
need to defragment them after that...
MC
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Joe
Most likely that SELECT * INTO will win. BTW ,1 million rows is not so big
nowadays
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hello,
If it is a production server I will suggest you to create the table and
indexes and then insert the data into new table using BCP IN or BULK INSERT
in batch commit mode with BULK_INSERT recovery model. If you are creating a
table with SELECT * INTO and this will create locks in sysobjects table
which
is not a good idea.
Thanks
Hari
"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
> indexes is faster (or slower) than doing a SELECT INTO and then creating
> the indexes on the table created.
> The table in question contains around a million records.
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Can anybody explain to me exactly what BULK_LOGGED recovery does?
I gather it has less overhead and less recovery options than FULL, but
the BOL description is pretty vague.
Thanks.
Josh
On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hello,
>If it is a production server I will suggest you to create the table and
>indexes and then insert the data into new table using BCP IN or BULK INSERT
>in batch commit mode with BULK_INSERT recovery model. If you are creating a
>table with SELECT * INTO and this will create locks in sysobjects table
>which
> is not a good idea.
>Thanks
>Hari
>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||BOL has some good info but here are some basics. First off it is no
different than FULL recovery until you do an operation that can take
advantage of a minimally logged operation. These would be things like:
CREATE INDEX
SELECT INTO
BULK INSERT
etc. (See BOL for more details)
When you do execute one of these in Bulk_Logged or Simple recovery mode only
the ID of the extent that was modified during that operation is logged in
the tran log. So if you did a bulk insert of 1 million rows and it filled up
1000 extents you would only log the ID's for those 1000 extents not the
actual data that would normally be logged in FULL recovey mode.There are
several implications of this. One is that you can no longer do point in time
recovery until you issue another full backup to start the proper logging
again. You can restore the entire log file though. The second is that when
you backup the tran log it will go and get all the data for those 1000
extents and place them in the log backup file. So the backup will take the
hit that normally would have occured if you did not do a minimally logged
load.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7skgu25thlm2vjher8dfrl29scbpj894bv@.4ax.com...
> Can anybody explain to me exactly what BULK_LOGGED recovery does?
> I gather it has less overhead and less recovery options than FULL, but
> the BOL description is pretty vague.
> Thanks.
> Josh
>
> On Wed, 28 Feb 2007 08:05:51 -0600, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>>Hello,
>>If it is a production server I will suggest you to create the table and
>>indexes and then insert the data into new table using BCP IN or BULK
>>INSERT
>>in batch commit mode with BULK_INSERT recovery model. If you are creating
>>a
>>table with SELECT * INTO and this will create locks in sysobjects table
>>which
>> is not a good idea.
>>Thanks
>>Hari
>>"Joe Grizzly" <grizzlyjoe@.campcool.com> wrote in message
>>news:%23OYYT%23zWHHA.4252@.TK2MSFTNGP06.phx.gbl...
>> Hi, I'm trying to figure out if doing an INSERT INTO.. a table with
>> indexes is faster (or slower) than doing a SELECT INTO and then creating
>> the indexes on the table created.
>> The table in question contains around a million records.
>> Thanks
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Andrew,
Thanks!
So, bulk_logged is not faster or cheaper than simple, even for those
operations.
We have some large tables that are recreated daily, and it has
occurred to us to move them out to a separate database we can run on
whatever lightweight logging we can find. I guess simple is the
simple answer! Also make sure it's on RAID10 space rather than RAID5.
Also, the point about backup taking a hit, is good to know. I guess
another option might be to use bulk_logged for a little extra safety
and then if nothing goes wrong, just truncate the log instead of
backing it up to tape.
Josh
On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>BOL has some good info but here are some basics. First off it is no
>different than FULL recovery until you do an operation that can take
>advantage of a minimally logged operation. These would be things like:
>CREATE INDEX
>SELECT INTO
>BULK INSERT
>etc. (See BOL for more details)
>When you do execute one of these in Bulk_Logged or Simple recovery mode only
>the ID of the extent that was modified during that operation is logged in
>the tran log. So if you did a bulk insert of 1 million rows and it filled up
>1000 extents you would only log the ID's for those 1000 extents not the
>actual data that would normally be logged in FULL recovey mode.There are
>several implications of this. One is that you can no longer do point in time
>recovery until you issue another full backup to start the proper logging
>again. You can restore the entire log file though. The second is that when
>you backup the tran log it will go and get all the data for those 1000
>extents and place them in the log backup file. So the backup will take the
>hit that normally would have occured if you did not do a minimally logged
>load.|||If it is a minimally logged operation (must meet all conditions listed
below) then you ge the same logging and performance from Bulk-Logged and
Simple cerocery models. The key difference with Simple is that you can't do
a log restore at all. Yes Raid 5 is bad for heavy writes. One more note. If
you do use a seperate db you still need valid FULL backups before you start
the operation.
a.. The recovery model is simple or bulk-logged.
a.. The target table is not being replicated.
a.. The target table does not have any triggers.
a.. The target table has either 0 rows or no indexes.
a.. The TABLOCK hint is specified. For more information,
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:623ju250u00obhg2c3v4v3ell4guds7sm6@.4ax.com...
> Andrew,
> Thanks!
> So, bulk_logged is not faster or cheaper than simple, even for those
> operations.
> We have some large tables that are recreated daily, and it has
> occurred to us to move them out to a separate database we can run on
> whatever lightweight logging we can find. I guess simple is the
> simple answer! Also make sure it's on RAID10 space rather than RAID5.
> Also, the point about backup taking a hit, is good to know. I guess
> another option might be to use bulk_logged for a little extra safety
> and then if nothing goes wrong, just truncate the log instead of
> backing it up to tape.
> Josh
>
> On Fri, 2 Mar 2007 13:40:41 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>BOL has some good info but here are some basics. First off it is no
>>different than FULL recovery until you do an operation that can take
>>advantage of a minimally logged operation. These would be things like:
>>CREATE INDEX
>>SELECT INTO
>>BULK INSERT
>>etc. (See BOL for more details)
>>When you do execute one of these in Bulk_Logged or Simple recovery mode
>>only
>>the ID of the extent that was modified during that operation is logged in
>>the tran log. So if you did a bulk insert of 1 million rows and it filled
>>up
>>1000 extents you would only log the ID's for those 1000 extents not the
>>actual data that would normally be logged in FULL recovey mode.There are
>>several implications of this. One is that you can no longer do point in
>>time
>>recovery until you issue another full backup to start the proper logging
>>again. You can restore the entire log file though. The second is that when
>>you backup the tran log it will go and get all the data for those 1000
>>extents and place them in the log backup file. So the backup will take the
>>hit that normally would have occured if you did not do a minimally logged
>>load.
>

fastest way to insert range/sequence

I'd like to use a stored procedure to insert large amounts of records
into a table. My field A should be filled with a given range of
numbers. I do the following ... but I'm sure there is a better
(faster) way:

select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
while @.start <= 500000
begin
insert into tbl (A, B, C)
values (@.start, 'test1', test2')
set @.start = @.start +1
end

another question is, how to prevent that another user inserts the same
numbers into the field A?

Thanks a lot for any help!
ratu"ratu" <postit@.hispeed.ch> wrote in message
news:e6e93102.0407070830.67d763c5@.posting.google.c om...
> I'd like to use a stored procedure to insert large amounts of records
> into a table. My field A should be filled with a given range of
> numbers. I do the following ... but I'm sure there is a better
> (faster) way:
> select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> while @.start <= 500000
> begin
> insert into tbl (A, B, C)
> values (@.start, 'test1', test2')
> set @.start = @.start +1
> end
> another question is, how to prevent that another user inserts the same
> numbers into the field A?
> Thanks a lot for any help!
> ratu

One possible solution is an auxiliary table of numbers, or a UDF as
described in this post:

http://groups.google.com/groups?q=i...sftngp13&rnum=1

You could then do something like this:

insert into tbl (A, B, C)
select n, 'test1', 'test2'
from dbo.fn_nums(@.start, 500000)

As for preventing duplicate entries, you can use a primary key or unique
constraint to prevent duplicates, depending on your data model. If
necessary, you can also use a check constraint to ensure that the table will
only accept a certain range of numbers.

Simon|||On 7 Jul 2004 09:30:03 -0700, ratu wrote:

> I'd like to use a stored procedure to insert large amounts of records
> into a table. My field A should be filled with a given range of
> numbers. I do the following ... but I'm sure there is a better
> (faster) way:
> select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> while @.start <= 500000
> begin
> insert into tbl (A, B, C)
> values (@.start, 'test1', test2')
> set @.start = @.start +1
> end

Here's the DIGITS view-based version:

CREATE VIEW DIGITS (D) AS
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;

INSERT INTO tbl (A,B,C)
SELECT D3.D * 1000 + D2.D * 100 + D1.D as [ValA],
'test1' AS [ValB], 'test2' AS [ValC])
FROM DIGITS AS [D1], DIGITS AS [D2], DIGITS AS [D3]
WHERE D3.D * 1000 + D2.D * 100 + D1.D between @.LowVal AND @.HiVal

> another question is, how to prevent that another user inserts the same
> numbers into the field A?

Create a unique index on field A. The other user will receive an error.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jaaqazxjdyer.1b96bwdbr1qod.dlg@.40tude.net...
> On 7 Jul 2004 09:30:03 -0700, ratu wrote:
> > I'd like to use a stored procedure to insert large amounts of records
> > into a table. My field A should be filled with a given range of
> > numbers. I do the following ... but I'm sure there is a better
> > (faster) way:
> > select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> > while @.start <= 500000
> > begin
> > insert into tbl (A, B, C)
> > values (@.start, 'test1', test2')
> > set @.start = @.start +1
> > end
> Here's the DIGITS view-based version:
> CREATE VIEW DIGITS (D) AS
> SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;

If there's no need to eliminate duplicates in the union operation, as here,
use UNION ALL instead. This particular case is trivial but in other
cases the performance improvement can be noticeable.

> INSERT INTO tbl (A,B,C)
> SELECT D3.D * 1000 + D2.D * 100 + D1.D as [ValA],
> 'test1' AS [ValB], 'test2' AS [ValC])
> FROM DIGITS AS [D1], DIGITS AS [D2], DIGITS AS [D3]
> WHERE D3.D * 1000 + D2.D * 100 + D1.D between @.LowVal AND @.HiVal

You're missing the 10s place here.

Of course, one can fill a table with all integers possibly needed and query
for ranges. Alternatively, one can define a view to calculate the range by
applying constraints to each place value in turn, that is, the ones, tens, hundreds,
etc., as opposed to applying a constraint to each final candidate integer. The
former being more of a branch and bound approach while the latter is
generate and test.

CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9

-- Return a range [@.lower, @.upper]
CREATE FUNCTION NonnegativeIntegerRange
(@.lower INT, @.upper INT)
RETURNS TABLE
AS
RETURN(
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d + 1000 * Thousands.d +
10000 * TenThousands.d AS n
FROM Digits AS TenThousands
INNER JOIN
Digits AS Thousands
ON TenThousands.d BETWEEN @.lower/10000 AND @.upper/10000 AND
(TenThousands.d <> @.upper/10000 OR
Thousands.d <= (@.upper%10000)/1000) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d >= (@.lower%10000)/1000)
INNER JOIN
Digits AS Hundreds
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <= (@.upper%1000)/100) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d >= (@.lower%1000)/100)
INNER JOIN
Digits AS Tens
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <> (@.upper%1000)/100 OR
Tens.d <= (@.upper%100)/10) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d <> (@.lower%1000)/100 OR
Tens.d >= (@.lower%100)/10)
INNER JOIN
Digits AS Ones
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <> (@.upper%1000)/100 OR
Tens.d <> (@.upper%100)/10 OR
Ones.d <= @.upper%10) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d <> (@.lower%1000)/100 OR
Tens.d <> (@.lower%100)/10 OR
Ones.d >= @.lower%10)
)

SELECT n
FROM NonnegativeIntegerRange(20000, 20009)
ORDER BY n

n
20000
20001
20002
20003
20004
20005
20006
20007
20008
20009

--
JAG|||On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote:

> If there's no need to eliminate duplicates in the union operation, as here,
> use UNION ALL instead. This particular case is trivial but in other
> cases the performance improvement can be noticeable.

Thanks. I noticed you using UNION ALL before, but never understood it,
because I never looked up the explanation of UNION ALL.

> You're missing the 10s place here.

D'oh!

> Of course, one can fill a table with all integers possibly needed and query
> for ranges. Alternatively, one can define a view to calculate the range by
> applying constraints to each place value in turn, that is, the ones, tens, hundreds,
> etc., as opposed to applying a constraint to each final candidate integer. The
> former being more of a branch and bound approach while the latter is
> generate and test.

Thanks for your clearly expressed improvement of my half-baked ideas. :)

I still have two very active SQL 6.5 servers, so I tend not to think of UDF
solutions.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:1x4qrf3kzc0t0$.1wjptvh6fhq50.dlg@.40tude.net.. .
> On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote:
> > If there's no need to eliminate duplicates in the union operation, as here,
> > use UNION ALL instead. This particular case is trivial but in other
> > cases the performance improvement can be noticeable.
> Thanks. I noticed you using UNION ALL before, but never understood it,
> because I never looked up the explanation of UNION ALL.

It's worth knowing when you're taking the union of significant result sets.
It's like knowing when and when not to use DISTINCT. In this case
it obviously isn't an issue other than reinforcing good practice.

> > You're missing the 10s place here.
> D'oh!

It's even easier to do when you go into the millions and beyond.

> > Of course, one can fill a table with all integers possibly needed and query
> > for ranges. Alternatively, one can define a view to calculate the range by
> > applying constraints to each place value in turn, that is, the ones, tens, hundreds,
> > etc., as opposed to applying a constraint to each final candidate integer. The
> > former being more of a branch and bound approach while the latter is
> > generate and test.
> Thanks for your clearly expressed improvement of my half-baked ideas. :)

Your generate-and-test approach is an obvious and completely reasonable
solution. The branch-and-bound approach, run on SQL Server 2000, does
seem, in cursory testing, to be over twice as fast. Though it is more verbose
and perhaps less immediately clear.

> I still have two very active SQL 6.5 servers, so I tend not to think of UDF
> solutions.

The UDF here is just for named packaging. Could've simply provided the
SELECT using variables for the range bounds.

--
JAG

Fastest Update/Insert Method

There is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter

>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...[vbcol=seagreen]
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> to perform monthly
> test to check if the
> may either perform
> ways for the
> records in the current
> as an insert. This
> are any tricks or|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
>

Fastest Update/Insert Method

There is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter

>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>
The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...[vbcol=seagreen]
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> to perform monthly
> test to check if the
> may either perform
> ways for the
> records in the current
> as an insert. This
> are any tricks or
|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
>

Fastest Update/Insert Method

I have a large database of several hundred gig and I need to perform monthly
updates of about 4 gigs of that data. However I have to test to check if the
update records exists in the current database so that I may either perform
an update or an insert. I've tried all kind of different ways for the
update, but the fastest appears to be to delete all records in the current
database that exist in the update and then do everything as an insert. This
still takes 30 hours to complete. I need to know if there are any tricks or
tips for updating large databases more quickly.
TIAThere is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter
>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>|||The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> >--Original Message--
> >I have a large database of several hundred gig and I need
> to perform monthly
> >updates of about 4 gigs of that data. However I have to
> test to check if the
> >update records exists in the current database so that I
> may either perform
> >an update or an insert. I've tried all kind of different
> ways for the
> >update, but the fastest appears to be to delete all
> records in the current
> >database that exist in the update and then do everything
> as an insert. This
> >still takes 30 hours to complete. I need to know if there
> are any tricks or
> >tips for updating large databases more quickly.
> >
> >TIA
> >
> >
> >.
> >|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
--
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
> > There is probably 1001 tips, but here are the two I know.
> >
> > I Remove all foreign keys before performing the insert /
> > update, if you need to know why then replay, nb I have not
> > included Primary key as you will need it to check if
> > record exists ;)
> >
> > The second thing is to perform the update on the same
> > server, taking out the network. What I mean is this if 4gb
> > on server B is to copied onto 5gb on Server A, then it
> > will take less time if you write it to a file on server B,
> > compress it, send it Server A, uncompress it, load it into
> > a temporary table then perform the SQL.
> >
> > Ok I now have a stupid question, did you try the
> >
> > INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> > (SELECT PrimaryKey from TEMPTABLE) ?
> >
> > Hope this helps
> > Peter
> >
> >
> > >--Original Message--
> > >I have a large database of several hundred gig and I need
> > to perform monthly
> > >updates of about 4 gigs of that data. However I have to
> > test to check if the
> > >update records exists in the current database so that I
> > may either perform
> > >an update or an insert. I've tried all kind of different
> > ways for the
> > >update, but the fastest appears to be to delete all
> > records in the current
> > >database that exist in the update and then do everything
> > as an insert. This
> > >still takes 30 hours to complete. I need to know if there
> > are any tricks or
> > >tips for updating large databases more quickly.
> > >
> > >TIA
> > >
> > >
> > >.
> > >
>

Faster Insert?

I was wondering which was faster:

INSERT INTO tab1 (col1, col2, col3) SELECT col1, col2, col3 FROM tab2

or

SELECT col1, col2, col3 INTO tab1 FROM tab2

I am inserting 400,000 rows and I am trying to figure out what the fastest way to do that is.

Thanks for any advice / help in advance

The Select Into has to create the metadata and the table, so that is some overhead.

But not enough that you'd even notice

If your recovery model is Bulk Logged, and depending on indexes, et al. you might be able to get minimal logging with a locking hint on the INSERT INTO, which would speed things up some.

Having your query optimized is probably the most important first step.

|||

they don't make much difference... however, having an index on those columns will be very costly when inserting. it maybe worth dropping and recreating after the insert...

just a thought.

|||First one is always fully logged except for INSERT...SELECT OPENROWSET(BULK) in SQL Server 2005. The other operation is minimally logged in simple or bulk_logged recovery model. The fastest way to create a table with data is to do SELECT...INTO. But both these statements are not identical in functionality. The 2nd one will fail if the table exists (tab1 in your example). And they have different permission requirements too.|||

DaleJ wrote:

If your recovery model is Bulk Logged, and depending on indexes, et al. you might be able to get minimal logging with a locking hint on the INSERT INTO, which would speed things up some.

INSERT is always fully logged except when used with OPENROWSET(BULK) in SQL Server 2005. There is no way to get minimal logging for other INSERT statements in SQL Server.

fast, consistent way to bulk insert?

I'm looking for a way to insert 50k records into a SQL Server table, and need to get it done faster. right now using BULK INSERT takes 5-10 seconds, but faster would be better, and even better if it were a consistent amount of time.

I've heard of DTS but don't know quite how to use it - would be offer any performance gains? any clue what the bottleneck is for BULK INSERT? hard drive speed? amount of RAM (this was on a 512mb machine)? parsing the fields?

thanks for any ideas.512mb is rock bottom for running a production SQL box. Are you appending these records to an existing table? Is it something you're recreating every time? There's other options available if you're starting from scratch each go...|||...and as I understand it Bulk Insert is faster than anythting DTS can offer. BCP? I don't know about comparisons of BULK INSERT and BCP - maybe oters do.

Also you can try inserting into another table and then inserting from that. If you validate the data in the intermediary table you can use the NOCHECK option on constraints to speed up inserting.|||There's a comparison of bulk insert methods here. All the comparisons are for 2005 though.

http://weblogs.sqlteam.com/mladenp/archive/2006/07/17/10634.aspx|||What I have heard is that BULK INSERT is slightly faster than bcp mainly because bcp is a client side utility and BULK INSERT is internal to SQL Server. I could be wrong.|||Want faster imports?

Drop the indexes and use bcp|||thanks for the suggestions, I'll check them out. 512mb is definitely low, and will be upgraded once approved (ah bureaocracy)

this bulk insert will actually be done several times during the day, adding to the table instead of updating, and has to be able to be initiated from code (i.e. .NET app). looks like SSIS is something to check out.|||also, if you use bcp it can be considerably faster if you specify the TABLOCK hint in your query. You can pass the TABLOCK hint using the -h flag.

More tips here:

http://www.databasejournal.com/features/mssql/article.php/3095511

Fast Way To "Insert Into" a million records?

See the SQL below, on our SQL server this takes about
10min for 50,000 records, and about 3 hours for a million
records. Is there ANYTHING I can do to speed this up?
-Can I allocate DB space ahead of time?
-Can I put a table in to some type of lock mode'
-Is there something better than insert into?
INSERT INTO MaintHist (DebtorID, AssignCollector,
ChangeCollector, DateChanged, TableName, FieldName,
OldValue, NewValue)
SELECT TempUpdate.RecordUniqueValue, CAST
(TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
WHERE TempUpdate.BatchID = 505
Thanks!
Jasonyou can try to Bulk copy it in as this is non-logged. However that may NOT
be the right solution for you.
it's also fairly common to drop indexes before you do huge inserts and then
rebuild them when inserts are complete.
just food for thought
Greg Jackson
PDX, Oregon|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:16e3c01c448b6$d7c55ca0$a601280a@.phx
.gbl...
> See the SQL below, on our SQL server this takes about
> 10min for 50,000 records, and about 3 hours for a million
> records. Is there ANYTHING I can do to speed this up?
>
Note that your example indicates an insert speed of 5000 records/minute.
That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
better than linear scaling. This is good.

> -Can I allocate DB space ahead of time?
Yes. Expand the database before adding the records. This will improve
performance some.
> -Can I put a table in to some type of lock mode'
SQL handles this automagically. I doubt you could improve performance with
a locking hint.
> -Is there something better than insert into?
You could try a DTS package to do the transfer, but I am not sure if that
will help.
There is always faster hardware.
>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason|||How would a BULK COPY be done?
Jason Roozee

>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx
.gbl...
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
>
> Yes. Expand the database before adding the records. This will improve
> performance some.
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware.
>|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Way To "Insert Into" a million records?

See the SQL below, on our SQL server this takes about
10min for 50,000 records, and about 3 hours for a million
records. Is there ANYTHING I can do to speed this up?
-Can I allocate DB space ahead of time?
-Can I put a table in to some type of lock mode?
-Is there something better than insert into?
INSERT INTO MaintHist (DebtorID, AssignCollector,
ChangeCollector, DateChanged, TableName, FieldName,
OldValue, NewValue)
SELECT TempUpdate.RecordUniqueValue, CAST
(TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
WHERE TempUpdate.BatchID = 505
Thanks!
Jason
you can try to Bulk copy it in as this is non-logged. However that may NOT
be the right solution for you.
it's also fairly common to drop indexes before you do huge inserts and then
rebuild them when inserts are complete.
just food for thought
Greg Jackson
PDX, Oregon
|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> See the SQL below, on our SQL server this takes about
> 10min for 50,000 records, and about 3 hours for a million
> records. Is there ANYTHING I can do to speed this up?
>
Note that your example indicates an insert speed of 5000 records/minute.
That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
better than linear scaling. This is good.

> -Can I allocate DB space ahead of time?
Yes. Expand the database before adding the records. This will improve
performance some.
> -Can I put a table in to some type of lock mode?
SQL handles this automagically. I doubt you could improve performance with
a locking hint.
> -Is there something better than insert into?
You could try a DTS package to do the transfer, but I am not sure if that
will help.
There is always faster hardware.
>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason
|||How would a BULK COPY be done?
Jason Roozee

>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>
|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
> Yes. Expand the database before adding the records. This will improve
> performance some.
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware.
>
|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ
|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>
|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Way To "Insert Into" a million records?

See the SQL below, on our SQL server this takes about
10min for 50,000 records, and about 3 hours for a million
records. Is there ANYTHING I can do to speed this up?
-Can I allocate DB space ahead of time?
-Can I put a table in to some type of lock mode'
-Is there something better than insert into?
INSERT INTO MaintHist (DebtorID, AssignCollector,
ChangeCollector, DateChanged, TableName, FieldName,
OldValue, NewValue)
SELECT TempUpdate.RecordUniqueValue, CAST
(TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
WHERE TempUpdate.BatchID = 505
Thanks!
Jasonyou can try to Bulk copy it in as this is non-logged. However that may NOT
be the right solution for you.
it's also fairly common to drop indexes before you do huge inserts and then
rebuild them when inserts are complete.
just food for thought
Greg Jackson
PDX, Oregon|||Comments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> See the SQL below, on our SQL server this takes about
> 10min for 50,000 records, and about 3 hours for a million
> records. Is there ANYTHING I can do to speed this up?
>
Note that your example indicates an insert speed of 5000 records/minute.
That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
better than linear scaling. This is good.
> -Can I allocate DB space ahead of time?
Yes. Expand the database before adding the records. This will improve
performance some.
> -Can I put a table in to some type of lock mode'
SQL handles this automagically. I doubt you could improve performance with
a locking hint.
> -Is there something better than insert into?
You could try a DTS package to do the transfer, but I am not sure if that
will help.
There is always faster hardware. :)
>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason|||How would a BULK COPY be done?
Jason Roozee
>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
--
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> > See the SQL below, on our SQL server this takes about
> > 10min for 50,000 records, and about 3 hours for a million
> > records. Is there ANYTHING I can do to speed this up?
> >
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
> > -Can I allocate DB space ahead of time?
> Yes. Expand the database before adding the records. This will improve
> performance some.
> > -Can I put a table in to some type of lock mode'
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> > -Is there something better than insert into?
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware. :)
> >
> >
> >
> > INSERT INTO MaintHist (DebtorID, AssignCollector,
> > ChangeCollector, DateChanged, TableName, FieldName,
> > OldValue, NewValue)
> >
> > SELECT TempUpdate.RecordUniqueValue, CAST
> > (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> > GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> > TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> > WHERE TempUpdate.BatchID = 505
> >
> > Thanks!
> > Jason
>|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Uploading Data from Client to SQL2000 Database

Does anyone know how to upload (bulk) data from a client (written in Excel VBA) to a remote SQL2000 database? Of coarse I tried "INSERT INTO" and rst.addnew but I noticed this is much, much slower as downloading from the same remote database.
Thanks.It tends to be faster if you can do this the other way round i.e. get SQL Server to load it from Excel rather than using Excel to load it into SQL Server.|||You can try to use BCP function or SQL DTS to import into the relevant table. This will automate your process and make it much faster. Sometimes, it depend on the indexing,size of a table or the design of the database for performance.|||BCP uses the filesystem which I cannot access from the client v.v.

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/users look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays' data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method for the Account table.
If possible, you might want to drop the main table indexes just before you load the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/u
sers look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays'
data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to
not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method
for the Account table.
If possible, you might want to drop the main table indexes just before you l
oad the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.

Fast insert

I need to do insert a lot of data into a table and need to improve
performance. I don't think DTS is adequate since I'm not simply
copying/transforming existing data but rather computing it as I go. I think
transaction logging is an extra cost that I should avoid, but I'm already
using simple recovery mode... What are other possible optimizations?Have your appliation write each computed record to a tab delimited text file
residing on the local HD and having the same column format as the table.
Once done, bulk copy the file into the destination table. Also, it may help
to drop indexes prior to the load and re-create them afterward.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx[/u
rl]
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:3E700E7B-0983-4DB7-BCAF-BC862C2AEB9A@.microsoft.com...
>I need to do insert a lot of data into a table and need to improve
> performance. I don't think DTS is adequate since I'm not simply
> copying/transforming existing data but rather computing it as I go. I
> think
> transaction logging is an extra cost that I should avoid, but I'm already
> using simple recovery mode... What are other possible optimizations?

Fast "bulk" inserts...

I have an app that must insert batches of transaction records every 30 min.
Typically only a few hundred each time. We found that just appending a
hundred or so INSERT commands together in a single query sent to the server
was significantly faster than our first approach of preparing a paraemterize
d
query and executing once for each record. So now we send only a few commands
to SQL instead of hundreds.
Someone suggested to me that we put all of the records into an XML
"document" and use OPENXML. We will test this approach.
Any suggestions, guesses on how the new approach and other possible
approaches to try?
Also, would a different approach be warranted if the number of records in a
batch was around 10K, instead of just 200-300?
Thanks,
LMcPhee"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:E2D39232-D7B3-450C-B76F-5536DC10FEF5@.microsoft.com...
>I have an app that must insert batches of transaction records every 30 min.
> Typically only a few hundred each time. We found that just appending a
> hundred or so INSERT commands together in a single query sent to the
> server
> was significantly faster than our first approach of preparing a
> paraemterized
> query and executing once for each record. So now we send only a few
> commands
> to SQL instead of hundreds.
> Someone suggested to me that we put all of the records into an XML
> "document" and use OPENXML. We will test this approach.
If you can get by with your current approach, do. In SQL 2005/ADO.NET 2.0
you will have the SQLBulkInsert, which will be the perfered method.

> Any suggestions, guesses on how the new approach and other possible
> approaches to try?
> Also, would a different approach be warranted if the number of records in
> a
> batch was around 10K, instead of just 200-300?
>
The batching approach will still work, but you wouldn't want to batch 10k
rows. That's just too much SQL. Keep your batch size constant, and send
multiple batches if necessary.
Another critically important thing for running multiple inserts is to use an
explicit transaction. Without an explicit transaction SQL Server must flush
the log to disk (aka commit) after every insert. The commit takes much
longer than just doing the insert, so you want to do all the inserts, and
just force a single commit at the end.
David