a faster way to update a big table

hi
well I am working on a project that our database has 2, 8000 record table that they have triggers on the update of their feilds
our application must update these tables but it takes a long time to do this
I know there is something named bulk insert but I couldn't find sth similar to this command for update
so would you please help me to find a faster way to update these tables?
thanks for your attention
Best Regards
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
hi,
netman Mo wrote:
> hi
> well I am working on a project that our database has 2, 8000 record
> table that they have triggers on the update of their feilds our
> application must update these tables but it takes a long time to do
> this
> I know there is something named bulk insert but I couldn't find sth
> similar to this command for update
> so would you please help me to find a faster way to update these
> tables?
nope.. update syntax is not overloaded with bulk operators..
if the cause of your delay is dependent on the trigger fired by the update
statement, you should perhaps check it's code... or... if you are sure the
updates you are performing do not involve the trigger check, you can disable
it before executing the statements..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply

A cursor performance related question

Hi All,
I have 800 K records need to be processed one by one in a large table daily
using cursor way to update the default columns. The rest of queries are only
read information from this table. The records in this table could be very
large later on.
My question is what's the best way to use cursor to duel with this table.
here is my options:
1.Use a BIG cursor to lock all the un-processed records and use a singal
connection from Query Analyzer
2. Try to break /subgroup them with flags and run the same procedure above
with muti-Query Analyzer Connections. Each connection only duel part of the
record set.
I only have one SQL server . The testing result is Option 1 has the best
performance.
Is SQL server not good at running query parallelly with muti-connections? or
I need to improve the SQL server hardware staff by adding more memory ?
Any expert can point me to the right way?
Many Thanks,
Stevenews.microsoft.com (stevenxiu@.yahoo.com) writes:
> I have 800 K records need to be processed one by one in a large table
> daily using cursor way to update the default columns. The rest of
> queries are only read information from this table. The records in this
> table could be very large later on.
> My question is what's the best way to use cursor to duel with this
> table. here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure
> above with muti-Query Analyzer Connections. Each connection only duel
> part of the record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with
> muti-connections? or I need to improve the SQL server hardware staff by
> adding more memory ?
With only this abstract narrative it is impossible to say very much. One
possibility is that you have poor indexing, cause the multiple connections
to block each other.
To get any accurate response you would need to post:
o CREATE TABLE and CREATE INDEX statements for the table.
o The code for the two options you are using.
o Some background on what the code is actually doing.
However, there is fair chance that the answer is option 0: don't use a
cursor at all, but apply set-based logic. This usually improves performance
with magnitudes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns.
Chances are that you don't need to do it "one by one" and that it will be
more efficient without a cursor. We won't know for sure unless you post a
better description of your problem. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
But based on a few decades with SQL, I have written only five cursors
and I know that I could have avoided three of them if I had the CASE
expression back in the old days.
The fact that you do not know the differences between records and rows
tells me your approach is probably not relational and that yoiur
mindset is still stuck in sequential file processing.|||You should avoid using cursors to perform updates. Set-based updates
perform better for a number of reasons:
(1) Set-based updates write to the transaction log more efficiently. Each
update incurs a certain amount of overhead in the transaction log to mark
the start and end of each write to each affected object in the database.
This includes not only writes to the heap or clustered index, but also
writes to each nonclustered index. In addition, if the same index page is
updated more than once during the thousands of individual updates, then that
page will be recorded in the transaction log once for each related update,
causing the log to grow faster than is necessary. Frequent disk allocations
can hugely affect performance. (This last shouldn't be a problem if you
pre-allocate log space.)
(2) With set-based updates, indexes can be updated en-mass--meaning fewer
costly page splits and fewer writes to each affected database object thus
reducing the frequency of disk head ss.
(3) Locking is more efficient. Set-based updates obtain locks on all of the
affected rows before beginning the write, and then release them as soon as
the changes have been committed. Thousands of individual updates requires
the server to go through the process of obtaining each individual exclusive
lock on each individual row. With set-based updates, locks are more likely
to be escalated when necessary, thereby reducing overhead.
(4) With set-based updates, the system is tasked with writing and writing
only. Thousands of individual updates usually means that several additional
reads are interspersed within the writes. This can cause a lot more disk
activity and in particular, a significant increase in costly disk ss.
If you must use a cursor (I'm not of the opinion that they are always bad;
however, they should only be used as a last resort.), then you should cache
the updates in a temp table or table variable and then flush them using
set-based updates. Inserting individual rows into a table with no indexes
(a heap) or appending to a table with only a clustered index performs pretty
well, and if you have enough memory, temp tables and table variables remain
for the most part in memory. (Writes to tempdb are eventually flushed out
to disk, provided the affected rows and objects still exist by the time the
system gets around to initiating the write.) By appending, I mean that
inserts occur in the same order as the clustered index key--that is, with an
ascending index, each row inserted has a key value that is greater than the
key value in any existing row in the table.
"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns. The rest of queries are
> only
> read information from this table. The records in this table could be very
> large later on.
> My question is what's the best way to use cursor to duel with this table.
> here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure above
> with muti-Query Analyzer Connections. Each connection only duel part of
> the
> record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with muti-connections?
> or
> I need to improve the SQL server hardware staff by adding more memory ?
> Any expert can point me to the right way?
> Many Thanks,
> Steve
>sql

