JDBC 드라이버의 프리페어드 스테이트먼트 매개 변수 성능

JDBC 드라이버 다운로드

이 문서에서는 준비된 문 매개 변수가 Microsoft JDBC Driver for SQL Server의 서버 쪽 성능에 미치는 영향을 설명하고 매개 변수 사용 최적화에 대한 지침을 제공합니다.

준비된 문 매개 변수 이해

준비된 문은 SQL Server가 쿼리를 한 번 구문 분석, 컴파일 및 최적화한 다음 실행 계획을 여러 번 다시 사용할 수 있도록 하여 상당한 성능 이점을 제공합니다. 그러나 매개 변수를 지정하는 방식은 이 성능 혜택에 큰 영향을 줄 수 있습니다.

준비된 문을 만들 때 SQL Server는 다음을 비롯한 매개 변수 메타데이터를 기반으로 실행 계획을 생성합니다.

• 데이터 형식
• 정밀도(숫자 형식의 경우)
• 소수 자릿수(소수점 형식의 경우)
• 최대 길이(문자열 및 이진 형식의 경우)

SQL Server는 이 메타데이터를 사용하여 쿼리 실행 계획을 최적화하기 때문에 이 메타데이터가 중요합니다. 이러한 매개 변수 특성을 변경하면 SQL Server에서 기존 계획을 취소하고 새 계획을 만들면 성능이 저하됩니다.

매개 변수 변경이 성능에 미치는 영향

매개 변수 형식 변경

준비된 문의 매개 변수 형식이 실행 간에 변경되면 SQL Server는 문을 다시 준비해야 합니다. 이 배상에는 다음이 포함됩니다.

1. SQL 문을 다시 구문 분석합니다.
2. 새 실행 계획 컴파일
3. 새 계획 캐싱(캐싱을 사용하는 경우).

다음 예시를 참조하세요.

String sql = "SELECT * FROM Employees WHERE EmployeeID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

// First execution with Integer
pstmt.setInt(1, 100);
pstmt.executeQuery();

// Second execution with String - causes re-preparation
pstmt.setString(1, "100");
pstmt.executeQuery();

이 시나리오에서 setInt에서 setString로 전환하면 매개 변수 유형이 int에서 varchar로 변경되어 SQL Server가 문을 다시 준비하게 됩니다.

정밀도 및 스케일 변경

숫자 유형인 decimal 및 numeric과 같은 경우, 정밀도나 배율의 변경도 재준비를 트리거합니다.

String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

// First execution with specific precision
BigDecimal price1 = new BigDecimal("19.99"); // precision 4, scale 2
pstmt.setBigDecimal(1, price1);
pstmt.setInt(2, 1);
pstmt.executeUpdate();

// Second execution with different precision - causes re-preparation
BigDecimal price2 = new BigDecimal("1999.9999"); // precision 8, scale 4
pstmt.setBigDecimal(1, price2);
pstmt.setInt(2, 2);
pstmt.executeUpdate();

SQL Server는 정밀도 및 크기 조정이 데이터베이스 엔진이 쿼리를 처리하는 방식에 영향을 주므로 정밀도 및 크기 조정 조합에 대해 서로 다른 실행 계획을 만듭니다.

매개 변수 사용 모범 사례

준비된 구문의 성능을 최대화하려면 다음 모범 사례를 따르는 것이 좋습니다.

매개 변수 형식을 명시적으로 지정

가능하면 데이터베이스 열 형식과 일치하는 명시적 setter 메서드를 사용합니다.

// Good: Explicit type matching
pstmt.setInt(1, employeeId);
pstmt.setString(2, name);
pstmt.setBigDecimal(3, salary);

// Avoid: Using setObject() without explicit types
pstmt.setObject(1, employeeId); // Type inference might vary

일관된 매개 변수 메타데이터 사용

숫자 매개 변수에 대해 일관된 정밀도 및 배율을 유지합니다.

// Good: Consistent precision and scale
BigDecimal price1 = new BigDecimal("19.99").setScale(2);
BigDecimal price2 = new BigDecimal("29.99").setScale(2);

// Avoid: Varying precision and scale
BigDecimal price3 = new BigDecimal("19.9");    // scale 1
BigDecimal price4 = new BigDecimal("29.999");  // scale 3

숫자 형식을 사용하여 데이터 반올림 이해

숫자 매개 변수에 잘못된 정밀도 및 배율을 사용하면 의도하지 않은 데이터 반올림이 발생할 수 있습니다. 전체 자릿수와 소수 자릿수는 매개 변수 값과 SQL 문에서 사용되는 위치에 모두 적합해야 합니다.

