이 문서에서는 준비된 문 매개 변수가 Microsoft JDBC Driver for SQL Server의 서버 쪽 성능에 미치는 영향을 설명하고 매개 변수 사용 최적화에 대한 지침을 제공합니다.
준비된 문 매개 변수 이해
준비된 문은 SQL Server가 쿼리를 한 번 구문 분석, 컴파일 및 최적화한 다음 실행 계획을 여러 번 다시 사용할 수 있도록 하여 상당한 성능 이점을 제공합니다. 그러나 매개 변수를 지정하는 방식은 이 성능 혜택에 큰 영향을 줄 수 있습니다.
준비된 문을 만들 때 SQL Server는 다음을 비롯한 매개 변수 메타데이터를 기반으로 실행 계획을 생성합니다.
- 데이터 형식
- 정밀도(숫자 형식의 경우)
- 소수 자릿수(소수점 형식의 경우)
- 최대 길이(문자열 및 이진 형식의 경우)
SQL Server는 이 메타데이터를 사용하여 쿼리 실행 계획을 최적화하기 때문에 이 메타데이터가 중요합니다. 이러한 매개 변수 특성을 변경하면 SQL Server에서 기존 계획을 취소하고 새 계획을 만들면 성능이 저하됩니다.
매개 변수 변경이 성능에 미치는 영향
매개 변수 형식 변경
준비된 문의 매개 변수 형식이 실행 간에 변경되면 SQL Server는 문을 다시 준비해야 합니다. 이 배상에는 다음이 포함됩니다.
- SQL 문을 다시 구문 분석합니다.
- 새 실행 계획 컴파일
- 새 계획 캐싱(캐싱을 사용하는 경우).
다음 예시를 참조하세요.
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();
}
정확성과 성능의 균형을 맞추려면 다음을 수행합니다.
- 비즈니스 요구 사항에 적합한 정밀도 및 규모를 결정합니다.
- 일관된 정밀도 및 배율을 사용하도록 모든 매개 변수 값을 정규화합니다.
- 명시적 반올림 모드를 사용하여 값 조정 방법을 제어합니다.
- 정규화된 값이 대상 열 정의와 일치하는지 확인합니다.
비고
연결 옵션을 사용하여 calcBigDecimalPrecision 매개 변수 정밀도를 자동으로 최적화할 수 있습니다. 사용하도록 설정하면 드라이버는 각 BigDecimal 값에 필요한 최소 정밀도를 계산하므로 불필요한 반올림을 방지할 수 있습니다. 그러나 이 접근 방식은 정밀도 값의 변화로 인해 데이터를 변경할 때 재준비가 필요하므로 더 많은 문 준비가 발생할 수 있습니다. 데이터 정확도와 일관된 문 재사용을 모두 제공하므로 가능한 경우 애플리케이션 코드에서 최적의 정밀도 및 배율을 수동으로 정의하는 것이 가장 좋습니다.
매개 변수 설정 메서드 혼합 방지
실행에서 동일한 매개 변수 위치에 대해 서로 다른 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
매개 변수 관련 성능 모니터링
재설치 문제 감지
매개 변수 변경으로 인해 성능 문제가 발생하는지 여부를 식별하려면 다음을 수행합니다.
- SQL Server Profiler 또는 확장 이벤트를 사용하여
SP:CacheMiss및SP:Recompile이벤트를 모니터링합니다. - DMV를 검토하여 계획이 재사용되는지 확인하십시오.
- 쿼리 성능 메트릭을 분석하여 자주 재구성된 문을 식별합니다.
계획 재사용을 확인하는 예제 쿼리:
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처리하여 성능을 향상시킬 수 있습니다. 값이 더 높을수록 준비된 문이 서버에 더 오래 남아 있게 됩니다.
자세한 내용은 연결 속성 설정을 참조하세요.
요약
매개 변수에 대해 준비된 문 성능을 최적화하려면 다음을 수행합니다.
- 데이터베이스 열 형식과 일치하는 명시적 setter 메서드를 사용합니다.
- 매개 변수 메타데이터(형식, 정밀도, 소수 자릿수, 길이)를 실행 간에 일관되게 유지합니다.
- 동일한 매개 변수에 대해 서로 다른 setter 메서드 간에 전환하지 마세요.
- SQL 유형을 명시적으로 지정할 때
setObject또는setNull을(를) 사용하십시오. - 새로운 준비된 문장을 만드는 대신 기존의 준비된 문장을 재사용합니다.
- 계획 캐시 통계를 모니터링하여 배상 문제를 식별합니다.
- 준비된 문 성능에 영향을 주는 연결 속성을 고려합니다.
이러한 관행을 따르면 서버 쪽 재준비를 최소화하고 준비된 문장에서 최대의 성능 이점을 얻을 수 있습니다.
참고하십시오
JDBC 드라이버에 대한 Prepared Statement 메타데이터 캐싱
JDBC 드라이버로 성능 및 안정성 개선
연결 속성 설정