英文:
Simple JPA findBy request takes 4 minutes vs 365 milliseconds of SQL Query
问题
在Spring Boot应用程序中，我正在使用JpaRepository接口来映射一个没有外键的表。
我的pom.xml文件包含：
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-tools</artifactId>
<version>4.3.2.Final</version>
</dependency>
因此，我正在使用Hibernate。
我的实体类如下：
@Entity
@Table(name = "TABLE_NAME")
@NamedQuery(name = "CbmAnomalyDectOutput.findAll", query = "SELECT c FROM EntityName c")
public class EntityName implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name = "ID_GENERATOR", sequenceName = "MY_SEQ", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ID_GENERATOR")
private long id;
@Column(name = "ANOMALY_CLASS")
private String anomalyClass;
@Column(name = "ANOMALY_PROB")
private BigDecimal anomalyProb;
@Column(name = "ANOMALY_SEVERITY")
private BigDecimal anomalySeverity;
等等！
我创建了方法findByAnomalyClass(String anomalyClass)。
表中包含17,050条记录，查询返回大约3,000条记录。
但是……它需要4分钟！！！
与SQL查询进行比较，执行时间要短得多。
编辑：我启用了非常详细的日志，并注意到对象org.hibernate.loader.Loader是问题所在！它以相同的时间戳记录了2048行，然后是其他145行和其他行。
这是关键部分。
从结果集中的2048行移动到2049行，整体执行时间变得非常非常长！
有什么建议吗？
英文:
I am using the JpaRepository interface in a Spring Boot application to map a table without foreign keys.
My pom.xml contains:
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-tools</artifactId>
<version>4.3.2.Final</version>
</dependency>
So, I am using Hibernate.
My entity looks like this:
@Entity
@Table(name = "TABLE_NAME")
@NamedQuery(name = "CbmAnomalyDectOutput.findAll", query = "SELECT c FROM EntityName c")
public class EntityName implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name = "ID_GENERATOR", sequenceName = "MY_SEQ", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ID_GENERATOR")
private long id;
@Column(name = "ANOMALY_CLASS")
private String anomalyClass;
@Column(name = "ANOMALY_PROB")
private BigDecimal anomalyProb;
@Column(name = "ANOMALY_SEVERITY")
private BigDecimal anomalySeverity;
and so on!
I created the method findByAnomalyClass(String anomalyClass).
The table contains 17,050 records and the query returns about 3,000 of them.
BUT... It takes 4 minutes!!!
Comparing it with SQL query, the execution time is many times shorter.
EDIT: I activated a very verbose log and I noticed that the object org.hibernate.loader.Loader is the problem! It logs 2048 rows with the same timestamp, then other 145 and the the other rows.
THIS is the critical part.
MOVING FROM A RESULTSET OF 2048 TO 2049 THE OVERALL EXECUTION TIME BECOMES VERY VERY VERY LONG!
Any suggestion?
答案1
得分: 0
- 你需要确保由JPA生成的查询与预期相符。你可以配置JPA将查询记录在日志文件中。
- 假设查询相同，我们可以期望响应时间大致相同。但有一个例外，就是当结果集行尚未在内存中时。在这种情况下，首次查询执行将需要从磁盘读取到内存，然后才能返回结果集。
- 根据在Java代码中处理结果集的方式，在从数据库中检索结果集到完成使用该数据的业务逻辑之间可能会有显著的时间差。在调试会话中，您可以找出是否存在这种情况。
英文:
- You need to make sure that the query generated by JPA is the one you expect. You can configure JPA to log queries in log file.
- Assuming that queries are same we can expect that response times are approximately same. One exception to this is when result set rows are not in memory already. In that case first query execution will need to read from disc into memory before returning result set.
- Depending on how result set is processed in Java code there may be a significant time between retrieving result set from database and completing business logic using that data. You can find out if this is the case in a debug session.
专注分享java语言的经验与见解，让所有开发者获益！
评论