일괄 처리를 사용하여 Azure SQL Database 및 Azure SQL Managed Instance 애플리케이션 성능을 개선하는 방법

적용 대상: Azure SQL Database Azure SQL Managed Instance

Azure SQL 데이터베이스와 Azure SQL Managed Instance에 대한 일괄 처리 작업은 애플리케이션의 성능과 확장성을 크게 향상시킵니다. 이점을 이해하기 위해 이 문서의 첫 번째 부분에서는 순차 및 일괄 처리된 요청을 Azure SQL 데이터베이스 또는 Azure SQL Managed Instance의 데이터베이스와 비교하는 몇 가지 샘플 테스트 결과를 설명합니다. 문서의 나머지 부분에서는 Azure 애플리케이션에서 일괄 처리를 성공적으로 사용하는 데 도움이 되는 기술, 시나리오 및 고려 사항을 보여줍니다.

Azure SQL 데이터베이스와 Azure SQL Managed Instance에 일괄 처리가 중요한 이유는 무엇인가요?

원격 서비스에 대한 호출 일괄 처리는 성능 및 확장성을 향상시키기 위한 유명한 전략입니다. 직렬화, 네트워크 전송, 역직렬화 같은 원격 서비스와의 모든 트랜잭션에는 고정 처리 비용이 있습니다. 여러 개별 트랜잭션을 단일 일괄 처리로 패키징하면 이러한 비용이 최소화됩니다.

이 문서에서는 다양한 일괄 처리 전략과 시나리오를 살펴보겠습니다. 이러한 전략은 SQL Server를 사용하는 온-프레미스 애플리케이션에도 중요하지만 Azure SQL 데이터베이스 및 Azure SQL Managed Instance에 대한 일괄 처리 사용을 강조하는 몇 가지 이유가 있습니다.

• 특히 동일한 Microsoft Azure 데이터 센터 외부에서 Azure SQL 데이터베이스나 Azure SQL Managed Instance에 액세스하는 경우 Azure SQL 데이터베이스와 Azure SQL Managed Instance에 액세스하는 데 네트워크 대기 시간이 증가할 수 있습니다.
• Azure SQL 데이터베이스 및 Azure SQL Managed Instance의 다중 테넌트 특성은 데이터 액세스 레이어 효율성이 전반적인 데이터베이스 확장성과 관련이 있음을 의미합니다. 미리 정의된 할당량을 초과하는 사용량에 대한 응답으로 Azure SQL 데이터베이스와 Azure SQL Managed Instance는 처리량을 줄이거나 제한 예외로 응답할 수 있습니다. 일괄 처리와 같은 효율성을 통해 해당 한도에 도달하기 전에 더 많은 작업을 수행할 수 있습니다.
• 일괄 처리는 여러 데이터베이스(분할)를 사용하는 아키텍처에도 효과적입니다. 각 데이터베이스 단위와의 상호 작용 효율성은 여전히 전반적인 확장성의 핵심 요인입니다.

Azure SQL 데이터베이스나 Azure SQL Managed Instance를 사용하는 이점 중 하나는 데이터베이스를 호스트하는 서버를 관리할 필요가 없다는 점입니다. 그러나 이 관리형 인프라는 데이터베이스 최적화에 대해 다르게 생각해야 한다는 의미이기도 합니다. 더 이상 데이터베이스 하드웨어나 네트워크 인프라를 개선할 수 없습니다. Microsoft Azure는 이러한 환경을 제어합니다. 사용자가 제어할 수 있는 주요 영역은 애플리케이션이 Azure SQL 데이터베이스 및 Azure SQL Managed Instance와 상호 작용하는 방식입니다. 일괄 처리는 이러한 최적화 중 하나입니다.

이 문서의 첫 번째 부분에서는 Azure SQL 데이터베이스나 Azure SQL Managed Instance를 사용하는 .NET 애플리케이션에 대한 다양한 일괄 처리 기술을 살펴봅니다. 마지막 두 세션은 일괄 처리 지침 및 시나리오를 포함합니다.

일괄 처리 전략

이 문서의 타이밍 결과에 대한 정보

참고 항목

결과가 기준은 아니며 상대적인 성능을 표시하기 위한 것입니다. 타이밍은 테스트 실행 최소 10회 이상의 평균을 기반으로 합니다. 작업은 빈 테이블에 삽입됩니다. 이러한 테스트는 V12 이전으로 측정되었으며 새 DTU 서비스 계층 또는 vCore 서비스 계층을 사용하여 V12 데이터베이스에서 경험할 수 있는 처리량과 반드시 일치하지는 않습니다. 일괄 처리 기술의 상대적 이점은 비슷해야 합니다.

트랜잭션

일괄 작업에 대한 검토를 트랜잭션에 대한 얘기로 시작하는 것이 생소해 보일 수 있습니다. 하지만 클라이언트 쪽 트랜잭션 사용은 서버 쪽 일괄 처리에 성능을 향상시키는 미묘한 영향을 미칩니다. 그리고 트랜잭션은 몇몇 코드 줄을 통해서만 추가될 수 있으므로 순차 작업 성능을 향상시키는 빠른 방법을 제공합니다.

다음 C# 코드는 간단한 테이블에 삽입 및 업데이트 작업 시퀀스를 포함합니다.

List<string> dbOperations = new List<string>();
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 1");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 2");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 3");
dbOperations.Add("insert MyTable values ('new value',1)");
dbOperations.Add("insert MyTable values ('new value',2)");
dbOperations.Add("insert MyTable values ('new value',3)");

다음 ADO.NET 코드는 이러한 작업을 순차적으로 수행합니다.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();

    foreach(string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn);
        cmd.ExecuteNonQuery();
    }
}

