sistema obtener memoria informacion con java sql-server performance hibernate ejb

memoria - obtener informacion del sistema con java



Hibernar SQL en cláusula haciendo uso de CPU al 100% (8)

En mi aplicación java estoy usando SQL server and Hibernate3 with EJB . Cuando intenté ejecutar una consulta de selección with In clause , el uso de la CPU del servidor DB alcanza el 100%. Pero cuando intenté ejecutar la misma consulta en el SQL management studio , la consulta se está ejecutando sin picos de CPU. El servidor de aplicaciones y el servidor DB son dos máquinas diferentes. Mi tabla tiene el siguiente esquema,

CREATE TABLE student_table ( Student_Id BIGINT NOT NULL IDENTITY , Class_Id BIGINT NOT NULL , Student_First_Name VARCHAR(100) NOT NULL , Student_Last_Name VARCHAR(100) , Roll_No VARCHAR(100) NOT NULL , PRIMARY KEY (Student_Id) , CONSTRAINT UK_StudentUnique_1 UNIQUE (Class_Id, Roll_No) );

La tabla contiene alrededor de 1000k registros. Mi consulta es

select Student_Id from student_table where Roll_No in (''A101'',''A102'',''A103'',.....''A250'');

En la cláusula contiene 250 valores. Cuando intenté ejecutar la consulta anterior en el estudio de administración de SQL, el resultado se recupera en 1 segundo y sin picos de CPU. Pero cuando intenté ejecutar la misma consulta mediante hibernación, los picos de la CPU alcanzaron el 100% durante unos 60 segundos y el resultado se recuperó alrededor de 60 segundos. La consulta de hibernación es,

Criteria studentCriteria = session.createCriteria(StudentTO.class); studentCriteria.add(Restrictions.in("rollNo", rollNoLists)); //rollNoLists is an Arraylist contains 250 Strings studentCriteria.setProjection(Projections.projectionList().add(Projections.property("studentId"))); List<Long> studentIds = new ArrayList<Long>(); List<Long> results = (ArrayList<Long>) studentCriteria.list(); if (results != null && results.size() > 0) { studentIds.addAll(results); } return studentIds;

¿Cuál es el problema por qué es así. Si la misma consulta se está ejecutando en Management Studio, el resultado se recupera sin picos y el resultado se recupera en 1 segundo. ¿¿¿Alguna solución???

Edit1: Mi consulta de hibernación generada es,

select this_.Student_Id as y0_ from student_table this_ where this_.Roll_No in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Edit2: Mi plan de ejecución Esto fue después de indexar roll_no

CREATE INDEX i_student_roll_no ON student_table (Roll_No)

,

Acabo de señalar esta parte de la respuesta de LBushkin para ti de esta publicación.

En segundo lugar, al usar IN u OR con un número variable de argumentos, está causando que la base de datos tenga que volver a analizar la consulta y reconstruir un plan de ejecución cada vez que cambien los argumentos. La construcción del plan de ejecución para una consulta puede ser un paso costoso. La mayoría de las bases de datos almacenan en caché los planes de ejecución de las consultas que ejecutan utilizando el texto de consulta EXACTO como clave. Si ejecuta una consulta similar pero con diferentes valores de argumento en el predicado, lo más probable es que la base de datos dedique una cantidad significativa de tiempo al análisis y la creación de planes de ejecución. Esta es la razón por la que las variables de enlace son muy recomendables como una forma de garantizar un rendimiento óptimo de las consultas.

Así que puedes intentar vincular variables para evitar ejecutar el plan de ejecución cada vez

Uso de variables de enlace (consultas parametrizadas en SQL Server)

La consulta que ejecuta desde la consola es fácil de almacenar en caché y es por eso que la respuesta es instantánea. Si observa la consulta, verá que todos los parámetros están incrustados en la consulta, por lo que el planificador de consultas puede detectar que no hay variaciones y que todas las ejecuciones irán siempre al mismo plan y al mismo resultado almacenado en caché.

La consulta que ejecuta con Hibernate, incluso si fuera una consulta nativa, utiliza PreparedStatement y los parámetros están vinculados en el momento de ejecución de la consulta y para citar a uno de los mejores autores en la indexación :

¿Qué tiene eso que ver con los parámetros de enlace?

