Bulk Updates taking a long time

We have a huge table with around 25 Million records. We want to reset two int Columns of all records to 0. Currently its taking around 1.5 hours... What are the best practises we can follow to reduce the total update time.

Initially we used - Update <TableName> set <Column1>=0, <Column2>=0.
Later we modified the query to include a WHERE clause and did the update in batch mode such as

DECLARE @.maxCount Int
DECLARE @.iCount Int
SELECT @.MaxCount = Max(ID) FROM organizationsource
SET @.iCount = 0
WHILE (@.iCount<@.MaxCount)
BEGIN
UPDATE <tableName> set <Column1> = 0, <Column2>=0
WHERE ID between @.iCount and @.iCount+1000000
SET @.iCount = @.iCount + 1000000
END

Can you please suggest some tips to improve the update performance.
Can we do something at the SQL Server level / are there any settings at the database level for performing faster updates.

Thanks,
Loonysan

Hard to determine based on the information you've provided so far:

Is the ID field the primary index?|||

Thanks for your interest.

To Answer your Questions

1) Yes - ID field is the primary key in my table.
2) This table will not be accessed by other applications during the update process.
3) Yeah - I have the Data and Log files in different drives. (Should I keep them in different disks for better performance)
4) We are using SQL Server 2005 :)
5) The code is resides in a Stored Procedure

Thanks,
Loonysan

|||

Ok.

Are the columns you are updating indexed also?

This can slow down updates. If so, drop the index and recreate after the update has happened.

Also - make sure the following is turned off to improve performance:

auto create statistics

Bulk insert and vb.net - URGENT

Hello.
I want to use bulk insert in order to import a huge file, lets say
"Source.txt" in my db, lets say "MyDB".
The data in "Source.txt" are numerical and date format. Values are seperated
with the tab character.
When I use :
BULK INSERT [owner].[dbname].[tablename] FROM
'c:\Source.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
KEEPNULLS )
everything works fine.
Now, I want to add a datetime field in the beginning of each line in
source.txt.
I do this by :
<...open Source.txt as 1...>
<...open Altered.txt as 2...>
OldLine=LineInput(1)
NewLine="...DateVariable..." & vbTab &
OldLine
Print 2, OldLine & vbNewLine
<...close files...>
Why does
BULK INSERT [owner].[dbname].[tablename] FROM
'c:\Altered.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
KEEPNULLS )
doesn't work?
The error message I get is
Bulk insert data conversion error (type mismatch) for row 1,
column 385 (...column description...).
I have tried with different row terminators but no success.
I suspect that while creating the Altered.txt, 2 row terminator characters
are added instead of 1.
Please any help will be much appreciated.
Thank you,
VasilisDid you also recreate the table with the datetime as the first column? File
fields are mapped to target table columns by ordinal position unless you
specify a format file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1d59$2pa3$1@.ulysses.noc.ntua.gr...
> Hello.
> I want to use bulk insert in order to import a huge file, lets say
> "Source.txt" in my db, lets say "MyDB".
> The data in "Source.txt" are numerical and date format. Values are
> seperated with the tab character.
> When I use :
> BULK INSERT [owner].[dbname].[tablename] FROM
> 'c:\Source.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
> KEEPNULLS )
> everything works fine.
> Now, I want to add a datetime field in the beginning of each line in
> source.txt.
> I do this by :
> <...open Source.txt as 1...>
> <...open Altered.txt as 2...>
> OldLine=LineInput(1)
> NewLine="...DateVariable..." & vbTab & OldLine
> Print 2, OldLine & vbNewLine
> <...close files...>
>
> Why does
> BULK INSERT [owner].[dbname].[tablename] FROM
> 'c:\Altered.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
> KEEPNULLS )
> doesn't work?
> The error message I get is
> Bulk insert data conversion error (type mismatch) for row
> 1, column 385 (...column description...).
>
> I have tried with different row terminators but no success.
> I suspect that while creating the Altered.txt, 2 row terminator characters
> are added instead of 1.
> Please any help will be much appreciated.
> Thank you,
> Vasilis
>
>
>|||Yes. The table stracture was changed accordingly.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23WQyROGPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Did you also recreate the table with the datetime as the first column?
> File fields are mapped to target table columns by ordinal position unless
> you specify a format file.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
> news:du1d59$2pa3$1@.ulysses.noc.ntua.gr...
>
>|||>>> Print 2, OldLine & vbNewLine
Reviewing your pseudo-code, it looks to me like your are writing out the
original record instead of the new one with the date. Check the output file
to ensure it is as expected. If you still have problems, post the actual
code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1gjs$14o$1@.ulysses.noc.ntua.gr...
> Yes. The table stracture was changed accordingly.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23WQyROGPGHA.2124@.TK2MSFTNGP14.phx.gbl...
>
>|||Yes, you are right.
I actually use Print 2, Newline & vbNewLine.
Following is the actual code :
FileOpen(2, Bulk_File, OpenMode.Output)
While Not EOF(1)
Input(1, lineText)
'create new line
Dim NewLine As String
NewLine = NewDateVariable & vbTab & lineText
'print new line
Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine &
vbNewLine, even with a simple terminator, eg "!" and changed bulk command
syntax to ...ROWTERMINATOR='!'...
End While
FileClose()
Dim bulkCom As String
bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File &
"' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
bulkCom &= WED.ExecuteCommand(bulkCom, False)
End If
WED is a class to communicate with the server. It doesn't add anything to
the command that is to be executed
Bulk_File is the file to be imported|||Did you view the output file to make sure the format is as expected?
Unfortunately, this code snippet doesn't include the NewDataVariable
assignment, which may be part of the problem. I'd like to have enough code
so that I can reproduce your problem in my environment.
I notice that you use NewLine as a variable name. Perhaps
System.Environment.NewLine is being used instead so the output is not as
expected.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1kki$c2q$1@.ulysses.noc.ntua.gr...
> Yes, you are right.
> I actually use Print 2, Newline & vbNewLine.
> Following is the actual code :
> FileOpen(2, Bulk_File, OpenMode.Output)
> While Not EOF(1)
> Input(1, lineText)
> 'create new line
> Dim NewLine As String
> NewLine = NewDateVariable & vbTab & lineText
> 'print new line
> Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine
> & vbNewLine, even with a simple terminator, eg "!" and changed bulk
> command syntax to ...ROWTERMINATOR='!'...
> End While
> FileClose()
> Dim bulkCom As String
> bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File
> & "' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
> bulkCom &= WED.ExecuteCommand(bulkCom, False)
> End If
>
> WED is a class to communicate with the server. It doesn't add anything to
> the command that is to be executed
> Bulk_File is the file to be imported
>
>|||The first thing I'd check is that the file you are getting has the correct
separator and terminator bytes. Possibly Print is adding its own line
terminators, e.g., vbCrLf. The easiest way to check is to open a Command
window, enter debug source.txt, and then type d at the - prompt. That will
dump the start of the file in ASCII and Hex. Enter d again to dump the next
128 bytes. Enter q to quit out of debug.
Your lines should be terminated by 0A. If they are terminated by 0D 0A, for
example, VB is adding a CR and LF (the standard Windows text file line
terminator), and you will have to modify your program or the BULK INSERT
accordingly.
HTH,
Mike Abraham
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1kki$c2q$1@.ulysses.noc.ntua.gr...
> Yes, you are right.
> I actually use Print 2, Newline & vbNewLine.
> Following is the actual code :
> FileOpen(2, Bulk_File, OpenMode.Output)
> While Not EOF(1)
> Input(1, lineText)
> 'create new line
> Dim NewLine As String
> NewLine = NewDateVariable & vbTab & lineText
> 'print new line
> Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine
> & vbNewLine, even with a simple terminator, eg "!" and changed bulk
> command syntax to ...ROWTERMINATOR='!'...
> End While
> FileClose()
> Dim bulkCom As String
> bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File
> & "' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
> bulkCom &= WED.ExecuteCommand(bulkCom, False)
> End If
>
> WED is a class to communicate with the server. It doesn't add anything to
> the command that is to be executed
> Bulk_File is the file to be imported
>
>