이 코드를 최적화하는 가장 좋은 방법은 이러한 호출의 클라이언트 쪽 일괄 처리 형식 일부를 구현하는 것입니다. 그러나 단순히 트랜잭션에서 호출 시퀀스를 래핑하여 이 코드 성능을 향상시키는 간단한 방법이 있습니다. 트랜잭션을 사용하는 동일한 코드는 다음과 같습니다.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();
    SqlTransaction transaction = conn.BeginTransaction();

    foreach (string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
        cmd.ExecuteNonQuery();
    }

    transaction.Commit();
}

트랜잭션이 양쪽 예제에 실제로 사용되고 있습니다. 첫 번째 예제에서 각 개별 호출은 암시적 트랜잭션입니다. 두 번째 예제에서 명시적 트랜잭션이 모든 호출을 래핑합니다. 미리 쓰기 트랜잭션 로그에 대한 설명서에 따라, 로그 레코드는 트랜잭션이 커밋할 때 디스크에 플러시됩니다. 따라서 트랜잭션에 더 많은 호출을 포함하면 트랜잭션 로그에 대한 쓰기는 트랜잭션이 커밋될 때까지 지연될 수 있습니다. 사실상, 서버의 트랜잭션 로그에 대한 쓰기에 일괄 처리를 사용하는 것입니다.

다음 표에서는 임시 테스트 결과를 보여줍니다. 테스트에서는 트랜잭션을 사용하거나 사용하지 않고 같은 순차 삽입을 수행했습니다. 더 많은 관점에서 첫 번째 테스트 집합은 원격으로 노트북에서 Microsoft Azure의 데이터베이스로 실행되었습니다. 두 번째 테스트 집합은 같은 Microsoft Azure 데이터 센터(미국 서부) 내에 있는 클라우드 서비스와 데이터베이스에서 실행되었습니다. 다음 표에서는 트랜잭션을 사용하거나 사용하지 않는 순차 삽입 기간(밀리초)을 보여줍니다.

온-프레미스에서 Azure로:

작업	트랜잭션 없음(ms)	트랜잭션(ms)
1	130	402
10	1208	1226
100	12662	10395
1000	128852	102917

Azure에서Azure(동일한 데이터 센터):

작업	트랜잭션 없음(ms)	트랜잭션(ms)
1	21	26
10	220	56
100	2145	341
1000	21479	2756

참고 항목

결과는 벤치마크가 아닙니다. 이 문서의 타이밍 결과에 대한 정보를 참조하세요.

이전 테스트 결과에 따라 트랜잭션에서 단일 작업을 래핑하면 실제로 성능이 저하됩니다. 그러나 단일 트랜잭션 내에서 작업 수를 늘리면 성능 향상이 더욱 두드러집니다. 또한 Microsoft Azure 데이터 센터 내에서 모든 작업이 발생할 때 성능 차이가 더 두드러집니다. Microsoft Azure 데이터 센터 외부에서 Azure SQL 데이터베이스나 Azure SQL Managed Instance 사용 대기 시간이 늘어나면 트랜잭션 사용 성능 이점이 줄어듭니다.

트랜잭션 사용이 성능을 향상시킬 수 있지만 트랜잭션 및 연결에 대한 모범 사례를 지속적으로 관찰하는 것이 필요합니다. 트랜잭션을 최대한 짧게 유지하고 작업이 완료된 후에는 데이터베이스 연결을 닫습니다. 이전 예제의 using 문은 후속 코드 블록이 완료되면 연결이 닫히도록 합니다.

앞선 예제에서는 두 줄로 로컬 트랜잭션을 모든 ADO.NET 코드에 추가할 수 있음을 보여줍니다. 트랜잭션은 순차 삽입, 업데이트 및 삭제 작업을 수행하는 코드의 성능을 향상시키는 빠른 방법을 제공합니다. 그러나 가장 빠른 성능을 위해 테이블 반환 매개 변수와 같은 클라이언트 쪽 일괄 처리를 활용하도록 코드를 추가로 변경하는 것이 좋습니다.

ADO.NET의 트랜잭션에 대한 자세한 내용은 ADO.NET의 로컬 트랜잭션을 참조하세요.

테이블 반환 매개 변수

테이블 반환 매개 변수는 사용자 정의 테이블 형식을 Transact-SQL 문, 저장 프로시저 및 함수의 매개 변수로 지원합니다. 이 클라이언트 쪽 일괄 처리 기술을 사용하면 테이블 반환 매개 변수 내에서 여러 행의 데이터를 보낼 수 있습니다. 테이블 반환 매개 변수를 사용하려면 먼저 테이블 형식을 정의합니다. 다음 Transact-SQL 문은 MyTableType이라는 이름의 테이블 형식을 만듭니다.

    CREATE TYPE MyTableType AS TABLE
    ( mytext TEXT,
      num INT );

코드에서 테이블 형식의 이름과 형식이 정확하게 같은 DataTable을 만듭니다. 텍스트 쿼리나 저장 프로시저 호출의 매개 변수에 이 DataTable을 전달합니다. 다음 예에서는 이 기법을 보여줍니다.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    DataTable table = new DataTable();
    // Add columns and rows. The following is a simple example.
    table.Columns.Add("mytext", typeof(string));
    table.Columns.Add("num", typeof(int));
    for (var i = 0; i < 10; i++)
    {
        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
    }

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
        connection);

    cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@TestTvp",
            SqlDbType = SqlDbType.Structured,
            TypeName = "MyTableType",
            Value = table,
        });

    cmd.ExecuteNonQuery();
}

이전 예제에서 SqlCommand 개체는 테이블 반환 매개 변수 @TestTvp의 행을 삽입합니다. 앞에서 만든 DataTable 개체는 SqlCommand.Parameters.Add 메서드를 통해 이 매개 변수에 할당됩니다. 삽입을 호출 1회로 일괄 처리하면 순차 삽입에 비해 성능이 크게 향상됩니다.

