PreparedStatements 및 성능

PreparedStatements 및 성능

---

그래서 PreparedStatements가 성능에 좋다고 계속 들었습니다.

우리는 'PreparedStatement'를 사용하는 것보다 일반 'Statement'를 더 많이 사용하는 Java 애플리케이션이 있습니다. 더 많은 PreparedStatements를 사용하기 위해 노력하는 동안 나는 PreparedStatements가 클라이언트 측과 서버 측에서 어떻게 작동하는지 더 철저히 이해하려고 노력하고 있습니다.

그렇다면 일반적인 CRUD 작업이 있고 애플리케이션에서 객체를 반복적으로 업데이트하는 경우 PS를 사용하는 것이 도움이됩니까? 나는 우리가 매번 PS를 닫아야한다는 것을 알고있다. 그렇지 않으면 커서 누수가 발생할 것이다.

그렇다면 성능에 어떤 도움이 될까요? 드라이버가 미리 컴파일 된 명령문을 캐시하고 다음에 connection.prepareStatement를 수행 할 때 사본을 제공합니까? 아니면 DB 서버가 도움이됩니까?

나는 PreparedStatements의 보안 이점에 대한 주장을 이해하고 그것을 강조하는 아래 답변에 감사드립니다. 그러나 저는이 토론을 PreparedStatements의 성능 이점에 초점을 맞추고 싶습니다.

업데이트 : 데이터 업데이트라고하면 그 메서드가 무작위로 여러 번 호출된다는 점에서 더 많은 것을 의미합니다. 나는 루프 내에서 진술을 재사용하도록 요구하는 아래 제공된 답변의 이점을 이해합니다.

    // some code blah blah
    update();

    // some more code blah blah 
    update();

.... 

public void update () throws SQLException{
 try{
      PreparedStatement ps = connection.prepareStatement("some sql");
      ps.setString(1, "foobar1");
      ps.setString(2, "foobar2");
      ps.execute();
 }finally {
     ps.close();

 }

}

실제로 'ps'자바 객체를 재사용 할 수있는 방법은 없으며 실제 connection.prepareStatement 호출이 상당히 비싸다는 것을 이해합니다.

이것이 원래 질문으로 돌아 가게하는 것입니다. 이 "일부 SQL"PreparedStatement가 여전히 캐시되고 내가 알지 못하는 커버 아래에서 재사용되고 있습니까?

또한 여러 데이터베이스를 지원한다는 점도 언급해야합니다.

미리 감사드립니다.

---

준비된 진술이 주로 성능에 관한 것이라는 개념은 매우 일반적인 것이지만 오해의 일부입니다.

또 다른 포스터는 Oracle과 SQL Server의 속도가 약 20 % 향상되었다고 언급했습니다. MySQL에서도 비슷한 수치를 확인했습니다. 쿼리 구문 분석은 관련된 작업에서 그렇게 중요한 부분이 아니라는 것이 밝혀졌습니다. 매우 바쁜 데이터베이스 시스템에서 쿼리 구문 분석이 전체 처리량에 영향을 미치는지 확실하지 않습니다. 전체적으로 데이터가 디스크에서 돌아 오는 동안 유휴 상태 인 CPU 시간을 사용하게 될 것입니다.

따라서 준비된 명령문을 사용하는 이유로 SQL 주입 공격에 대한 보호 가 성능 향상 보다 훨씬 큽니다 . 그리고 SQL 인젝션 공격에 대해 걱정하지 않는다면 아마도 ...

---

준비된 명령문은 준비한 동일한 명령문을 재사용 할 때 성능을 향상시킬 수 있습니다.

PreparedStatement ps = connection.prepare("SOME SQL");

