Jump to content
과거의 기술자료(읽기 전용): https://tech.devgear.co.kr ×
과거의 기술자료(읽기 전용): https://tech.devgear.co.kr

[DocWiki 번역] Array DML (FireDAC)

Kori
 공유하기

Recommended Posts

Kori Proficient

Kori

에 포스트됨
에 포스트됨

Docwiki에 있는 "Array DML (FireDAC)"를 번역한 글 (번역일: 2022년 3월 12일)

위로 가기: [DocWiki 번역] 명령(command)을 가지고 작업하기 (FireDAC)

목차

 

일반 사항 (General)

Array DML은 Params(파라미터들) 배열을 DBMS 명령(Command) 1개에 담아서 보내는 기술이다. Params 안에는 파라미터가 여러개 있는데, 각 파라미터마다 자신의 배열을 가지고 있고, 여기에는 DBMS로 전달할 값들이 들어간다. 따라서 각 파라미터 안에 들어있는 배열들은 그 길이가 모두 같다. Params 배열이 준비되고 나면, FireDAC은 SQL 명령 1개과 이 Params 배열을 함께 묶어서 한번에 DBMS로 전달하고, 각 행(Row) 별로 명령을 실행하도록 요청한다. 이 기술은 DBMS와 클라이언트 사이의 통신 횟수를 줄이고, DBMS가 명령을 끊임없이 처리도록 하고, 실행 속도를 향상시킨다.

처리 과정은 아래 그림과 같다.

arraydml.png

그림. FireDAC 애플리케이션에서 삽입할 데이터 5개를 하나로 묶어서 전달하면, RDBMS에서는 이 묶음 명령을 받아서 5개 행(Row)을 삽입한다

FireDAC을 설명할 때, 묶음 명령 실행(batch command execution)과 Array DML 실행(Array DML execution)은 같은 뜻이다. Array DML은 파라미터를 가질 수 있는 명령(INSERT, UPDATE, DELETE ...)이라면 거의 모든 경우에 사용할 수 있다. 여기에는 저장 프로시저 (SP) 호출도 포함된다. FireDAC은 해당 DBMS의 네이티브 API 기능을 사용하여 Array DML을 구현하는데, 만약 이 API를 지원하지 않는 DBMS라면 Array DML 실행을 흉내낸다. 

아래 표는 각 DBMS 별 Array DML 기능 목록이다.

DBMS Array DML 구현 Array DML 모드 Array DML 한도 시 증상
Advantage Database 모방하기 aeUpToFirstError  
DataSnap server 모방하기 aeUpToFirstError  
IBM DB2 네이티브 aeCollectAllErrors  
Informix 네이티브 aeCollectAllErrors  
InterBase v < XE3 모방하기 aeUpToFirstError  
InterBase v >= XE3 네이티브 (Command Batch API) aeUpToFirstError  
Firebird v < 2.1 모방하기 aeUpToFirstError  
Firebird v >= 2.1 네이티브 (EXECUTE BLOCK) aeOnErrorUndoAll "Too many contexts" 에러
Microsoft SQL Server 네이티브 aeCollectAllErrors "Access violation" 에러 발생 가능
Microsoft Access database 모방하기 aeUpToFirstError  
MySQL Server

네이티브 (INSERT with multiple VALUES)

aeOnErrorUndoAll  
Oracle Server 네이티브  (OCI Array DML) aeUpToFirstError 애플리케이션 행업. 명시적 제한 - 배열 항목 65K
PostgreSQL v < 8.1 모방하기 aeUpToFirstError  
PostgreSQL v >= 8.1 네이티브  (INSERT /MERGE with multiple VALUES) aeOnErrorUndoAll  
SQLite database v < 3.7.11 모방하기 aeUpToFirstError  
SQLite database v >= 3.7.11
  • 모방하기, Params.BindMode = pbByName 일때
  • 네이티브 (INSERT with multiple VALUES), Params.BindMode = pbByNumber 일때
  • aeUpToFirstError
  • aeOnErrorUndoAll
 
Sybase SQL Anywhere 네이티브 aeUpToFirstError  
Teradata Database 네이티브 aeOnErrorUndoAll  

주의:

 

명령 실행 (Command execution)

Array DML을 실행하기 전에, 애플리케이션 코드에서는 반드시 파라미터 값 배열을 설정해야 한다. 첫째, Params.ArraySize에 값을 할당하여 배열의 길이를 지정한다. 이 프로퍼티에 명시한 값은 모든 파라미터의 ArraySize 프로퍼티에 할당된다. 따라서, Params.ArraySize에 값을 할당하기 전에 Params 컬렉션이 비어있으면 안된다. 둘째, 각 파라미터의 배열에 값을 할당한다. TADParam 클래스에는 AsXXXs [AIndex: Integer] 형식으로된 프로퍼티 세트가 있다. 이 세트는 파라미터의 첫번째 인덱스를 받아서 하나를 처리하는 AsXXX 프로퍼티와 기타 프로퍼티와 메소드와 비슷하다. 예를 들어, 

FDQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// 이 FDQuery1.Params 컬렉션에 담긴 파라미터는 3개이다.
FDQuery1.Params.ArraySize := 100;
for i := 0 to 100-1 do begin
  FDQuery1.Params[0].AsIntegers[i] := i;
  FDQuery1.Params[1].AsStrings[i] := 'qwe';
  FDQuery1.Params[2].Clear(i);
end;

TFDCustomCommand, TFDQuery, TFDStoredProc에는 Execute (ATimes: Integer = 0; AOffset: Integer = 0) 메소드가 있다. 여기에서 ATimes에는 배열의 길이를 지정한다. AOffset에는 배열에서 맨 처음 꺼낼 항목의 인덱스를 지정한다. 그 결과, 명령 실행 횟수는  ATimes 번이고, AOffset 행(Row)부터 시작한다. ATimes는 반드시 Params.ArraySize 보다 작거나 같아야 한다. 예를 들어, 

FDQuery1.Execute(100, 0);

Array DML이 실행되고 나면, RowsAffected 프로퍼티에는 성공한 실행 횟수가 담긴다. 실행 대상이 된 전체 Row(행)의 갯수가 담기는 것이 아니다. 예를 들어, 

ShowMessage(IntToStr(FDQuery1.RowsAffected));

 

에러 핸들링 (Error handling)

TFDAdaptedDataSet, TFDQuery, TFDStoredProc는 에러를 탐지할 수 있다. 이때에는 OnExecuteError 이벤트 핸들러를 사용한다. 이 에러 핸들러가 할당되지 않은 상태에서 에러가 발생하면, Execute는 예외를 일으키고 RowsAffected가 업데이트도 된다.

TFDAdaptedDataSet.OnExecuteError 이벤트 핸들러가 할당되면, 이 이벤트 핸들러는 (원인이 된) 원래 예외 오브젝트, 현재 처리 순번, 오프셋(offset)을 받는다 뿐만 아니라 이후 처리를 AAction 값에 담아서 반환할 수도 있다. AError.Errors[...]에는 에러 하나 또는 여러개가 담긴다. AError.Errors[i].RowIndex는 실패한 행(Row)의 인덱스이다. 주의, OnExecuteError는 구문 오류 또는 ATimes = 1 일때는 호출되지 않는다.

예를 들어, 

procedure TForm1.FDQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AError: EFDDBEngineException; var AAction: TFDErrorAction);
begin
  if AError.Errors[0].Kind = ekUKViolated then
    AAction := eaSkip
  else
    AAction := eaFail;
end;

정확한 행위는 DBMS와 해당 Array DML 모드에 따라 다르다.

Array DML 모드 설명
aeOnErrorUndoAll 첫번째 에러에서 실행을 멈춘다. 반영된 배열 안에 있는 항목 중에서 성공한 항목은 모두 취소된다. 그리고 나서 FireDAC은 하나씩 실행하는 모드로 전환하여 전체 배열을 다시 실행한다. 이후은 aeUpToFirstError와 유사하다. aeUpToFirstError를 볼것
aeUpToFirstError 첫번째 에러에서 실행을 멈춘다. 반영된 배열 안에 있는 항목 중에서 성공한 항목은 모두 저장된다. DBMS는 첫 에러가 발생한 항목의 인덱스를 반환한다. RowsAffected = 반영된 배열 안에 있는 항목 중에서 성공한 항목의 갯수. AError.Errors[...]에 담긴 에러 컬렉션에는 실패한 행(Row)를 가리키는 에러 하나 또는 여러개가 들어간다. AError.Errors[i].RowIndex는 실패한 행(Row)의 인덱스이다.
aeCollectAllErrors 배열 항목 모두 실행된다. 반영된 배열 안에 있는 항목 중에서 성공한 항목은 모두 저장된다. DBMS는 실패한 배열 항목의 인덱스를 하나씩 반환한다. RowsAffected = 반영된 배열 안에 있는 항목 중에서 성공한 항목의 갯수. AError.Errors[...]에 담긴 에러 컬렉션에는 실패한 행(Row) 별로 하나씩 담긴다. AError.Errors[i].RowIndex는 실패한 행(Row)의 인덱스이다.

주의: ResourceOptions.ArrayDMLSize을 1로 지정하면 배열 실행 모드가 aeUpToFirstError로 지정된다. 현재 연결된 DBMS의 Array DML 모드를 얻으려면 다음과 같이 한다. 