앞선 예제를 더욱 향상시키려면 텍스트 기반 명령 대신 저장 프로시저를 사용합니다. 다음 Transact-SQL 명령은 SimpleTestTableType 테이블 반환 매개 변수를 받아들이는 저장 프로시저를 만듭니다.

CREATE PROCEDURE [dbo].[sp_InsertRows]
@TestTvp as MyTableType READONLY
AS
BEGIN
INSERT INTO MyTable(mytext, num)
SELECT mytext, num FROM @TestTvp
END
GO

그 후 이전 코드 예제의 SqlCommand 개체 선언을 다음과 같이 변경합니다.

SqlCommand cmd = new SqlCommand("sp_InsertRows", connection);
cmd.CommandType = CommandType.StoredProcedure;

대부분의 경우 테이블 반환 매개 변수는 다른 일괄 처리 기술과 같거나 더 우수한 성능을 갖습니다. 테이블 반환 매개 변수는 다른 옵션에 비해 융통성이 많기 때문에 더 좋을 수 있습니다. 예를 들어 SQL 대량 복사와 같은 다른 기법은 새 행의 삽입만을 허용합니다. 그러나 테이블 반환 매개 변수를 사용하면 저장 프로시저의 논리를 사용하여 업데이트되는 행과 삽입되는 행을 확인할 수 있습니다. 지정한 행을 삽입, 업데이트 또는 삭제해야 하는지 여부를 나타내는 "작업" 열이 포함되도록 테이블 형식을 수정할 수도 있습니다.

다음 테이블은 테이블 반환 매개 변수 사용에 대한 임시 테스트 결과를 밀리초 단위로 보여 줍니다.

작업	온-프레미스에서 Azure로(ms)	Azure 동일한 데이터 센터(ms)
1	124	32
10	131	25
100	338	51
1000	2615	382
10000	23830	3586

참고 항목

결과는 벤치마크가 아닙니다. 이 문서의 타이밍 결과에 대한 정보를 참조하세요.

일괄 처리로 인한 성능 이점은 즉시 명백합니다. 앞선 순차 테스트에서는 작업 1000개를 수행하는 데 데이터 센터 외부에서는 129초, 데이터 센터 내에서는 21초가 걸렸습니다. 하지만 테이블 반환 변수를 사용하면 1000개 작업이 데이터센터 외부에서는 2.6초, 데이터센터 내부에서는 0.4초밖에 걸리지 않습니다.

테이블 반환 매개 변수에 대한 자세한 내용은 테이블 반환 매개 변수를 참조하세요.

SQL 대량 복사

SQL 대량 복사는 대량의 데이터를 대상 데이터베이스에 삽입하는 또 다른 방법입니다. .NET 애플리케이션은 SqlBulkCopy 클래스를 사용하여 대량 삽입 작업을 수행할 수 있습니다. SqlBulkCopy는 명령줄 도구에 대한 함수인 Bcp.exe 또는 Transact-SQL 문인 BULK INSERT에서 유사합니다. 다음 코드 예제에서는 원본 DataTable 테이블의 행을 대상 테이블인 MyTable에 대량 복사하는 방법을 보여줍니다.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "MyTable";
        bulkCopy.ColumnMappings.Add("mytext", "mytext");
        bulkCopy.ColumnMappings.Add("num", "num");
        bulkCopy.WriteToServer(table);
    }
}

테이블 반환 매개 변수보다 대량 복사를 선호하는 경우가 있습니다. 테이블 반환 매개 변수 문서의 테이블 반환 매개 변수와 BULK INSERT 작업의 비교 테이블을 참조하세요.

다음 임시 테스트 결과에서는 SqlBulkCopy를 사용한 일괄 처리 성능(밀리초 단위)을 보여줍니다.

작업	온-프레미스에서 Azure로(ms)	Azure 동일한 데이터 센터(ms)
1	433	57
10	441	32
100	636	53
1000	2535	341
10000	21605	2737

참고 항목

결과는 벤치마크가 아닙니다. 이 문서의 타이밍 결과에 대한 정보를 참조하세요.

소규모 배치에서는, 테이블 반환 매개 변수가 SqlBulkCopy 클래스보다 성능이 뛰어납니다. 그러나 행 1,000~10,000개에 대한 테스트의 경우 SqlBulkCopy가 테이블 반환 매개 변수보다 12~31% 빠르게 수행됩니다. 테이블 반환 매개 변수처럼 SqlBulkCopy 역시 일괄 처리된 삽입의 좋은 옵션이며, 비일괄 처리 작업의 성능과 비교하면 특히 그렇습니다.

ADO.NET에서 대량 복사에 대한 자세한 내용은 대량 복사 작업을 참조하세요.

여러 행 매개 변수가 있는 INSERT 문

소규모 일괄 처리의 한 가지 대안은 행을 여러 개 삽입하는 매개 변수가 있는 대규모 INSERT 문을 생성하는 것입니다. 다음 코드 예제에서는 이 기술을 설명합니다.

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    string insertCommand = "INSERT INTO [MyTable] ( mytext, num ) " +
        "VALUES (@p1, @p2), (@p3, @p4), (@p5, @p6), (@p7, @p8), (@p9, @p10)";

    SqlCommand cmd = new SqlCommand(insertCommand, connection);

    for (int i = 1; i <= 10; i += 2)
    {
        cmd.Parameters.Add(new SqlParameter("@p" + i.ToString(), "test"));
        cmd.Parameters.Add(new SqlParameter("@p" + (i+1).ToString(), i));
    }

    cmd.ExecuteNonQuery();
}

이 예제는 기본 개념을 보여주기 위한 것입니다. 보다 현실적인 시나리오에서는 쿼리 문자열과 명령 매개 변수를 동시에 생성하는 데 필요한 엔터티를 반복합니다. 쿼리 매개 변수는 총 2100개로 제한되기 때문에, 이러한 방식으로 처리되는 행의 총 수가 제한됩니다.