// Example: Column defined as DECIMAL(10, 2)
// Good: Matching precision and scale
BigDecimal amount = new BigDecimal("12345.67").setScale(2, RoundingMode.HALF_UP);
pstmt.setBigDecimal(1, amount);

// Problem: Scale too high causes rounding
BigDecimal amount2 = new BigDecimal("12345.678"); // scale 3
pstmt.setBigDecimal(1, amount2); // Rounds to 12345.68

// Problem: Precision too high
BigDecimal amount3 = new BigDecimal("123456789.12"); // Exceeds precision
pstmt.setBigDecimal(1, amount3); // Might cause truncation or error

데이터에 적절한 정밀도 및 규모가 필요하지만, 준비된 문을 실행할 때마다 이러한 값을 변경하지 마십시오. 정밀도나 소수점 자리 수가 변경될 때마다 서버에서 문을 다시 준비해야 하므로 준비된 문의 성능상의 이점이 무효화됩니다.

// Good: Consistent precision and scale across executions
PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO Orders (OrderID, Amount) VALUES (?, ?)");

for (Order order : orders) {
    pstmt.setInt(1, order.getId());
    // Always use scale 2 for currency
    BigDecimal amount = order.getAmount().setScale(2, RoundingMode.HALF_UP);
    pstmt.setBigDecimal(2, amount);
    pstmt.executeUpdate();
}

// Avoid: Changing scale for each execution
for (Order order : orders) {
    pstmt.setInt(1, order.getId());
    // Different scale each time - causes re-preparation
    pstmt.setBigDecimal(2, order.getAmount()); // Variable scale
    pstmt.executeUpdate();
}

정확성과 성능의 균형을 맞추려면 다음을 수행합니다.

1. 비즈니스 요구 사항에 적합한 정밀도 및 규모를 결정합니다.
2. 일관된 정밀도 및 배율을 사용하도록 모든 매개 변수 값을 정규화합니다.
3. 명시적 반올림 모드를 사용하여 값 조정 방법을 제어합니다.
4. 정규화된 값이 대상 열 정의와 일치하는지 확인합니다.

매개 변수 설정 메서드 혼합 방지

실행에서 동일한 매개 변수 위치에 대해 서로 다른 setter 메서드 간에 전환하지 마세요.

// Avoid: Mixing setter methods
pstmt.setInt(1, 100);
pstmt.executeQuery();

pstmt.setString(1, "100"); // Different method - causes re-preparation
pstmt.executeQuery();

명시적 형식으로 setNull() 사용

null 값을 설정할 때 일관성을 유지하기 위해 SQL 형식을 지정합니다.

// Good: Explicit type for null
pstmt.setNull(1, java.sql.Types.INTEGER);

// Avoid: Generic null without type
pstmt.setObject(1, null); // Type might be inferred differently

매개 변수 관련 성능 모니터링

재설치 문제 감지

매개 변수 변경으로 인해 성능 문제가 발생하는지 여부를 식별하려면 다음을 수행합니다.

1. SQL Server Profiler 또는 확장 이벤트를 사용하여 SP:CacheMiss 및 SP:Recompile 이벤트를 모니터링합니다.
2. DMV를 검토하여 계획이 재사용되는지 확인하십시오.
3. 쿼리 성능 메트릭을 분석하여 자주 재구성된 문을 식별합니다.

계획 재사용을 확인하는 예제 쿼리:

SELECT 
    text,
    usecounts,
    size_in_bytes,
    cacheobjtype,
    objtype
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE text LIKE '%YourQueryText%'
ORDER BY usecounts DESC;

성능 계수기

다음 SQL Server 성능 카운터를 모니터링합니다.

• SQL 통계: SQL 다시 컴파일/초 - 문이 다시 컴파일되는 횟수를 보여 줍니다.
• SQL 통계: SQL 컴파일/초 - 새 계획이 생성되는 빈도를 보여 줍니다.
• 계획 캐시: 캐시 적중률 - 계획이 얼마나 효과적으로 재사용되고 있는지를 나타냅니다.

카운터 및 카운터를 해석하는 방법에 대한 자세한 내용은 SQL Server, Plan Cache 개체를 참조하세요.

고급 고려 사항

매개 변수가 있는 쿼리 및 계획 캐시 오염

10진수 또는 숫자 정밀도가 다르면 SQL Server가 동일한 쿼리에 대한 여러 실행 계획을 만들 때 계획 캐시 오염이 발생합니다. 이 문제는 메모리를 낭비하고 계획 재사용 효율성을 줄입니다.