Bulk Delete

we are trying to delete data from a huge 75 million records table
it takes 4hr to prune data

delete from Company where recordid in (select top 10000 recordid from
recordid_Fed3 where flag = 0)

we have a loop that prunes 10000 records at a time in a while loop
let me know if there is a better way to acheive thisIf you just want to delete *every* row in the table you can use
TRUNCATE TABLE :

<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_2hk5.asp
If you want to selectively delete rows, that won't work, though. If
you want to delete most of the rows (but not all of them) you could
insert the ones you want to keep into some other (temporary) table,
truncate the main table, then insert the rows back.|||kumar (svengala@.gmail.com) writes:
> we are trying to delete data from a huge 75 million records table
> it takes 4hr to prune data
> delete from Company where recordid in (select top 10000 recordid from
> recordid_Fed3 where flag = 0)
> we have a loop that prunes 10000 records at a time in a while loop
> let me know if there is a better way to acheive this

Rather than using SELECT TOP, try use a condition that matches the clustered
index and slice that up in intervals. Assume that the clustered index is
on recordid, and that this is an integer you would do:

SELECT @.recordid = MIN(recordid) FROM Company (WHERE flag = 0),
@.increment = 100000
WHILE EXISTS (SELECT * FROM Company WHERE recordid = @.recordid)
BEGIN
DELETE Company
WHERE recordid BETWEEN @.recordid AND @.recordid + @.increment - 1
AND flag = 0
SELECT @.recordid = @.recordid + @.increment
END

In this way you are only scanning the table once for rows to delete.

If you anticipate that you will delete more rows than you will retain,
you could create a new table, and insert the rows to keep. In this case
you need to make sure that you also bring with you constraints, indexes,
and triggers, and you will have to move referencing foreign keys. The
insert can be further speedied up by using SELECT INTO, but SELECT INTO
may not give you a faithful copy of the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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