다음 임시 테스트 결과는 이런 형식으로 된 Insert 문의 성능을 밀리초 단위로 보여 줍니다.

작업	테이블 반환 매개 변수(ms)	단일 문 INSERT(ms)
1	32	20
10	30	25
100	33	51

참고 항목

결과는 벤치마크가 아닙니다. 이 문서의 타이밍 결과에 대한 정보를 참조하세요.

이 방법은 행이 100개 미만인 일괄 처리의 경우에 약간 더 빠를 수 있습니다. 이 기법은 향상 폭은 작지만 사용자의 특정 애플리케이션 시나리오에서 잘 작동할만한 또 다른 옵션입니다.

DataAdapter

DataAdapter 클래스를 사용하면 DataSet 개체를 수정한 다음, 변경 내용을 INSERT, UPDATE 및 DELETE 작업으로 제출할 수 있습니다. DataAdapter 를 이런 방식으로 사용하는 경우, 각각의 고유한 작업에 대해 개별 호출이 생성된다는 점에 유의해야 합니다. 성능을 향상시키려면 UpdateBatchSize 속성을 한 번에 일괄 처리해야 하는 여러 작업에 사용합니다. 자세한 내용은 DataAdapters를 사용하여 Batch 작업 수행을 참조하세요.

Entity Framework

Entity Framework Core에서 일괄 처리를 지원합니다.

XML

완전성을 위해 XML을 일괄 처리 전략으로 취급하는 것이 중요합니다. 하지만 XML 사용이 다른 메서드에 비해 이점이 없고 몇 가지 불편한 점이 있습니다. 접근 방법은 테이블 반환 매개 변수와 유사하지만 사용자 정의된 테이블 대신 XML 파일 또는 문자열이 저장 프로시저로 전달됩니다. 저장 프로시저는 저장 프로시저의 명령을 구문 분석합니다.

이 방법에는 여러 가지 불편한 점이 있습니다.

• XML 작업은 번거롭고 오류 가능성이 높습니다.
• 데이터베이스에서 XML을 구문 분석하면 CPU가 많이 사용될 수 있습니다.
• 대부분의 경우 이 방법은 테이블 반환 매개 변수보다 느립니다.

이러한 이유로 일괄 처리 쿼리에 XML을 사용하지 않는 것이 좋습니다.

일괄 처리 고려 사항

다음 섹션은 Azure SQL Database 및 Azure SQL Managed Instance 애플리케이션에서 일괄 처리를 사용하는 것에 대해 더 많은 참고 자료를 제공합니다.

균형 유지

아키텍처에 따라 일괄 처리에서 성능과 복원력이 절충될 수 있습니다. 예를 들어 역할이 예기치 않게 중단되는 시나리오를 고려해 보세요. 데이터의 행 하나가 손실되는 경우 영향은 제출되지 않은 행이 대량으로 손실되는 영향보다 작습니다. 지정된 기간 동안 행을 데이터베이스로 보내기 전에 버퍼링할 때 더 큰 위험이 있습니다.

이러한 절충으로 인해 일괄 처리하는 작업의 형식을 평가합니다. 덜 중요한 데이터를 사용하여 더 적극적으로 일괄 처리합니다(더 큰 일괄 처리 및 더 긴 시간).

Batch 크기

테스트에 따르면 대량의 배치를 작은 청크로 나누는 장점은 대체적으로 거의 없었습니다. 실제로 이러한 세분화가 큰 배치 하나를 제출하는 것보다 성능을 느리게 하는 결과를 초래하기도 했습니다. 예를 들어 행 1000개를 삽입하려는 시나리오를 고려해 보세요. 다음 표에서는 더 작은 일괄 처리로 나눌 때 테이블 반환 매개 변수를 사용하여 행 1000개를 삽입하는 데 걸리는 시간을 보여줍니다.

Batch 크기	반복 횟수	테이블 반환 매개 변수(ms)
1000	1	347
500	2	355
100	10	465
50	20	630

참고 항목

결과는 벤치마크가 아닙니다. 이 문서의 타이밍 결과에 대한 정보를 참조하세요.

1000개 행에 대한 최고의 성능은 모두를 한꺼번에 제출하는 것이라는 사실을 볼 수 있습니다. 다른 테스트(여기에 표시되지 않은)에서는 10,000개 행의 배치 하나를 5,000개 행의 배치 2개로 나눈 경우에 약간의 성능 향상이 있었습니다. 그러나 이러한 테스트의 테이블 스키마는 비교적 간단하므로 특정 데이터와 일괄 처리 크기에 대한 테스트를 수행하여 이러한 결과를 확인해야 합니다.

고려해야 할 또 다른 요인은 총 일괄 처리가 너무 커지면 Azure SQL 데이터베이스나 Azure SQL Managed Instance에서 일괄 처리 커밋을 제한하고 거부할 수 있다는 점입니다. 최상의 결과를 위해 특정 시나리오를 테스트하여 이상적인 일괄 처리 크기가 있는지 확인합니다. 런타임 시 일괄 처리 크기를 구성하여 성능이나 오류에 따라 빠르게 조정할 수 있도록 합니다.

마지막으로, 일괄 처리 크기와 일괄 처리와 관련된 위험 간의 균형을 조정합니다. 일시적인 오류 또는 역할 실패가 발생하는 경우에는 작업을 재시도하거나 배치의 데이터가 손실되어 발생하는 결과를 고려합니다.

병렬 처리

배치의 규모는 줄이면서 다수의 스레드를 사용하여 작업을 실행하는 방법을 취하면 어떨까요? 다시 말하지만, 테스트를 통해 여러 작은 다중 스레드 일괄 처리 성능이 일반적으로 단일 대규모 일괄 처리보다 더 저하된다는 점을 알았습니다. 다음 테스트는 1000개의 행을 하나 이상의 병렬 배치에 삽입하려고 합니다. 이 테스트에서는 더 많은 동시 일괄 처리가 실제로 성능을 저하된 방식을 보여줍니다.