if FDConnection1.ConnectionMetaDataIntf.ArrayExecMode = aeOnErrorUndoAll then
  ....

 

문제 해결 (Troubleshooting)

파라미터 설정을 올바르게 하는 것이 중요하다. 여기에는 문자열(string) 파라미터에서 Size 프로퍼티를 올바르게 설정하는 것도 포함된다. 예를  들어, 오라클을 연결할 때 별도로 Size를 명시하지 않으면 FireDAC은 4000 바이트를 ftString / ftWideString 파라미터에 할당한다. 따라서 10,000개 값을 담으면 40 Mb 버퍼가 할당된다. 이런 파라미터가 많다면, 애플리케이션에서 시스템 메모리를 모두 잡아먹을 수 있다.

DBMS 대부분은 묵시적인 Array DML 크기 제한이 있다. 이 제한은 DBMS 클라이언트 라이브러리의 버퍼 크기, 또는 허용되는 최대 네트워크 패킷에 따라 다르다. 한도에 도달하면,  ResourceOptions.ArrayDMLSize 옵션을 사용하여 커다란 Array DML을 투명하게 작은 조각으로 나누도록 한다.

예제 1

IFDPhysCommand에서 Array DML을 사용 하기: 

var
  oCmd: IFDPhysCommand;
……
  with oCmd do begin
    CommandText := 'insert into Customers (ID, Name) values (:ID, :Name)';
    // 각 파라미터 별 타입 지정
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftString;
    Params[1].Size := 40;
    // 파라미터의 배열 크기 지정
    Params.ArraySize := 10000;
    // 파라미터에 값을 지정
    for i := 0 to 10000 - 1 do begin
      Params[0].AsIntegers[i] := i;
      Params[1].AsStrings[i] := 'Somebody ' + IntToStr(i);
    end;
    // 묶음 일괄 실행
    Execute(10000, 0);
  end;

 

예제 2

TFDQuery에서 Array DML을 사용하고 에러 핸들링 하기: 

procedure TForm1.FDQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AException: EFDDBEngineException; var AAction: TFDErrorAction);
begin
  case AException.Errors[0].Kind of
  ekPKViolated:
    begin
      // ID를 고유하게 고정
      FDQuery1.Params[0].AsIntegers[AException.Errors[0].RowIndex] := AException.Errors[0].RowIndex;
      AAction := eaRetry;
    end;
  ekFKViolated:
    // 참조하는 키인 RegionID가 없는 경우, 해당 행(Row) 처리 생략
    AAction := eaSkip;
  else
    AAction := eaFail;
  end;
end;

procedure TForm1.Button1Click(ASender: TObject);
begin
  with FDQuery1 do begin
    SQL.Text := 'insert into Customers (ID, RegionID, Name, Note) values (:ID, :RegionID, :Name, :Note)';
    // 각 파라미터 별 타입 지정
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftInteger;
    Params[2].DataType := ftString;
    Params[2].Size := 40;
    Params[3].DataSize := ftMemo;
    // 파라미터의 배열 크기 지정
    Params.ArraySize := 10000;
    // 파라미터에 값을 지정
    for i := 0 to 10000 - 1 do begin
      if i mod 100 = 0 then
        // 일부러 PK 위반 일으키기
        Params[0].AsIntegers[i] := i - 1
      else
        Params[0].AsIntegers[i] := i;
      Params[1].AsIntegers[i] := GetRegionIdForCustomer(i);
      Params[2].AsStrings[i] := 'Somebody ' + IntToStr(i);
      Params[3].Clear(i);
    end;
    // 묶음 일괄 실행
    Execute(10000, 0);
  end;
end;

 

기타 자료 (See Also)

예제 (Samples)

이 댓글 링크
다른 사이트에 공유하기

이 토의에 참여하세요

지금 바로 의견을 남길 수 있습니다. 그리고 나서 가입해도 됩니다. 이미 회원이라면, 지금 로그인하고 본인 계정으로 의견을 남기세요.

Guest
이 토픽(기고/질문)에 답하기

×   서식있는 텍스트로 붙여넣기.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   이전에 작성한 콘텐츠가 복원되었습니다..   편집창 비우기

×   You cannot paste images directly. Upload or insert images from URL.

×
 공유하기
토픽(기고/질문) 목록으로 가기
×
×
  • Create New...

중요한 정보

이용약관 개인정보보호정책 이용규칙 이 사이트가 더 잘 작동하기 위해 방문자의 컴퓨터에 쿠키가 배치됩니다. 쿠키 설정 변경에서 원하는 설정을 할 수 있습니다. 변경하지 않으면 쿠키를 허용하는 것으로 이해합니다.