PreparedStatements y rendimiento

Así que sigo escuchando que los estados preparados son buenos para el rendimiento.

Tenemos una aplicación Java en la que usamos la ‘Declaración’ regular más de lo que usamos ‘PreparedStatement’. Al intentar avanzar hacia el uso de más declaraciones preparadas, estoy tratando de obtener una comprensión más profunda de cómo funcionan las declaraciones preparadas, en el lado del cliente y del servidor.

Entonces, si tenemos algunas operaciones CRUD típicas y actualizamos un objeto repetidamente en la aplicación, ¿ayuda usar una PS? Entiendo que tendremos que cerrar el PS cada vez; de lo contrario, se producirá una fuga del cursor.

Entonces, ¿cómo ayuda con el rendimiento? ¿El controlador almacena en caché la statement precomstackda y me da una copia la próxima vez que hago connection.prepareStatement? ¿O el servidor DB ayuda?

Entiendo la discusión sobre los beneficios de seguridad de PreparedStatements y aprecio las respuestas a continuación que lo enfatizan. Sin embargo, realmente quiero mantener esta discusión centrada en los beneficios de rendimiento de PreparedStatements.

Actualización: cuando digo datos de actualización, realmente quiero decir más en términos de que ese método sea llamado aleatoriamente varias veces. Entiendo la ventaja en la respuesta ofrecida a continuación, que solicita volver a utilizar la statement dentro de un ciclo.

// 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(); } } 

No hay forma de reutilizar realmente el objeto ‘ps’ java y entiendo que la llamada real connection.prepareStatement es bastante costosa.

Que es lo que me lleva de vuelta a la pregunta original. ¿Este “preparado sql” PreparedStatement aún se está almacenando en caché y reutilizado bajo las coberturas que no conozco?

También debo mencionar que admitimos varias bases de datos.

Gracias por adelantado.