일괄 처리 크기 [반복]	스레드 2개(ms)	스레드 4개(ms)	스레드 6개(ms)
1000 [1]	277	315	266
500 [2]	5:48	278	256
250 [4]	405	329	265
100 [10]	488	439	391

참고 항목

결과는 벤치마크가 아닙니다. 이 문서의 타이밍 결과에 대한 정보를 참조하세요.

병렬 처리로 인한 성능의 저하에는 몇 가지 잠재적인 이유가 있습니다.

• 동시 네트워크 호출이 하나가 아닌 여러 개 있습니다.
• 단일 테이블에 대해 여러 개의 작업이 수행되면 경합과 차단이 발생할 수 있습니다.
• 멀티 스레드와 관련된 오버헤드가 있습니다.
• 여러 연결을 여는 비용은 병렬 처리의 이점보다 더 큽니다.

다른 테이블이나 데이터베이스를 대상으로 하는 경우 이 전략으로 일부 성능 이점을 확인할 수 있습니다. 데이터베이스 분할이나 페더레이션은 이 방법을 위한 시나리오입니다. 분할은 여러 데이터베이스를 사용하고 서로 다른 데이터를 각 데이터베이스로 라우팅합니다. 작은 일괄 처리 각각이 다른 데이터베이스로 이동하는 경우 병렬로 작업을 수행하는 것이 더 효율적일 수 있습니다. 그러나 성능 이점은 솔루션에서 데이터베이스 분할을 사용하기 위한 결정의 기초로 사용할 만큼 크지 않습니다.

일부 디자인에서는 더 작은 일괄 처리를 병렬로 실행하면 로드 중인 시스템에서 요청 처리량이 향상될 수 있습니다. 이 경우 더 큰 단일 일괄 처리를 더 빠르게 처리하더라도 여러 일괄 처리를 병렬로 처리하는 것이 더 효율적일 수 있습니다.

병렬 실행을 사용하는 경우 최대 작업자 스레드 수를 제어하는 것이 좋습니다. 숫자가 작을수록 경합이 줄어들고 실행 시간이 빨라질 수 있습니다. 또한 연결 및 트랜잭션 모두에서 대상 데이터베이스에 로드를 추가하는 것을 고려합니다.

관련 성능 요인

데이터 베이스 성능에 대한 전형적인 지침은 일괄 처리에도 영향을 미칩니다. 예를 들어 기본 키가 크거나 비클러스터형 인덱스가 많은 테이블에서는 삽입 성능이 저하됩니다.

테이블 반환 매개 변수가 저장 프로시저를 사용하는 경우에는 프로시저의 시작에 SET NOCOUNT ON 명령을 사용할 수 있습니다. 이 문은 프로시저에서 영향을 받는 행 수의 반환을 중지합니다. 하지만 테스트에서는 SET NOCOUNT ON 의 사용이 효과가 없거나 성능을 감소시켰습니다. 테스트 저장 프로시저는 테이블 반환 매개 변수의 단일 INSERT 명령으로 간단했습니다. 더 복잡한 저장 프로시저에서 이 문의 이점을 활용할 수 있습니다. 그러나 저장 프로시저에 SET NOCOUNT ON을 추가한다고 해서 성능이 자동으로 향상된다고 가정하지 마세요. 효과를 이해하려면 SET NOCOUNT ON 문을 사용하거나 사용하지 않고 저장 프로시저를 테스트합니다.

일괄 처리 시나리오

다음 섹션에서는 세 가지 애플리케이션 시나리오에서 테이블 반환 매개 변수를 사용하는 방법을 설명합니다. 첫 번째 시나리오에서는 버퍼링 및 일괄 처리가 함께 작동하는 방식을 보여줍니다. 두 번째 시나리오는 하나의 저장 프로시저 호출로 마스터-세부 정보 작업 수행하여 성능을 향상시킵니다. 마지막 시나리오에서는 "UPSERT" 작업에서 테이블 반환 매개 변수를 사용하는 방법을 보여줍니다.

버퍼링

일괄 처리를 위한 확실한 시나리오가 있지만 지연된 처리에서 일괄 처리를 활용할 수 있는 시나리오가 많이 있습니다. 그러나 처리가 지연되면 예기치 않은 오류가 발생할 경우 데이터가 손실될 위험이 커집니다. 이러한 위험을 이해하고 그에 따른 결과를 고려하는 것이 중요합니다.

예를 들어 각 사용자의 탐색 기록을 추적하는 웹 애플리케이션을 고려해 보겠습니다. 각 페이지 요청에 대해 애플리케이션에서 사용자의 페이지 보기를 기록하도록 데이터베이스를 호출할 수 있습니다. 그러나 사용자 탐색 활동을 버퍼링한 다음, 이 데이터를 일괄 처리하여 데이터베이스로 전송하여 성능과 확장성을 향상시킬 수 있습니다. 경과된 시간 또는 버퍼 크기를 기준으로 데이터베이스 업데이트를 트리거할 수 있습니다. 예를 들어 규칙에서 20초 후 또는 버퍼가 항목 1000개에 도달할 때 일괄 처리를 처리해야 한다고 지정할 수 있습니다.

다음 코드 예제에서는 Reactive Extensions - Rx를 사용하여 모니터링 클래스에서 발생하는 버퍼링된 이벤트를 처리합니다. 버퍼가 차거나 제한 시간에 도달하면, 사용자 데이터 배치는 테이블 반환 매개 변수와 함께 데이터베이스로 전송됩니다.

다음 NavHistoryData 클래스는 사용자 탐색 세부 정보를 모델링합니다. 여기에는 사용자 ID, 액세스된 URL 및 액세스 시간과 같은 기본 정보가 포함됩니다.