for (Data data : dataList) {
  ps.setInt(1, data.getId());
  ps.setString(2, data.getValue();
  ps.executeUpdate();
}

ps.close();

이것은 루프에서 문을 만드는 것보다 훨씬 빠릅니다.

일부 플랫폼은 준비된 명령문을 캐시하여 닫더라도 더 빠르게 재구성 할 수 있습니다.

그러나 성능이 동일하더라도 SQL 주입을 방지하기 위해 준비된 명령문을 사용해야합니다. 우리 회사에서 이것은 인터뷰 질문입니다. 잘못하면 우리는 당신을 고용하지 않을 수도 있습니다.

---

준비된 명령문은 실제로 처음 사용 후 캐시되며 표준 명령문보다 성능면에서 제공됩니다. 진술이 변경되지 않으면이 방법을 사용하는 것이 좋습니다. 일반적으로 변경 사용을 위해 명령문 캐시에 저장됩니다.

자세한 정보는 여기에서 찾을 수 있습니다.

http://www.theserverside.com/tt/articles/article.tss?l=Prepared-Statments

JDBC를 직접 사용하는 대신 Spring JDBCTemplate을보고 싶을 수도 있습니다.

http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html

---

SQL 구문 분석 만 진행되는 것은 아닙니다. 테이블과 열이 실제로 존재하는지 확인하고 쿼리 계획을 만드는 등의 작업이 있습니다. PreparedStatement로 한 번 지불하면됩니다.

SQL 인젝션을 방지하기위한 바인딩은 실제로 매우 좋은 일입니다. 충분하지 않습니다, IMO. 지속성 계층에 도달하기 전에 입력의 유효성을 검사해야합니다.

---

그렇다면 성능에 어떤 도움이 될까요? 드라이버가 미리 컴파일 된 명령문을 캐시하고 다음에 connection.prepareStatement를 수행 할 때 사본을 제공합니까? 아니면 DB 서버가 도움이됩니까?

성능 측면에서 대답하겠습니다. 여기에있는 다른 사람들은 이미 PreparedStatementSQL 주입에 탄력적 이라고 규정했습니다 (축복 된 이점).

응용 프로그램 (JDBC 드라이버)은를 만들고 PreparedStatement자리 표시 자 ( ?) 를 사용하여 RDBMS에 전달합니다 . RDBMS는 미리 컴파일하여 수신 된 쿼리 최적화 (필요한 경우)를 적용 PreparedStatement하고 (일부에서는) 일반적으로 캐시합니다. 를 실행하는 동안 PreparedStatement미리 컴파일 된 PreparedStatement이 사용되어 각 자리 표시자를 관련 값으로 바꾸고 계산합니다. 이것은 Statement그것을 PreparedStatement컴파일하고 직접 실행하는 것과 대조적으로 쿼리를 한 번만 컴파일하고 최적화합니다 . 이제 위에서 설명한이 시나리오는 모든 JDBC 벤더의 절대적인 경우는 아니지만 본질적 PreparedStatement으로 사용 및 운영되는 방법 입니다.

---

일화 : 몇 년 전에 Oracle과 SQL Server 백엔드 모두에서 Java 1.4에서 ODBC를 사용하여 준비된 명령문과 동적 명령문을 몇 가지 실험했습니다. 준비된 명령문이 특정 쿼리에 대해 최대 20 % 더 빠를 수 있음을 발견했지만 어느 쿼리가 어느 정도 개선되었는지에 대한 공급 업체별 차이가있었습니다. (이것은 놀라운 일이 아닙니다.)

결론은 동일한 쿼리를 반복적으로 재사용 할 경우 준비된 문이 성능 향상에 도움이 될 수 있다는 것입니다. 그러나 당신의 성과가 너무 나빠서 즉시 조치를 취해야한다면, 급진적 인 향상을 위해 준비된 진술을 사용하지 마십시오. (일반적으로 20 %는 집에 쓸 내용이 없습니다.)

물론 귀하의 마일리지는 다를 수 있습니다.

---

이것이 원래 질문으로 돌아 가게하는 것입니다. 이 "일부 SQL"PreparedStatement가 여전히 캐시되고 내가 알지 못하는 커버 아래에서 재사용되고 있습니까?

예, 적어도 Oracle에서는 그렇습니다. Oracle® Database JDBC Developer 's Guide Implicit Statement Caching (강조 추가됨)에 따라,

암시 적 명령문 캐싱을 활성화하면 JDBC close는이 명령문 객체 의 메서드 를 호출 할 때 준비된 명령문이나 호출 가능한 명령문을 자동으로 캐시 합니다. 준비되고 호출 가능한 문은 표준 연결 개체 및 문 개체 메서드를 사용하여 캐시되고 검색됩니다.

암시 적 문 캐싱은 SQL 문자열을 키로 사용하고 일반 문은 SQL 문자열없이 생성되기 때문에 일반 문은 내재적으로 캐시되지 않습니다. 따라서 암시 적 문 캐싱 은 SQL 문자열로 생성 된 OraclePreparedStatement및 OracleCallableStatement개체 에만 적용됩니다 . OracleStatement에서는 암시 적 명령문 캐싱을 사용할 수 없습니다. 당신이를 만들 때 OraclePreparedStatement나 OracleCallableStatement, JDBC 드라이버는 자동으로 일치하는 문에 대한 캐시를 검색합니다 .

---

1. PreparedStatement를 사용하면 동적 및 매개 변수 쿼리를 작성할 수 있습니다.

Java에서 PreparedStatement를 사용하면 매개 변수화 된 SQL 쿼리를 작성하고 다른 쿼리를 만드는 것보다 훨씬 더 나은 동일한 SQL 쿼리를 사용하여 다른 매개 변수를 보낼 수 있습니다.

2. PreparedStatement는 Java의 Statement보다 빠릅니다.

PreparedStatement 사용의 주요 이점 중 하나는 성능 향상입니다. PreparedStatement는 데이터베이스에서 사전 컴파일되며 액세스 계획도 데이터베이스에 캐시되므로 데이터베이스는 할 일이 적기 때문에 준비된 명령문을 사용하여 작성된 매개 변수 쿼리를 훨씬 빠르게 실행할 수 있습니다. 데이터베이스에 대한로드를 줄이기 위해 프로덕션 JDBC 코드에서 항상 PreparedStatement를 사용해야합니다. 성능 이점을 얻으려면 문자열 연결이 아닌 매개 변수화 된 SQL 쿼리 버전 만 사용하는 것이 좋습니다.

3. PreparedStatement는 Java에서 SQL 주입 공격을 방지합니다.

자세히보기 : http://javarevisited.blogspot.com/2012/03/why-use-preparedstatement-in-java-jdbc.html#ixzz3LejuMnVL

---

준비된 명령문은 사용 방법에 따라 일반 명령문과 비교하여 성능 측면에서 몇 가지 장점이 있습니다. 앞서 언급 한 것처럼 다른 매개 변수로 동일한 쿼리를 여러 번 실행해야하는 경우 준비된 문을 재사용하고 새 매개 변수 집합 만 전달할 수 있습니다. 성능 향상은 사용중인 특정 드라이버 및 데이터베이스에 따라 다릅니다.

예를 들어, 데이터베이스 성능 측면에서 Oracle 데이터베이스는 각 계산 후 일부 쿼리의 실행 계획을 캐시합니다 (Oracle의 모든 버전 및 모든 구성에 해당되지 않음). RDBMS 수준에서 수행되기 때문에 문을 닫고 새 문을 열어도 개선 사항을 찾을 수 있습니다. 이러한 종류의 캐싱은 두 개의 후속 쿼리가 (문자별로) 동일한 경우에만 활성화됩니다. 매개 변수가 쿼리의 일부이고 다른 SQL 문자열을 생성하기 때문에 일반 명령문에는 적용되지 않습니다.

일부 다른 RDBMS는 더 "지능적"일 수 있지만 실행 계획을 캐싱하는 데 복잡한 패턴 일치 알고리즘을 사용하면 성능이 저하 될 것으로 예상하지 않습니다. 실행 계획의 계산이 쿼리 실행의 일부일 뿐이라고 주장 할 수 있습니다. 일반적인 경우는 동의하지만 .. 상황에 따라 다릅니다. rdbms는 통계와 같은 오프 메모리 데이터 (Oracle뿐만 아니라)를 참조해야하므로 일반적으로 실행 계획을 계산하는 것은 비용이 많이 드는 작업 일 수 있습니다.

그러나 캐싱에 대한 주장은 실행 계획에서 추출 프로세스의 다른 부분에 이르기까지 다양합니다. RDBMS에 동일한 쿼리를 여러 번 제공하면 (특정 구현에 대해 자세히 설명하지 않음) JDBC (드라이버) 또는 RDBMS 수준에서 이미 계산 된 구조를 식별하는 데 도움이됩니다. 지금 성능에서 특별한 이점을 찾지 못했다면 성능 향상이 드라이버 / rdbms의 향후 / 대체 버전에서 구현 될 것이라는 점을 배제 할 수 없습니다.

업데이트에 대한 성능 향상은 배치 모드에서 준비된 문을 사용하여 얻을 수 있지만 이것은 또 다른 이야기입니다.

---

짧은 답변:

PreparedStatement는 일반적으로 DB 클라이언트가 동일한 쿼리를 반복적으로 수행하기 때문에 성능에 도움이되며,이를 통해 초기 쿼리 에 대해 일부 전처리 를 수행 하여 다음 반복 쿼리의 속도를 높일 수 있습니다.

긴 대답 :

Wikipedia 에 따르면 준비된 문을 사용하는 일반적인 워크 플로는 다음과 같습니다.

준비 : 문 템플릿은 응용 프로그램에서 생성되어 데이터베이스 관리 시스템 (DBMS)으로 전송됩니다. 매개 변수, 플레이스 홀더 또는 바인드 변수라고하는 특정 값은 지정되지 않은 채로 남습니다 (아래에 "?"로 표시됨). INSERT INTO PRODUCT (이름, 가격) VALUES (?,?)

(Pre-compilation) : DBMS는 구문 템플릿에 대한 쿼리 최적화를 구문 분석, 컴파일, 수행하고 실행하지 않고 결과를 저장합니다.

Execute : 나중에 응용 프로그램이 매개 변수에 대한 값을 제공 (또는 바인딩)하고 DBMS가 명령문을 실행합니다 (결과를 반환 할 수 있음). 응용 프로그램은 다른 값으로 원하는만큼 문을 실행할 수 있습니다. 이 예에서는 첫 번째 매개 변수에 'Bread'를, 두 번째 매개 변수에 '1.00'을 제공 할 수 있습니다.

준비 :

JDBC에서 "준비"단계는 java.sql.Connection을 호출하여 수행됩니다. prepareStatement (문자열 SQL) API. Javadoc에 따르면 :

이 방법은 사전 컴파일의 이점을 얻는 매개 변수 SQL 문을 처리하는 데 최적화되어 있습니다. 드라이버가 사전 컴파일을 지원하는 경우 prepareStatement 메소드는 사전 컴파일을 위해 명령문을 데이터베이스에 보냅니다. 일부 드라이버는 사전 컴파일을 지원하지 않을 수 있습니다. 이 경우 PreparedStatement 개체가 실행될 때까지 문이 데이터베이스로 전송되지 않을 수 있습니다. 이것은 사용자에게 직접적인 영향을 미치지 않습니다. 그러나 특정 SQLException 개체를 throw하는 메서드에 영향을줍니다.

이 API를 호출하면 SQL 문이 데이터베이스로 전송 될 수 있으므로 일반적으로 비용이 많이 듭니다. JDBC 드라이버의 구현에 따라 동일한 SQL 문 템플릿이있는 경우 성능 향상을 위해 동일한 SQL 문 템플릿에 대해 클라이언트 측에서이 API를 여러 번 호출하지 않도록해야 할 수 있습니다.

사전 컴파일 :

전송 된 명령문 템플릿은 데이터베이스에서 사전 컴파일 되고 db 서버에 캐시됩니다. 데이터베이스는 연결 및 SQL 문 템플릿을 키로 사용하고 미리 컴파일 된 쿼리와 계산 된 쿼리 계획을 캐시의 값으로 사용합니다. 쿼리 구문 분석은 테이블, 쿼리 할 열의 유효성을 검사해야 할 수 있으므로 비용이 많이 드는 작업이 될 수 있으며 쿼리 계획 계산 도 비용이 많이 드는 작업입니다.

실행 :

동일한 연결 및 SQL 문 템플릿의 다음 쿼리의 경우 다시 계산하지 않고 데이터베이스 서버에 의해 미리 컴파일 된 쿼리 및 쿼리 계획이 캐시에서 직접 조회됩니다.

결론:

성능 관점에서 prepare 문을 사용하는 것은 2 단계 프로세스입니다.

1. 1 단계, 준비 및 사전 컴파일,이 단계는 한 번 수행되고 성능에 약간의 오버 헤드가 추가 될 것으로 예상됩니다.
2. 2 단계, 동일한 쿼리의 반복 실행 1 단계에는 쿼리에 대한 일부 사전 처리가 있으므로 반복되는 쿼리 수가 충분히 크면 동일한 쿼리에 대한 많은 사전 처리 작업을 절약 할 수 있습니다.

자세한 내용을 알고 싶다면 PrepareStatement의 이점을 설명하는 몇 가지 문서가 있습니다.

1. http://javarevisited.blogspot.com/2012/03/why-use-preparedstatement-in-java-jdbc.html
2. http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

참조 URL : https://stackoverflow.com/questions/687550/preparedstatements-and-performance