A better way? Short query

Code Snippet

UPDATE mr

SET [Test Name] = ic.[New Test Name],

Mnemonic = ic.[New Mnemonic]

FROM MRSTATS mr

INNER JOIN IdentityChanges ic

ON mr.Mnemonic = ic.Mnemonic

AND mr.[Test Name] = ic.[Test Name]

AND mr.Bill_Item_ID = ic.Bill_Item_ID

IdentityChanges has approximately 25 entries, MRSTATS has about 1.5 million

Hi,

why do you want a better way? If it's too slow, have you set an index like this:

Code Snippet

CREATE INDEX IX_IC_TO_MR

ON MRSTATS (Mnemonic, [Test Name], Bill_Item_ID)

Try it and and run the query again.

--

Regards,

Daniel Kuppitz

|||Sorry for the small amount of information, the query in my origional post takes upwards of 10 minutes. When I create this index do I need to specify how the columns map to eachother or does the statement assume that it means MR.Mnemonic maps to IC.Mnemonic? I do believe this is what i'm looking for. Will this create index statement work without modification because the query is still taking a really long time? Do I need to change the origional query at all? Thanks a lot for your help.|||I got it working. Thanks a lot.

7/11 Windows update broke my stored procedure

I have a managed code DLL that is used as a CLR assembly in my database. A stored procedure in the database references this CLR assembly.
My CLR assembly has a dependency on Microsoft's System.Management.dll.
In order to get my assembly to work, I added System.Management.dll as an assembly to the database using:

CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

For months this stored procedure has worked fine... up until today.

Today (7/11/07) I noticed a new Microsoft update was available.
I applied the patch to an XP machine that has my database and now my CLR assembly stored procedure no longer works.
I checked a 2003 Server machine that did not have the latest patch and see that the stored procedure is working fine.
I then apply Microsoft update to the 2003 server machine.
Now the stored procedure no longer works on the 2003 machine.

I deleted my stored procedure and CLR assembly and then added them back with no success.
I then deleted and added back the System.Management.dll assembly again and suddenly now my stored procedure works again.

This is BAD. I can't have Windows updates blowing up my app.
What am I doing wrong here?
I must use the System.Management.dll in my CLR stored procedure but I can't have changes to Microsoft's files causing me to be unable to reference them as assemblies.

How can my CLR stored procedure assembly reference a Microsoft assembly such that changes to the Microsoft assembly does not cause version mismatches?

Hmm, that's weird. In what way does you proc no longer work?