Las declaraciones preparadas pueden mejorar el rendimiento al volver a utilizar la misma statement que preparó:

 PreparedStatement ps = connection.prepare("SOME SQL"); for (Data data : dataList) { ps.setInt(1, data.getId()); ps.setString(2, data.getValue(); ps.executeUpdate(); } ps.close(); 

Esto es mucho más rápido que crear la statement en el ciclo.

Algunas plataformas también almacenan en caché las declaraciones preparadas para que, incluso si las cierra, puedan reconstruirse más rápidamente.

Sin embargo, incluso si el rendimiento fuera idéntico, debería seguir utilizando sentencias preparadas para evitar la inyección de SQL. En mi compañía esta es una pregunta de entrevista; hazlo mal y es posible que no te contratemos.

La noción de que las declaraciones preparadas son principalmente sobre el rendimiento es algo así como un concepto erróneo, aunque es bastante común.

Otro cartel mencionó que notó una mejora de velocidad de aproximadamente 20% en Oracle y SQL Server. He notado una figura similar con MySQL. Resulta que analizar la consulta simplemente no es una parte tan significativa del trabajo involucrado. En un sistema de base de datos muy ocupado, tampoco está claro si el análisis de consultas afectará el rendimiento general: en general, probablemente solo utilizará el tiempo de CPU que, de lo contrario, estaría inactivo mientras los datos volvían del disco.

Por lo tanto, como una razón para usar declaraciones preparadas, la protección contra los ataques de inyección SQL supera con creces la mejora en el rendimiento. Y si no te preocupan los ataques de inyección SQL, probablemente deberías …

Las declaraciones preparadas se almacenan en la memoria caché después de su primer uso, que es lo que proporcionan en el rendimiento sobre las declaraciones estándar. Si su estado de cuenta no cambia, se aconseja utilizar este método. Por lo general, se almacenan dentro de un caché de declaraciones para su posterior uso.

Aquí se puede encontrar más información:

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

y es posible que desee ver Spring JDBCTemplate como una alternativa al uso de JDBC directamente.

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

Analizar el SQL no es lo único que está pasando. Está validando que las tablas y columnas sí existen, creando un plan de consulta, etc. Usted paga eso una vez con un Estado Preparado.

La vinculación para proteger contra la inyección de SQL es algo muy bueno, de hecho. No es suficiente, IMO. Aún debe validar la entrada antes de llegar a la capa de persistencia.

Entonces, ¿cómo ayuda con el rendimiento? ¿El controlador almacena en caché la statement precomstackda y me da una copia la próxima vez que hago connection.prepareStatement? ¿O el servidor DB ayuda?

Voy a responder en términos de rendimiento. Otros aquí ya han estipulado que PreparedStatement s son resistentes a la inyección de SQL (ventaja bendita).

La aplicación (JDBC Driver) crea el PreparedStatement y lo pasa al RDBMS con marcadores de posición ( ? ). El RDBMS precomstack, aplicando la optimización de consulta (si es necesario) del PreparedStatement recibido y (en algunos) generalmente los almacena en caché. Durante la ejecución de PreparedStatement , se utiliza el precomstackdo PreparedStatement , que reemplaza cada marcador de posición con sus valores relevantes y se calcula. Esto está en contraste con el Statement que lo comstack y lo ejecuta directamente, el PreparedStatement comstack y optimiza la consulta solo una vez . Ahora bien, este escenario explicado anteriormente no es un caso absoluto de TODOS los proveedores de JDBC, sino que, en esencia, así es como se usa y se utiliza PreparedStatement .

Anecdóticamente: hice algunos experimentos con declaraciones preparadas frente a dinámicas utilizando ODBC en Java 1.4 hace algunos años, con los servidores de Oracle y SQL Server. Descubrí que las declaraciones preparadas pueden ser hasta un 20% más rápidas para ciertas consultas, pero hubo diferencias específicas del proveedor con respecto a qué consultas se mejoraron en qué medida. (Esto no debería sorprender, de verdad.)

La conclusión es que si volverá a utilizar la misma consulta repetidamente, las declaraciones preparadas pueden ayudar a mejorar el rendimiento; pero si su desempeño es tan malo que necesita hacer algo al respecto inmediatamente, no cuente con el uso de declaraciones preparadas para darle un impulso radical. (20% generalmente no es nada del otro mundo)

Su kilometraje puede variar, por supuesto.

Que es lo que me lleva de vuelta a la pregunta original. ¿Este “preparado sql” PreparedStatement aún se está almacenando en caché y reutilizado bajo las coberturas que no conozco?

Sí, al menos con Oracle. Por Oracle® Database JDBC Guía del desarrollador Almacenamiento en caché de sentencias implícitas (énfasis agregado),

Cuando habilita el almacenamiento en caché de sentencias implícitas, JDBC almacena en caché automáticamente la instrucción preparada o invocable cuando llama al método de close de este objeto de statement. Las declaraciones preparadas y desechables se almacenan en caché y se recuperan utilizando el objeto de conexión estándar y los métodos de objeto de statement.

Las sentencias planas no se almacenan en caché implícitamente, porque el almacenamiento en caché de sentencias implícitas utiliza una cadena SQL como clave y las sentencias planas se crean sin una cadena SQL. Por lo tanto, el almacenamiento en caché de Sentencias implícitas solo se aplica a los objetos OraclePreparedStatement y OracleCallableStatement , que se crean con una cadena SQL. No puede usar el caché de Sentencias implícitas con OracleStatement. Cuando crea un OraclePreparedStatement u OracleCallableStatement , el controlador JDBC busca automáticamente en el caché una statement correspondiente .

Las declaraciones preparadas tienen algunas ventajas en términos de rendimiento con respecto a las declaraciones normales, dependiendo de cómo las use. Como alguien dijo antes, si necesita ejecutar la misma consulta varias veces con diferentes parámetros, puede reutilizar la statement preparada y pasar solo el nuevo conjunto de parámetros. La mejora del rendimiento depende del controlador específico y la base de datos que está utilizando.

Como instancia, en términos de rendimiento de la base de datos, la base de datos Oracle almacena en caché el plan de ejecución de algunas consultas después de cada cálculo (esto no es cierto para todas las versiones y todas las configuraciones de Oracle). Puede encontrar mejoras incluso si cierra una statement y abre una nueva, porque esto se hace a nivel RDBMS. Este tipo de almacenamiento en caché solo se activa si las dos consultas siguientes son (char-by-char) iguales. Esto no se aplica a las declaraciones normales porque los parámetros son parte de la consulta y producen diferentes cadenas de SQL.

Algunos otros RDBMS pueden ser más “inteligentes”, pero no espero que utilicen algoritmos complejos de coincidencia de patrones para almacenar en caché los planes de ejecución porque reducirían el rendimiento. Puede argumentar que el cálculo del plan de ejecución es solo una pequeña parte de la ejecución de la consulta. Para el caso general, estoy de acuerdo, pero … depende. Tenga en cuenta que, por lo general, calcular un plan de ejecución puede ser una tarea costosa, porque el rdbms necesita consultar datos que no están en la memoria, como las estadísticas (no solo Oracle).

Sin embargo, el argumento sobre el almacenamiento en caché abarca desde planes de ejecución hasta otras partes del proceso de extracción. Dar al RDBMS varias veces la misma consulta (sin profundizar en una implementación en particular) ayuda a identificar estructuras ya computadas en JDBC (controlador) o en el nivel de RDBMS. Si no encuentra ninguna ventaja particular en el rendimiento ahora, no puede excluir que la mejora del rendimiento se implementará en versiones futuras / alternativas del controlador / rdbms.

Las mejoras de rendimiento para las actualizaciones se pueden obtener mediante el uso de declaraciones preparadas en modo por lotes, pero esta es otra historia.

1. PreparedStatement le permite escribir consultas dinámicas y paramétricas

Al usar PreparedStatement en Java, puede escribir consultas SQL parametrizadas y enviar diferentes parámetros utilizando las mismas consultas sql, que es mucho mejor que crear consultas diferentes.

2. PreparedStatement es más rápido que Statement en Java

Uno de los principales beneficios de usar PreparedStatement es un mejor rendimiento. PreparedStatement se comstack previamente en la base de datos y el plan de acceso también se almacena en caché en la base de datos, lo que permite que la base de datos ejecute la consulta paramétrica utilizando una statement preparada mucho más rápido que la consulta normal porque tiene menos trabajo por hacer. Siempre debe intentar usar PreparedStatement en el código JDBC de producción para reducir la carga en la base de datos. Para obtener un beneficio en el rendimiento vale la pena señalar que solo se usa la versión parametrizada de la consulta sql y no con la concatenación de cadenas

3. PreparedStatement previene ataques de inyección SQL en Java

Leer más: http://javarevisited.blogspot.com/2012/03/why-use-preparedstatement-in-java-jdbc.html#ixzz3LejuMnVL

Respuesta corta:

PreparedStatement ayuda al rendimiento porque, por lo general, los clientes de DB realizan la misma consulta de forma repetitiva, y esto permite realizar un preprocesamiento de la consulta inicial para acelerar las siguientes consultas repetitivas .

Respuesta larga:

Según Wikipedia , el flujo de trabajo típico de usar una statement preparada es el siguiente:

Preparar : la aplicación crea la plantilla de statement y la envía al sistema de gestión de base de datos (DBMS). Ciertos valores quedan sin especificar, llamados parámetros, marcadores de posición o variables de vinculación (etiquetados como “?” A continuación): INSERTAR EN PRODUCTOS (nombre, precio) VALORES (?,?)

(Precomstackción) : el DBMS analiza, comstack y realiza la optimización de consultas en la plantilla de instrucciones, y almacena el resultado sin ejecutarlo.

Ejecutar : en un momento posterior, la aplicación proporciona (o vincula) valores para los parámetros, y el DBMS ejecuta la statement (posiblemente devolviendo un resultado). La aplicación puede ejecutar la statement tantas veces como quiera con diferentes valores. En este ejemplo, podría suministrar ‘Bread’ para el primer parámetro y ‘1.00’ para el segundo parámetro.

Preparar:

En JDBC, el paso “Preparar” se realiza llamando a java.sql.Connection. PrepararStatement (String sql) API. De acuerdo con su Javadoc:

Este método está optimizado para manejar declaraciones SQL paramétricas que se benefician de la precomstackción. Si el controlador admite la precomstackción, el método prepareStatement enviará la statement a la base de datos para su precomstackción. Algunos controladores pueden no ser compatibles con la precomstackción. En este caso, la statement no se puede enviar a la base de datos hasta que se ejecute el objeto PreparedStatement. Esto no tiene un efecto directo en los usuarios; sin embargo, sí afecta los métodos que lanzan ciertos objetos SQLException.

Dado que llamar a esta API puede enviar la statement SQL a la base de datos, normalmente es una llamada costosa. Dependiendo de la implementación del controlador JDBC, si tiene la misma plantilla de statement SQL, para un mejor rendimiento, puede que tenga que evitar llamar a esta API varias veces en el lado del cliente para la misma plantilla de statement SQL.

Precomstackción:

La plantilla de statement enviada se precomstackrá en la base de datos y se almacenará en caché en el servidor de db. La base de datos probablemente usará la plantilla de statement de conexión y SQL como la clave, y la consulta precomstackda y el plan de consulta calculado como valor en la memoria caché. La consulta de análisis puede necesitar validar tablas y columnas para ser consultadas, por lo que podría ser una operación costosa, y el cálculo del plan de consultas también es una operación costosa.

Ejecutar:

Para las siguientes consultas de la misma conexión y la misma plantilla de statement sql, el servidor de la base de datos buscará la consulta precomstackda y el plan de consulta directamente desde la memoria caché sin volver a calcularlo.

Conclusión:

Desde la perspectiva del rendimiento, el uso de la statement de preparación es un proceso de dos fases:

  1. Fase 1, preparación y precomstackción, se espera que esta fase se realice una vez y agregue un poco de sobrecarga para el rendimiento.
  2. Fase 2, ejecuciones repetidas de la misma consulta, ya que la fase 1 tiene algún procesamiento previo para la consulta, si el número de consultas repetitivas es lo suficientemente grande, esto puede ahorrar mucho esfuerzo de preprocesamiento para la misma consulta.

Y si desea conocer más detalles, hay algunos artículos que explican los beneficios de 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