Spring JPA Hibernate：慢速SELECT查询 [英] Spring JPA Hibernate : slow SELECT query
问题描述
我遇到一个优化问题，我无法弄清楚为什么我的查询太慢。
这里是我的实体：
@Entity
@Table（name =CLIENT）
public class Client {
private static final long serialVersionUID = 1L;
@Id
@Column（name =CLIENT_ID）
@SequenceGenerator（name =ID_GENERATOR，sequenceName =CLIENT_S，allocationSize = 1，initialValue = 1）
@GeneratedValue（strategy = GenerationType.SEQUENCE，generator =ID_GENERATOR）
private long ID;
@Column（name =LOGIN）
私人字符串登录;
@Column（name =PASSWORD）
私人字符串密码;
和DAO
@NoRepositoryBean
public interface ClientDao扩展JpaRepository< Client，Long> ;, JpaSpecificationExecutor< Client> {
客户端findByPasswordAndLogin（@Param（login）字符串customerLogin，@Param（password）字符串customerHashedPassword）;
}
执行方法findByPasswordAndLogin时，需要大约200ms才能完成通过Junit测试和JProfiler）。
$ b
这里Hibernate查询：
Hibernate：选择clientx0_.CLIENT_ID作为CLIENT_ID1_4_，clientx0_.LOGIN作为LOGIN9_4_，clientx0_.PASSWORD作为PASSWORD10_4_，clientx0_.STATUT作为STATUT13_4_来自CLIENT clientx0_，其中clientx0_.PASSWORD =？和clientx0_.LOGIN =？
当我手动执行数据库上的SQL查询时，它只需要3ms：
select * from CLIENT where PASSWORD ='xxxxx'and LOGIN ='yyyyyyyy'
在我们的开发环境中，我们有4000个客户。超过一百万的产品。
这里的上下文：
有什么想法？
我测试了不同类型的DAO（我不发布代码这里因为它很脏）：
$ b $ ul
使用（注入的）Spring JDBCTemplate和RowMapper ：〜70 ms
注：
- 由Spring注入的DAO代替新的ClientDao（）：+ 30ms丢失（-sick - ）
- 连接DB的时间： 46ms
我可以使用：
- Java语句和手动清理字段。
- 应用程序启动时的预连接
- 不要使用Spring Injection
ul> - 不是真正的安全/安全的
- 对于少量行来说很快，对于大量行将ResultSet映射为实体的速度很慢（我也有这个用例）
RowMapper 的 Spring JDBCTemplate 好像是 ：在特定情况下提高性能的最佳解决方案。
我们可以保留SQL查询的安全性。
但需要编写特定的RowMapper将ResultSet转换为实体。
但：
$ b
Spring JDBCTemplate示例
@Repository
public class ClientJdbcTemplateDao {
private final Logger logger = LoggerFactory.getLogger（ClientJdbcTemplateDao.class）;
private JdbcTemplate jdbcTemplate;
@Autowired
public void setDataSource（DataSource dataSource）{
this.jdbcTemplate = new JdbcTemplate（dataSource）;
}
public List< Client> find（）{
List< Client> c = this.jdbcTemplate.query（SELECT login FROM Client WHERE LOGIN ='xxxx'AND PASSWORD ='xxx'，new ClientRowMapper（））;
return c;
$ / code>
客户端RowMapper示例
公共类ClientRowMapper实现RowMapper< Client> {
$ b $ @Override
public Client mapRow（ResultSet arg0，int arg1）throws SQLException {
//这里实现转换器
//示例：
// String login = arg0.getString（LOGIN）
//客户端客户端=新客户端（登录）;
//返回客户端;
}
}
也许可以更好，任何建议都是值得欢迎的。 / p>
I encounter an optimisation problem and I can't figure out why my query is so slow.
Here my entity :
@Entity
@Table(name = "CLIENT")
public class Client {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "CLIENT_ID")
@SequenceGenerator(name = "ID_GENERATOR", sequenceName = "CLIENT_S", allocationSize = 1, initialValue = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ID_GENERATOR")
private Long id;
@Column(name="LOGIN")
private String login;
@Column(name="PASSWORD")
private String password;
And the DAO
@NoRepositoryBean
public interface ClientDao extends JpaRepository<Client, Long>, JpaSpecificationExecutor<Client> {
Client findByPasswordAndLogin(@Param("login") String customerLogin,@Param("password") String customerHashedPassword);
}
When the method findByPasswordAndLogin is executed, it takes about 200ms to be completed (seen both through Junit tests and with JProfiler).
Here the Hibernate query : Hibernate: select clientx0_.CLIENT_ID as CLIENT_ID1_4_, clientx0_.LOGIN as LOGIN9_4_, clientx0_.PASSWORD as PASSWORD10_4_, clientx0_.STATUT as STATUT13_4_ from CLIENT clientx0_ where clientx0_.PASSWORD=? and clientx0_.LOGIN=?
When I execute manually the SQL query on the database, it takes only 3ms :
select * from CLIENT where PASSWORD='xxxxx' and LOGIN='yyyyyyyy'
We have 4000 clients in our development environnement. More than a million in production.
Here the context :
- JDK 8
- Spring 4.1.6.RELEASE + JPA + Hibernate
- Oracle Database 10
Any idea ?
I have tested different types of DAO (I don't publish code here because it is so dirty) :
- With Hibernate : ~200ms
- With (Injected) Spring JDBCTemplate and RowMapper : ~70 ms
- With Java Statement : ~2 ms
- With Java OracleStatement : ~5 ms
- With Java PreparedStatement : ~100ms
- With Java PreparedStatement adjusted with Fetch size = 5000 : ~50ms
- With Java OraclePreparedStatement : ~100ms
- With Java OraclePreparedStatement adjusted with PreFetch size = 5000 : ~170ms
Notes :
- DAO injected by Spring instead of new ClientDao() : +30ms lost (-sick-)
- Connection time to DB : 46ms
I could use :
- Java Statement with manual sanitized fields.
- Pre-connection on application launch
- Do not use Spring Injection
But :
- Not really secured / safe
- Fast for a small number of rows, slow to map ResultSet to entity on large number of rows (I also have this use case)
So :
The Spring JDBCTemplate with RowMapper seems to be the best solution to increase performances on specific case. And we can keep a security on SQL queries. But need to write specific RowMapper to transform ResultSet to Entity.
Example of Spring JDBCTemplate
@Repository
public class ClientJdbcTemplateDao {
private final Logger logger = LoggerFactory.getLogger(ClientJdbcTemplateDao.class);
private JdbcTemplate jdbcTemplate;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<Client> find() {
List<Client> c = this.jdbcTemplate.query( "SELECT login FROM Client WHERE LOGIN='xxxx' AND PASSWORD='xxx'", new ClientRowMapper());
return c;
}
}
Example of Client RowMapper
public class ClientRowMapper implements RowMapper<Client> {
@Override
public Client mapRow(ResultSet arg0, int arg1) throws SQLException {
// HERE IMPLEMENTS THE CONVERTER
// Sample :
// String login = arg0.getString("LOGIN")
// Client client = new Client(login);
// return client;
}
}
Maybe can be better, any suggestion is welcome.
这篇关于Spring JPA Hibernate：慢速SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