Los cachés del plan de ejecución compartido de DB2, Oracle y SQL Server utilizan un valor de hash de la cadena de SQL literal como clave para el caché. Los planes en caché no se encuentran si el SQL contiene valores literales que varían con cada ejecución.

Los titulares de posición (parámetros de enlace) unifican la declaración para que la cadena SQL sea idéntica cuando se ejecuta con valores diferentes, lo que aumenta la tasa de aciertos de caché.

Para resolverlo, debe agregar un índice en ambas Roll_No ( Roll_No , Student_Id ) para que la consulta se convierta en una exploración de índice solamente.

Los valores predeterminados de SQL Server son índices de clúster , que lo limitan a un índice agrupado por tabla, por lo que es posible que desee convertir esta tabla en una heap table lugar y centrarse en las exploraciones de solo índice.

Para responder a la pregunta "por qué es lenta la hibernación", necesita ver el plan de ejecución real que usa el servidor cuando ejecuta su código de hibernación, NO el plan de ejecución que usa el servidor cuando ejecuta la consulta desde SSMS. La captura de pantalla con el plan de ejecución que incluyó en la pregunta no se parece al plan real que obtiene al ejecutar su código de hibernación. Una vez que tenga ese plan, puede compararlo con el plan que obtiene de SSMS y la diferencia probablemente explicará por qué es lento en un caso y rápido en otro.

Hay un muy buen artículo de Erland Sommarskog , que se centra en el llamado "parámetro sniffing", que podría ser la razón de un problema aquí, pero no es probable. Lo que es útil para nosotros en este artículo aquí es que él explica cómo puede extraer el plan de ejecución para examinarlo desde el caché.

Sin esta información solo puedes adivinar. Una suposición es que usted pasa sus parámetros como nvarchar , pero el campo indexado Roll_No es varchar , por lo que no se usa el índice. El servidor convierte su columna varchar a nvarchar para la comparación, lo que significa que no se puede usar el índice => es lento y la conversión puede ser la razón del alto uso de la CPU. http://sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

Aquí no hay una respuesta a su pregunta, sino una posible solución al problema. En lugar de pasar 250 parámetros individuales a la consulta para la cláusula IN use el table-valued parameter valores de tabla y pase sus valores como una tabla. En la consulta use JOIN lugar de IN . Esto es especialmente cierto después de sus comentarios que tendrá parámetros de 100K (lo que significa que desea ejecutar su consulta 400 veces). De hecho, 100K es un poco demasiado parejo para un parámetro con valores de tabla, por lo que consideraría tener una tabla auxiliar permanente o temporal, que mantendrá estos Roll_No con el índice adecuado . La consulta principal se uniría a ella. Algo como esto:

CREATE TABLE RollNumbers ( Roll_No VARCHAR(100) NOT NULL ,PRIMARY KEY (Roll_No) );

Asegúrese de que haya un índice en la tabla RollNumbers en Roll_No . Asegúrese de que haya un índice en la tabla student_table en Roll_No . Al principio, INSERT valores de 100K en RollNumbers y luego utilícelos en la consulta principal:

SELECT Student_Id FROM student_table INNER JOIN RollNumbers ON RollNumbers.Roll_No = student_table.Roll_No

Dependiendo del sistema general, la tabla RollNumbers podría ser una tabla permanente, una tabla temporal o una variable de tabla.

Parece que no tiene todos los registros al ejecutar la consulta, no desde la hibernación, sino en su código, todos los registros de la ejecución de la consulta colocados en la colección.

Por una extraña coincidencia, me encontré con este problema esta semana, y no es el problema de Hibernate N + 1 al que se refieren muchas personas aquí. Utilizo Amazon Redshift, con un controlador Java Postgres.