// Avoid: Varying precision pollutes the plan cache
String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

for (int i = 0; i < 1000; i++) {
    // Each different precision/scale creates a separate cached plan
    BigDecimal price = new BigDecimal("19." + i); // Varying scale
    pstmt.setBigDecimal(1, price);
    pstmt.setInt(2, i);
    pstmt.executeUpdate();
}
pstmt.close();

계획 캐시 오염을 방지하려면 숫자 매개 변수에 대해 일관된 정밀도 및 배율을 유지합니다.

// Good: Consistent precision and scale enables plan reuse
String sql = "UPDATE Products SET Price = ? WHERE ProductID = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);

for (int i = 0; i < 1000; i++) {
    // Same precision and scale - reuses the same cached plan
    // Note: Round or increase to a consistent scale that aligns with your application data needs.
    BigDecimal price = new BigDecimal("19." + i).setScale(2, RoundingMode.HALF_UP);
    pstmt.setBigDecimal(1, price);
    pstmt.setInt(2, i);
    pstmt.executeUpdate();
}
pstmt.close();

문자열 길이 및 정수 값 변형은 계획 캐시 오염을 유발하지 않으며 숫자 형식의 정밀도 및 배율 변경만 이 문제를 만듭니다.

연결 문자열 속성

JDBC 드라이버는 준비된 문 동작 및 성능에 영향을 주는 연결 속성을 제공합니다.

• enablePrepareOnFirstPreparedStatementCall - (기본값: false) 드라이버가 첫 번째 또는 두 번째 실행을 호출 sp_prepexec 할지 여부를 제어합니다. 애플리케이션이 동일한 준비된 문을 여러 번 일관되게 실행하는 경우 첫 번째 실행을 준비하면 성능이 약간 향상됩니다. 두 번째 실행을 준비하면 준비된 문을 한 번 실행하는 애플리케이션의 성능이 향상됩니다. 이 전략은 준비된 문이 한 번만 실행될 경우 별도로 unprepare를 호출할 필요가 없습니다.
• prepareMethod - (기본값: prepexec) 준비(prepare 또는 prepexec)에 사용할 동작을 지정합니다. prepareMethod를 prepare로 설정하면 데이터베이스를 실행 계획에서 고려할 초기 값 없이 문을 준비하기 위한 별도의 초기 액세스를 데이터베이스에서 발생시킵니다. prepexec를 sp_prepexec 준비 메서드로 사용하도록 설정합니다. 이 메서드는 준비 작업을 첫 번째 실행과 결합하여 네트워크 왕복을 줄입니다. 또한 데이터베이스가 실행 계획에서 고려할 수 있는 초기 매개 변수 값을 데이터베이스에 제공합니다. 인덱스를 최적화하는 방법에 따라 한 설정이 다른 설정보다 더 효율적으로 수행될 수 있습니다.
• serverPreparedStatementDiscardThreshold - (기본값: 10) sp_unprepare 작업의 일괄 처리를 조정합니다. 이 옵션은 호출을 일괄 sp_unprepare 처리하여 성능을 향상시킬 수 있습니다. 값이 더 높을수록 준비된 문이 서버에 더 오래 남아 있게 됩니다.

자세한 내용은 연결 속성 설정을 참조하세요.

요약

매개 변수에 대해 준비된 문 성능을 최적화하려면 다음을 수행합니다.

1. 데이터베이스 열 형식과 일치하는 명시적 setter 메서드를 사용합니다.
2. 매개 변수 메타데이터(형식, 정밀도, 소수 자릿수, 길이)를 실행 간에 일관되게 유지합니다.
3. 동일한 매개 변수에 대해 서로 다른 setter 메서드 간에 전환하지 마세요.
4. SQL 유형을 명시적으로 지정할 때 setObject 또는 setNull을(를) 사용하십시오.
5. 새로운 준비된 문장을 만드는 대신 기존의 준비된 문장을 재사용합니다.
6. 계획 캐시 통계를 모니터링하여 배상 문제를 식별합니다.
7. 준비된 문 성능에 영향을 주는 연결 속성을 고려합니다.

이러한 관행을 따르면 서버 쪽 재준비를 최소화하고 준비된 문장에서 최대의 성능 이점을 얻을 수 있습니다.

참고하십시오

JDBC 드라이버에 대한 Prepared Statement 메타데이터 캐싱
JDBC 드라이버로 성능 및 안정성 개선
연결 속성 설정