The reason I think it is weird is that as the dll in question is loaded from the database, having a new dll should not really matter, unless the patch also changes something else that the dll calls into and therefore fails.

So, once again - in what way does your proc fail?

Niels

|||

Unfortunately I do not remember the exact phraseology of the exception (the problem has been fixed on my development machine) but it did mention that the assembly it was looking for did not match the version found in the GAC. I'm certain it mentioned the GAC.

I will include as much detail as I can below.

I have a web app that makes calls to the stored procedure like this:

Code Snippet

try
{
using (SqlConnection sqlConnection = new SqlConnection(strConnectionString))
{
sqlCommand = new SqlCommand("usp_MyCLRStoredProcedure", sqlConnection);

sqlConnection.Open();

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlParameter = new SqlParameter("@.iRetVal", SqlDbType.Int);
sqlParameter.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@.iVersion", SqlDbType.Int);
sqlParameter.Value = (int)eVersion;
sqlCommand.Parameters.Add(sqlParameter);

// After applying the 7/11 Windows update, execution of
// the next line results in an exception being thrown
sqlCommand.ExecuteNonQuery();

// Code after this point never executes due to exception being thrown...

}
}
catch (Exception e)
{
Console.WriteLine("Error while attempting to access database:\n" + e.ToString());
}

The CLR stored procedure is a SQL Server Project written in Visual Studio and compiled into a DLL. This C# code fetches the CPU ID of the machine, and does some cryptography work. My CLR stored procedure is similar to the following code block (I've tried to omit non-relevant parts):

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using System.Management;
using System.IO;
using System.Security.Cryptography;

public partial class StoredProcedures
{

// This is the entry point of the stored procedure
[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyStoredProcedure(int iVersion)
{
// ... code ...

// calls are made to Environment.MachineName

// ... more code ...

// get the processor id
ManagementClass managementClass = new ManagementClass( "Win32_Processor" );
ManagementObjectCollection mocInstances = managementClass.GetInstances();

// ... more code ...
// Calls are made to System.Security.Cryptography.HMACSHA1
// Calls are made to System.Text.Encoding
// Calls are made to System.Convert
// ... more code ...

// Call ExecuteNonQuery() on a regular (T-SQL based) stored procedure

// ... more code ...
}
}

To hook the CLR DLL code above with a stored procedure that is accessible by SQL Server, I performed the following from within Microsoft SQL Server Management Studio:

Code Snippet

USE MyDatabase
GO

-- SQL server needs to know if a database is "trustworthy" before it
-- will allow it to load any extra assemblies
ALTER DATABASE MyDatabase SET trustworthy ON
GO

-- To add a reference to a "SQL Server Project" within Visual Studio
-- you must first add the dll you want to reference to the database itself
-- The following code block places "System.Management.dll" and all of
-- the assemblies it depends on in the database.
-- After completing this step, you should be able to use the "Add Reference"
-- feature of Visual Studio to add the "SystemManagement" class as a
-- reference in your SQL Server Project.
CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The "compatibility level" of the Database needs to be set to 90
-- before it has the ability to recognize CLR calls
exec sp_dbcmptlevel 'MyDatabase', 90
GO

-- SQL Server 2005 won't recognize CLR calls unless you manually enable it.
-- To enable the CLR on SQL Server 2005, execute the code block below
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Load a CLR DLL as an assembly onto the database
CREATE ASSEMBLY MyAssembly
FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The following code block demonstrates how to create and associate
-- a stored procedure with a method in a CLR assembly.
-- Of course, the CLR assembly must already be registered
-- with the database (as shown above) before this is attempted.
-- The "EXTERNAL NAME" clause takes the following parameter
-- [Assembly name (as shown in MSSQL Server Management Studio)].ClassName.MethodName
CREATE PROCEDURE usp_MyCLRStoredProcedure
@.iVersion int
AS EXTERNAL NAME MyAssembly.StoredProcedures.MyStoredProcedure
GO

-- Give execute priveleges to the newly added stored procedure
GRANT EXECUTE ON usp_MyCLRStoredProcedure TO MyWebAppUser
GO

-- To debug the CLR stored procedure, the PDB file
-- must be added to the assembly.
-- 1) The MyCLRStoredProcedure.PDB file must
-- be installed in the database; execute the
-- T-SQL code block below.
-- 2) In the Visual Studio IDE, select
-- Debug -> Attach to Process
-- 3) Select "sqlserver.exe" from the Available Processes
-- list box and click the "Attach" button.
-- 4) Breakpoints should now be functional in
-- the MyCLRStoredProcedure source code.
ALTER ASSEMBLY MyAssembly
ADD FILE FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.pdb'
GO