public class NavHistoryData
{
    public NavHistoryData(int userId, string url, DateTime accessTime)
    { UserId = userId; URL = url; AccessTime = accessTime; }
    public int UserId { get; set; }
    public string URL { get; set; }
    public DateTime AccessTime { get; set; }
}

NavHistoryDataMonitor 클래스는 사용자 탐색 데이터를 데이터베이스에 버퍼링합니다. OnAdded 이벤트를 발생시켜 응답하는 RecordUserNavigationEntry 메서드가 포함됩니다. 다음 코드는 Rx를 사용하여 이벤트를 기반으로 관측 가능한 컬렉션을 만드는 생성자 논리를 보여줍니다. 그런 다음, Buffer 메서드를 사용하여 이 관찰 가능한 컬렉션을 구독합니다. 오버로드는 버퍼가 20초 또는 항목 1000개마다 전송되도록 지정합니다.

public NavHistoryDataMonitor()
{
    var observableData =
        Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

    observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
}

처리기는 버퍼링된 모든 항목을 테이블 반환 형식으로 변환한 다음, 이 형식을 일괄 처리를 처리하는 저장 프로시저에 전달합니다. 다음 코드에서는 NavHistoryDataEventArgs 및 NavHistoryDataMonitor 클래스 모두에 대한 전체 정의를 보여줍니다.

public class NavHistoryDataEventArgs : System.EventArgs
{
    public NavHistoryDataEventArgs(NavHistoryData data) { Data = data; }
    public NavHistoryData Data { get; set; }
}

public class NavHistoryDataMonitor
{
    public event EventHandler<NavHistoryDataEventArgs> OnAdded;

    public NavHistoryDataMonitor()
    {
        var observableData =
            Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

        observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
    }

처리기는 버퍼링된 모든 항목을 테이블 반환 형식으로 변환한 다음, 이 형식을 일괄 처리를 처리하는 저장 프로시저에 전달합니다. 다음 코드에서는 NavHistoryDataEventArgs 및 NavHistoryDataMonitor 클래스 모두에 대한 전체 정의를 보여줍니다.

    public class NavHistoryDataEventArgs : System.EventArgs
    {
        if (OnAdded != null)
            OnAdded(this, new NavHistoryDataEventArgs(data));
    }

    protected void Handler(IList<EventPattern<NavHistoryDataEventArgs>> items)
    {
        DataTable navHistoryBatch = new DataTable("NavigationHistoryBatch");
        navHistoryBatch.Columns.Add("UserId", typeof(int));
        navHistoryBatch.Columns.Add("URL", typeof(string));
        navHistoryBatch.Columns.Add("AccessTime", typeof(DateTime));
        foreach (EventPattern<NavHistoryDataEventArgs> item in items)
        {
            NavHistoryData data = item.EventArgs.Data;
            navHistoryBatch.Rows.Add(data.UserId, data.URL, data.AccessTime);
        }

        using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
        {
            connection.Open();

            SqlCommand cmd = new SqlCommand("sp_RecordUserNavigation", connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(
                new SqlParameter()
                {
                    ParameterName = "@NavHistoryBatch",
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "NavigationHistoryTableType",
                    Value = navHistoryBatch,
                });

            cmd.ExecuteNonQuery();
        }
    }
}

이 버퍼링 클래스를 사용하기 위해서 애플리케이션은 정적 NavHistoryDataMonitor 개체를 생성합니다. 사용자가 페이지에 액세스할 때마다 애플리케이션에서 NavHistoryDataMonitor.RecordUserNavigationEntry 메서드를 호출합니다. 버퍼링 논리는 이러한 항목을 데이터베이스로 일괄 전송하는 작업을 진행합니다.

마스터 세부 정보

테이블 반환 매개 변수는 간단한 INSERT 시나리오에 유용합니다. 그러나 테이블을 2개 이상 포함하는 삽입을 일괄 처리하는 것이 더 어려울 수 있습니다. “마스터/세부 정보” 시나리오가 좋은 예제입니다. 마스터 테이블은 기본 엔터티를 식별합니다. 세부 정보 테이블 하나 이상은 엔터티에 대한 더 많은 데이터를 저장합니다. 이 시나리오에서 외래 키 관계는 고유 마스터 엔터티에 세부 정보의 관계를 적용합니다. 간소화된 버전의 PurchaseOrder 테이블과 연결된 OrderDetail 테이블을 고려합니다. 다음 Transact-SQL은 OrderID, OrderDate, CustomerID 및 Status 등 열이 4개 있는 PurchaseOrder 테이블을 만듭니다.

CREATE TABLE [dbo].[PurchaseOrder](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_PurchaseOrder]
PRIMARY KEY CLUSTERED ( [OrderID] ASC ))

주문마다 제품 구매가 하나 이상 포함됩니다. 이 정보는 PurchaseOrderDetail 테이블에서 캡처됩니다. 다음 Transact-SQL은 5개의 열 즉, OrderID, OrderDetailID, ProductID, UnitPrice, OrderQty를 포함하는 PurchaseOrderDetail 테이블을 생성합니다.

CREATE TABLE [dbo].[PurchaseOrderDetail](
[OrderID] [int] NOT NULL,
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
CONSTRAINT [PrimaryKey_PurchaseOrderDetail] PRIMARY KEY CLUSTERED
( [OrderID] ASC, [OrderDetailID] ASC ))

PurchaseOrderDetail 테이블의 OrderID 열은 PurchaseOrder 테이블의 주문을 참조해야 합니다. 다음 외래 키 정의에는 다음 제약 조건이 적용됩니다.

ALTER TABLE [dbo].[PurchaseOrderDetail]  WITH CHECK ADD
CONSTRAINT [FK_OrderID_PurchaseOrder] FOREIGN KEY([OrderID])
REFERENCES [dbo].[PurchaseOrder] ([OrderID])