Por razones en las que no entraré aquí, estaba usando una declaración WHERE IN () de 7.000 parámetros que, cuando se ejecuta en la base de datos, da como resultado una ejecución de consultas de 10 segundos (es una consulta compleja en una tabla grande, y aún no he configurado la indexación, aún en desarrollo, no hemos empezado a ajustar. Cuando se ejecuta a través de Hibernate, hay un tiempo de ejecución de consulta de 120 segundos.

Descubrí que si obtiene la cadena SQL real de Hibernate, reemplace (?,? ...?) Con una cadena de los valores reales, y ejecute eso (aún a través de Hibernate), de repente todo vuelve en 10 segundos.

Al excavar en el interior de Hibernate, resulta que hacen una cantidad no trivial de procesamiento por parámetro , lo que resulta en un pico inicial de CPU y tiempos de ejecución inflados para declaraciones con un gran número de parámetros.

Además, una vez que la consulta se envía finalmente a la base de datos, la CPU del servidor de la base de datos aumenta al 100% durante el uso de los parámetros, pero no sin los parámetros. No he comprobado los tiempos exactos para determinar cuánto de todo este procesamiento está ocurriendo en qué lado de la cerca, pero parece que el uso de tantos parámetros no es viable ni en el lado de Hibernate ni en el lado de la base de datos.

¿La solución? Utilice menos parámetros. O busque una base de datos que admita grandes conjuntos de parámetros mientras se mantiene con buen rendimiento.

Probablemente vamos a cambiar de Hibernate a jOOQ, ya que jOOQ le permite definir sus propios fragmentos de SQL personalizados que funcionan con el DSL oficial. Luego, construiremos la cláusula IN () manualmente sin parámetros. Podemos hacer esto ya que nuestras variables IN son ID internas, por lo que la inyección de SQL no es una posibilidad, pero si la inyección de SQL es una posibilidad, asegúrese de desinfectar sus entradas.

Probablemente esté pensando que, debido a que su consulta lenta tarda 60 segundos, su consulta "rápida" en 1 segundo es realmente rápida. Este no es el caso. Esta diferencia de velocidad de ejecución le impide comprender el problema real aquí.

Un problema adicional (probablemente no sea el problema real)

El tipo muy simple de consulta que está ejecutando debería devolver resultados en menos de un milisegundo si tenía un índice en Roll_No , independientemente de si está utilizando variables de enlace o valores en línea.

Solo asumo que no tiene ningún índice aparte de los que se generan a partir de las restricciones en su tabla. Por lo tanto, debe agregar un índice simple en Roll_No :

CREATE INDEX i_student_roll_no ON student_table (Roll_No);

O puede agregar una columna adicional al índice anterior para convertirlo en un "índice de cobertura" para esta consulta ( como lo explica Vlad )

CREATE INDEX i_student_roll_no2 ON student_table (Roll_No, Student_Id);

Esto haría que esta consulta en particular sea aún más rápida, ya que el plan de ejecución no tendría que golpear el disco nuevamente para obtener el Student_Id de la tabla. La información ya estaría contenida en el índice. Sin embargo, use los índices de cobertura con moderación, ya que:

  1. Usa más espacio, específicamente para una mesa de tamaño mediano como la tuya
  2. Trabaje bien siempre y cuando sus consultas se limiten a exactamente las columnas que están realmente cubiertas, lo cual es poco probable que permanezca así en su caso.

¿Cómo reconocer esto usando SQL Server Management Studio?

En realidad, hay una característica muy agradable en SQL Server Management Studio. Cuando active los planes de ejecución (que debería), obtendrá esta información adicional sobre su consulta:

Haga clic con el botón derecho en esa información y seleccione "Detalles del índice que faltan ..." para obtener información similar a esta:

/* Missing Index Details from SQLQuery1.sql - LUKAS-ENVY/SQLEXPRESS.test (LUKAS-ENVY/Lukas (52)) The Query Processor estimates that implementing the following index could improve the query cost by 87.5035%. */ /* USE [test] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[student_table] ([Roll_No]) INCLUDE ([Student_Id]) GO */

¿Por qué la diferencia entre SQL Server Mgmt Studio e Hibernate?

Tu pregunta original aún no ha sido respondida. Tal vez, el índice adicional lo corrija, pero tal vez usted no proporcionó toda la información. Podrías tener:

  • Vincular problemas de lectura de variables
  • Problemas de N + 1 en Hibernate (con tantas filas)

Verifique el tipo de datos en el nivel de hibernación de todos los campos utilizados en la consulta y asegúrese de que coincida con su definición de tabla. Los marcos como hibernación utilizan tipos de datos compatibles con Unicode (por ejemplo, nvarchar). Trate de cambiar el tipo de datos en cualquiera de los lados.

Alternativamente, puede agregar un parámetro llamado sendStringParametersAsUnicode en su cadena de conexión. Forzará hibernación para usar varchar en lugar de nvarchar.

¡Solo inténtalo y déjanos saber!