At this point, everything is wired up and ready to go.

Prior to the 7/11 Windows update, a user of the web app could initiate a call to "usp_MyCLRStoredProcedure" and everything would work fine.
After applying the 7/11 Windows update, an exception now occurs somewhere during the call to usp_MyCLRStoredProcedure.

After deleting the stored procedure "usp_MyCLRStoredProcedure" and the assemblies "MyAssembly" and "SystemManagement" from the database and then re-running the SQL code block shown above, everything worked normally again.

I have one machine that I haven't fixed yet but it does not have any sort of development environment. I can try to get a development environment set up on the machine or instrument the web app to log the exception text but in the meantime if anyone has any clues or sees something I am doing wrong in the code above, please let me know.

Thank you.

|||it would be definitely easier to help you with the exact error message that is thrown.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

64 bit Edition and 32bit Participate in VIRTUALSQL Cluster

Hi All

I have a requiment to update a SQL Cluster that I have, however we only have the budget to upgrade one of the servers participating in the Cluster.

Older servers are ML370 G4 Dual 3Ghz 10GB RAM - Windows 2003 Enterprise (existing)

the server I want to upgrade to is a

DL380 G5 Quad 3Ghz (Duo) - 20GB RAM - Windows 2003 Enterprise x64 edition (new)

I would also be upgrading to SQL 2005 Enterprise in the same upgrade (x64 edition on the new server)

What I want to know is if a x64bit Edition and a standard x86 edition both participate in the same VIRTUALSQL instance in a clustered environment?

Would appreciate assistance from any Microsoft people reading this post?

No,

All instances of SQL Server participating in a Cluster must be the same -either 32 bit or 64 bit.

However, you may want to consider running 32 bit SQL Server on the 64 bit Windows Server.

Or, you may forgo the Cluster, and using the 64 bit SQL Server as primary, create a Mirror server with the 32 bit server as a fail-over partner.

|||If you've got the cash for a SQL 2005 Enterprise license and a single DL380 don't skimp. Purchase another DL380, they aren't that expensive.

400 million record update

