All geek questions in one place

Mass updates and fixed frequency in SQL Server

The background of my database is mostly Oracle, but I recently helped with SQL Server. My group inherited some SQL Server DTS packages that download and update large amounts of data daily. It currently runs on SQL Server 2000, but will soon be upgraded to SQL Server 2005 or 2008. Bulk updates are too slow.

One thing I noticed about the code is that some major updates are performed in the procedural code in cycles, so each statement updates a small part of the table in one transaction. Is this a sound method for updating on a SQL server? Blocking concurrent sessions should not be a problem, as user access to tables is disabled during bulk loading. I ran into some problems and found some articles suggesting that this saves resources and that resources are released every time an update occurs, which leads to greater efficiency. In Oracle, this is usually a bad approach, and I have used separate transactions for very large updates with success in Oracle. Frequent commits slow down the process and use more resources in Oracle.

My question is that for bulk updates in SQL Server it is usually recommended to use procedural code and execute many SQL statements or use one large statement to perform the whole update?

+3
source share
4 answers

Sorry guys,

. , . , , . , - , , , , , , . , . , , 1 , : -)

SQL Server2000/2005, DB2, ADABAS . , Oracle -.

T-SQL bcp, .

, , , , , .

. IO IO CPU

+2

, , - 100 1000. , : ? ? ? , , .

SQL, - , :

SET ROWCOUNT 1000
WHILE 1=1 BEGIN
    DELETE FROM MyTable WHERE ...
    IF @@ROWCOUNT = 0
        BREAK
END
SET ROWCOUNT 0

. UPDATE, . ( xxxx , .)

, xx . , , ( .)

+1

.

... , db , (tablockx) , , , . , .

, tempdb, tempdb ( -), .

- , SQL Server ( ) concurrency - (, , 10 , ).

0

SQL Server 2005 2008 DTS SSIS. , , , SSIS.

, SQL Server 2000 , . , , . , , (, - !). 24- , .

0

Source: https://habr.com/ru/post/1702956/

More articles:


All Articles