테이블 반환 매개 변수를 사용하려면 대상 테이블마다 사용자 정의 테이블 형식 하나가 있어야 합니다.

CREATE TYPE PurchaseOrderTableType AS TABLE
( OrderID INT,
    OrderDate DATETIME,
    CustomerID INT,
    Status NVARCHAR(50) );
GO

CREATE TYPE PurchaseOrderDetailTableType AS TABLE
( OrderID INT,
    ProductID INT,
    UnitPrice MONEY,
    OrderQty SMALLINT );
GO

그 후 이런 형식의 테이블을 허용하는 저장 프로시저를 정의합니다. 이 절차를 수행하면 애플리케이션은 단일 호출에서 주문 및 주문 세부 정보 집합을 로컬로 일괄 처리할 수 있습니다. 다음 Transact-SQL은 이 구매 주문 예제에 대한 전체 저장 프로시저 선언을 제공합니다.

CREATE PROCEDURE sp_InsertOrdersBatch (
@orders as PurchaseOrderTableType READONLY,
@details as PurchaseOrderDetailTableType READONLY )
AS
SET NOCOUNT ON;

-- Table that connects the order identifiers in the @orders
-- table with the actual order identifiers in the PurchaseOrder table
DECLARE @IdentityLink AS TABLE (
SubmittedKey int,
ActualKey int,
RowNumber int identity(1,1)
);

-- Add new orders to the PurchaseOrder table, storing the actual
-- order identifiers in the @IdentityLink table
INSERT INTO PurchaseOrder ([OrderDate], [CustomerID], [Status])
OUTPUT inserted.OrderID INTO @IdentityLink (ActualKey)
SELECT [OrderDate], [CustomerID], [Status] FROM @orders ORDER BY OrderID;

-- Match the passed-in order identifiers with the actual identifiers
-- and complete the @IdentityLink table for use with inserting the details
WITH OrderedRows As (
SELECT OrderID, ROW_NUMBER () OVER (ORDER BY OrderID) As RowNumber
FROM @orders
)
UPDATE @IdentityLink SET SubmittedKey = M.OrderID
FROM @IdentityLink L JOIN OrderedRows M ON L.RowNumber = M.RowNumber;

-- Insert the order details into the PurchaseOrderDetail table,
-- using the actual order identifiers of the master table, PurchaseOrder
INSERT INTO PurchaseOrderDetail (
[OrderID],
[ProductID],
[UnitPrice],
[OrderQty] )
SELECT L.ActualKey, D.ProductID, D.UnitPrice, D.OrderQty
FROM @details D
JOIN @IdentityLink L ON L.SubmittedKey = D.OrderID;
GO

이 예제에서 로컬로 정의된 @IdentityLink 테이블은 새로 삽입된 행의 실제 OrderID 값을 저장합니다. 이 주문 식별자는 @orders 및 @details 테이블 반환 매개 변수의 임시 OrderID 값과 다릅니다. 이러한 이유로 @IdentityLink 테이블은 @orders 매개 변수의 OrderID 값을 PurchaseOrder 테이블의 새로운 행에 대한 실제 OrderID 값에 연결합니다. 이 단계에서 @IdentityLink 테이블은 외래 키 제약 조건을 충족하는 실제 OrderID로 주문 세부 정보를 삽입하는데 도움이 될 수 있습니다.

이 저장 프로시저는 코드나 다른 Transact-SQL 호출에서 사용될 수 있습니다. 코드 예제는 이 문서의 테이블 반환 매개 변수 섹션을 참조하세요. 다음 Transact-SQL에서는 sp_InsertOrdersBatch를 호출하는 방법을 보여줍니다.

declare @orders as PurchaseOrderTableType
declare @details as PurchaseOrderDetailTableType

INSERT @orders
([OrderID], [OrderDate], [CustomerID], [Status])
VALUES(1, '1/1/2013', 1125, 'Complete'),
(2, '1/13/2013', 348, 'Processing'),
(3, '1/12/2013', 2504, 'Shipped')

INSERT @details
([OrderID], [ProductID], [UnitPrice], [OrderQty])
VALUES(1, 10, $11.50, 1),
(1, 12, $1.58, 1),
(2, 23, $2.57, 2),
(3, 4, $10.00, 1)

exec sp_InsertOrdersBatch @orders, @details

이 솔루션은 각 배치가 1로 시작하는 OrderID 값 집합을 사용하도록 합니다. 이러한 임시 OrderID 값은 일괄 처리 관계를 설명하지만 실제 OrderID 값은 삽입 작업 시에 결정됩니다. 앞선 예제에서 같은 문을 반복적으로 실행하고 데이터베이스에서 고유한 순서를 생성할 수 있습니다. 이런 이유 때문에 일괄 처리 기법을 사용할 때는 중복 주문을 방지하는 코드 또는 데이터베이스 논리를 더 추가하는 것을 고려하는 좋습니다.

이 예제에서는 마스터-세부 정보 작업과 같은 더 복잡한 데이터베이스 작업을 테이블 반환 매개 변수를 사용하여 일괄 처리할 수 있음을 보여줍니다.

업서트

또 다른 일괄 처리 시나리오에는 동시에 기존 행을 업데이트하고 새 행을 삽입하는 작업이 포함됩니다. 이 작업을 "UPSERT"(업데이트 + 삽입) 작업이라고도 합니다. INSERT 및 UPDATE를 별도로 호출하는 대신 MERGE 문을 적절하게 대체할 수 있습니다. MERGE 문은 단일 호출에서 삽입 및 업데이트 작업을 모두 수행할 수 있습니다. MERGE 문 잠금 메커니즘은 별도의 INSERT 및 UPDATE 문과 다르게 작동합니다. 프로덕션에 배포하기 전에 특정 워크플로를 테스트합니다.