Hi,
I have a sql2k table with 400 Million records. All records need to be
updated (one field.).
1. What id tested was that DTS transfer(updating the field during the
transferring) to a diff table in the same db. It took 67 hours.
2. The second try I did was Select into the the same db. same thing -- 70
hours.
Is there any faster method taht I could use the accomplish this?
ThanksWith an index on a very specific field such as identity or spread-out date,
you can do this in a batch mode, something like this:
declare @.i int
set @.i = 1 (or min from your table)
get max id from table
while @.i < maxid
begin
begin tran
delete from table where id between @.i and @.i + somenumber (50K-00K')
check for error
commit tran
set @.i = @.i + somenumber
end
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Hi,
What you are testing is not really an update but the copy of a huge table.
Could you give more details about exactly what are you going to update, field
data type, operation.
Could you restore a copy of this database to some other place and test
running a simple UPDATE statement? Note that since this will be one
transaction you will need enough disk space for the transaction log to grow.
Hope this helps,
Ben Nevarez
"Mecn" wrote:
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Just so I can be clear , when you say UPDATE , I think you mean tarnsfer of
data (copy).
Some things you could try is:
use TABLOCK
If possible, use Simple Recovery Mode
Possibly disable indices and rebuild at the end .
These are just some ideas, but is dependant on the exact table structures,
code etc
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||67hours for 400millions?
its really really slow... (around 1600rows/sec)
are you sure that you use the bulk insert option and make sure you setup the
batch size value (to 10 000 or something like this)
your disk subsystem as an impact too, but you should be able to load the
table in 1 to 2hours. (depends on the size of 1 row)
also make sure you drop the indexes before the loading process, then
recreate the indexes. (recreating the indexes will add some processing time
after the loading, so estimate it between 15min to 1h regarding the number
of indexes)
the idea is to cut the big table into small batchs.
in our developments we reach 200 000rows loaded by second.
"Mecn" <mecn@.yahoo.com> wrote in message
news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Thanks for all the responses.
1. You are right I was doing transfer instead of updating the field. I think
that uase DTS with 400,000 batch size/fast load(same as bulk insert?) to a a
non-index table or Insert into (bulk insert?) will be fast than undate the
field. -- 67 Hours....too long.
Any faster ways?
Agree?
"Jeje" <willgart@.hotmail.com> wrote in message
news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
> 67hours for 400millions?
> its really really slow... (around 1600rows/sec)
> are you sure that you use the bulk insert option and make sure you setup
> the batch size value (to 10 000 or something like this)
> your disk subsystem as an impact too, but you should be able to load the
> table in 1 to 2hours. (depends on the size of 1 row)
> also make sure you drop the indexes before the loading process, then
> recreate the indexes. (recreating the indexes will add some processing
> time after the loading, so estimate it between 15min to 1h regarding the
> number of indexes)
> the idea is to cut the big table into small batchs.
> in our developments we reach 200 000rows loaded by second.
> "Mecn" <mecn@.yahoo.com> wrote in message
> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>|||The table datasize = 80GB, total rows = 375million
"Jeje" <willgart@.hotmail.com> wrote in message
news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
> 67hours for 400millions?
> its really really slow... (around 1600rows/sec)
> are you sure that you use the bulk insert option and make sure you setup
> the batch size value (to 10 000 or something like this)
> your disk subsystem as an impact too, but you should be able to load the
> table in 1 to 2hours. (depends on the size of 1 row)
> also make sure you drop the indexes before the loading process, then
> recreate the indexes. (recreating the indexes will add some processing
> time after the loading, so estimate it between 15min to 1h regarding the
> number of indexes)
> the idea is to cut the big table into small batchs.
> in our developments we reach 200 000rows loaded by second.
> "Mecn" <mecn@.yahoo.com> wrote in message
> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>|||yes, fast load is the bulk insert.
but a batch of 400 000 is too large, try to reduce it (to 10 000) and see
the difference.
each batch is a transaction, if the batch is big the transaction will take
more time to be commited.
you have to check the disk, memory & cpu activity during the load to
identify the bottleneck.
also insure that the database recovery is set to "simple", and make sure the
log files are on a separate controller and disks.
and validate that the source query used is correctly optimized.
and if everything is on the same server, try to spread the source database
or files on a different disks then the targeted database / table
If you read & write on the same disks this can cause performance issues.
"Mecn" <mecn@.yahoo.com> wrote in message
news:Okn4KT2XIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Thanks for all the responses.
> 1. You are right I was doing transfer instead of updating the field. I
> think that uase DTS with 400,000 batch size/fast load(same as bulk
> insert?) to a a non-index table or Insert into (bulk insert?) will be fast
> than undate the field. -- 67 Hours....too long.
> Any faster ways?
> Agree?
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
>> 67hours for 400millions?
>> its really really slow... (around 1600rows/sec)
>> are you sure that you use the bulk insert option and make sure you setup
>> the batch size value (to 10 000 or something like this)
>> your disk subsystem as an impact too, but you should be able to load the
>> table in 1 to 2hours. (depends on the size of 1 row)
>> also make sure you drop the indexes before the loading process, then
>> recreate the indexes. (recreating the indexes will add some processing
>> time after the loading, so estimate it between 15min to 1h regarding the
>> number of indexes)
>> the idea is to cut the big table into small batchs.
>> in our developments we reach 200 000rows loaded by second.
>> "Mecn" <mecn@.yahoo.com> wrote in message
>> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>
>|||Hi
SET ROWCOUNT 10000
update_rows:
UPDATE tbakle SET ...
WHERE <condition>
IF @.@.ROWCOUNT > 0 GOTO update_rows
SET ROWCOUNT 0
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>

3-way replication?

Hello,
I currently have a database set up for replication on 3 servers as
follows:
Pub - users connect and update database
Sub1 - database is accessed for local application
Sub2 - database is accessed for local application
The subscriptions are set up for both immediate and queued updating, so,
in theory, if Pub goes down, the users can re-connect to Sub1 to update
the database, and when Pub comes back, the queued updating will apply
the changes to Pub. Also, during the time Pub is down, the local
application on Sub1 will have access to current data.
However, as I understand it, Sub2 will not have access to the latest
data while Pub is down, since the only way those changes make it to Sub2
is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
unable to send the changes.
Do I have this right ? Is there a simple way to get Sub1 to update Sub2
when Pub is down ? As it is now configured, it's kind of a pain in the
neck to manage the replication when we have to make a change to the
database definition, and I also want people to be able to update at Sub2
and have the changes propagate to Sub1 and Pub, in the event that
becomes necessary. But I don't want to create a structure that is a
nightmare to manage.
Any suggestions ?
Thanks,
Patrick
No, there is no simple way to do this.
Replication requires a publisher which figures out what goes where.
What you could do is have Pub publish to Sub1, and then have Sub1 publish to
Sub2. I would use merge replication for this.
Why are you using immediate updating?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick Johnson" <PJohnson_TechnoScope@.nospam.com> wrote in message
news:PJohnson_TechnoScope-09A593.15383717022005@.msnews.microsoft.com...
> Hello,
> I currently have a database set up for replication on 3 servers as
> follows:
> Pub - users connect and update database
> Sub1 - database is accessed for local application
> Sub2 - database is accessed for local application
> The subscriptions are set up for both immediate and queued updating, so,
> in theory, if Pub goes down, the users can re-connect to Sub1 to update
> the database, and when Pub comes back, the queued updating will apply
> the changes to Pub. Also, during the time Pub is down, the local
> application on Sub1 will have access to current data.
> However, as I understand it, Sub2 will not have access to the latest
> data while Pub is down, since the only way those changes make it to Sub2
> is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
> unable to send the changes.
> Do I have this right ? Is there a simple way to get Sub1 to update Sub2
> when Pub is down ? As it is now configured, it's kind of a pain in the
> neck to manage the replication when we have to make a change to the
> database definition, and I also want people to be able to update at Sub2
> and have the changes propagate to Sub1 and Pub, in the event that
> becomes necessary. But I don't want to create a structure that is a
> nightmare to manage.
> Any suggestions ?
> Thanks,
> Patrick
|||In article <uHp4mjTFFHA.1264@.TK2MSFTNGP12.phx.gbl>,
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:

> No, there is no simple way to do this.
> Replication requires a publisher which figures out what goes where.
> What you could do is have Pub publish to Sub1, and then have Sub1 publish to
> Sub2. I would use merge replication for this.
> Why are you using immediate updating?
Hi Hilary,
Thanks for your reply.
I have all of the users updating at Pub right now, so conceivably I
could get away without immediate updating, but the intent is that local
updates can be made at Sub1 and Sub2 and propagated immediately through
the system.
In practice it is a rare event for an update to happen someplace other
than at Pub.
Is immediate updating a bad idea ?
It's been a while since I set it up, but I remember there was some
reason I didn't want to use merge. Of course, now I can't remember what
it was.
Thanks,
Patrick