MERGE 문과 함께 테이블 반환 매개 변수를 사용하여 업데이트 및 삽입을 수행할 수 있습니다. 예를 들어 EmployeeID, FirstName, LastName, SocialSecurityNumber 열을 포함하는 간소화된 Employee 테이블이 있습니다.

CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[SocialSecurityNumber] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_Employee] PRIMARY KEY CLUSTERED
([EmployeeID] ASC ))

이 예제에서는 SocialSecurityNumber가 고유하여 여러 직원의 MERGE를 수행한다는 사실을 사용할 수 있습니다. 우선, 사용자 정의 테이블 형식을 만듭니다.

CREATE TYPE EmployeeTableType AS TABLE
( Employee_ID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    SocialSecurityNumber NVARCHAR(50) );
GO

다음으로, 업데이트 및 삽입을 수행하기 위해 MERGE 문을 사용하는 코드를 작성하거나 저장 프로시저를 만듭니다. 다음 예제는 EmployeeTableType 형식의 테이블 반환 매개 변수 @employees에서 MERGE 문을 사용합니다. @employees 테이블 콘텐츠는 여기에 표시되지 않습니다.

MERGE Employee AS target
USING (SELECT [FirstName], [LastName], [SocialSecurityNumber] FROM @employees)
AS source ([FirstName], [LastName], [SocialSecurityNumber])
ON (target.[SocialSecurityNumber] = source.[SocialSecurityNumber])
WHEN MATCHED THEN
UPDATE SET
target.FirstName = source.FirstName,
target.LastName = source.LastName
WHEN NOT MATCHED THEN
    INSERT ([FirstName], [LastName], [SocialSecurityNumber])
    VALUES (source.[FirstName], source.[LastName], source.[SocialSecurityNumber]);

자세한 내용은 MERGE 문에 대한 설명서 또는 예제를 참조하세요. 별도의 INSERT 및 UPDATE 작업을 사용하여 다중 단계 저장 프로시저 호출에서 같은 작업을 수행할 수 있지만 MERGE 문이 더 효율적입니다. 데이터베이스 코드에서 INSERT 및 UPDATE에 대한 데이터베이스 호출 2개를 요구하지 않고 MERGE 문을 직접 사용하는 Transact-SQL 호출을 생성할 수도 있습니다.

권장 사항 요약

다음 목록에서는 이 문서에서 설명한 일괄 처리 권장 사항을 요약 정리합니다.

• 버퍼링과 일괄 처리를 사용하여 Azure SQL 데이터베이스 및 Azure SQL Managed Instance 애플리케이션의 성능과 확장성을 향상시킵니다.
• 일괄 처리/버퍼링과 복원력 간의 절충을 이해합니다. 역할 실패 시 처리되지 않은 중요 비즈니스용 데이터 일괄 처리가 손실될 위험이 일괄 처리 성능 이점보다 클 수 있습니다.
• 대기 시간이 단축되도록 단일 데이터 센터 내에서 데이터베이스에 대한 모든 호출을 유지하려고 합니다.
• 단일 일괄 처리 기법을 선택하는 경우, 테이블 반환 매개 변수가 최고의 성능 및 유연성을 제공합니다.
• 가장 빠른 삽입 성능을 위해 다음 일반적인 지침을 따르지만 시나리오를 테스트합니다.
  • 행이 100개 미만(< 100)이면 단일 매개 변수가 있는 INSERT 명령을 사용합니다.
  • 행이 1000개 미만(< 1000)이면 테이블 반환 매개 변수를 사용합니다.
  • 행이 1000개 이상(> 1000)이면 SqlBulkCopy를 사용합니다.
• 업데이트 및 삭제 작업의 경우 테이블 매개 변수의 각 행에 대해 올바른 작업을 결정하는 저장 프로시저 논리와 함께 테이블 반환 매개 변수를 사용합니다.
• 일괄 처리 크기 지침:
  • 사용자의 애플리케이션 및 비즈니스 요구 사항에 합당한 최대 배치 크기를 사용합니다.
  • 대규모 일괄 처리 성능 이점과 일시적 또는 치명적인 오류 위험 간의 균형을 조정합니다. 일괄 처리에서 데이터 재시도나 손실의 결과는 무엇인가요?
  • Azure SQL 데이터베이스나 Azure SQL Managed Instance에서 일괄 처리를 거부하지 않는지 확인하기 위해 가장 큰 일괄 처리 크기를 테스트합니다.
  • 일괄 처리 크기나 버퍼링 시간과 같은 일괄 처리를 제어하는 구성 설정을 만듭니다. 이러한 설정은 유연성을 제공합니다. 클라우드 서비스를 다시 배포하지 않고도 프로덕션에서 일괄 처리 동작을 변경할 수 있습니다.
• 데이터베이스 하나의 단일 테이블에서 작동하는 일괄 처리의 병렬 실행을 방지합니다. 단일 일괄 처리를 여러 작업자 스레드로 나누는 경우 테스트를 실행하여 이상적인 스레드 수를 결정합니다. 지정되지 않은 임계값을 초과하면 더 많은 스레드의 성능이 향상되지 않고 저하됩니다.
• 더 많은 시나리오를 위해 일괄 처리를 구현하는 방법으로 크기와 시간에 대한 버퍼링을 고려합니다.

다음 단계

이 문서에서는 주로 일괄 처리와 관련된 데이터베이스 디자인 및 코딩 기술을 통해 애플리케이션 성능과 확장성을 향상시킬 수 있는 방법을 살펴봤습니다. 하지만 이것은 사용자의 전반적인 전략 중 한 가지 요소에 불과합니다. 성능과 확장성을 향상시키는 더 많은 방법은 데이터베이스 성능 지침 및 탄력적 풀의 가격 및 성능 고려 사항을 참조하세요.