hibernate的面向对象查询的效率有多低？

前言

　　老平台的查询速度很慢，需要进行优化(...说白了就是优化sql语句)，老平台用的strus2+hibernate框架，查询基本都是使用的HSQL。

关于hsql

　　HQL是Hibernate Query Language即Hibernate查询语言

　　优点：

　　　　不需要再编写繁复的SQL 语句，针对实体类及其属性进行查询

    　　　查询结果是直接存放在List 中的对象，不需要再次封装

       　　独立于数据库(针对不同数据库进行查询，跨数据库,Mysql->Oracle)，

      　　 对不同的数据库根据Hibernate dialect 属性的配置自动生成不同的SQL 语句执行

　　缺点：

　　　　涉及到复杂的sql或者数据量大的情况，不好优化

　　

例子

　　我的项目里就是一张单表的分页查询，一共2百多万条数据。(oracle数据库)

下面是sql分页查询时打印出来的原生sql是这样的。。

 1     
 2     select
 3         * 
 4        from
 5         ( select
 6             row_.*,
 7             rownum rownum_ 
 8         from
 9             ( select
10                 tblmerinfo0_.MER_CODE as MER1_19_,
11                 tblmerinfo0_.MER_NAME as MER2_19_,
12                 tblmerinfo0_.MER_SHORT_NAME as MER3_19_,
13                 tblmerinfo0_.MER_CITY as MER4_19_,
14                 tblmerinfo0_.MER_NATION as MER5_19_,
15                 tblmerinfo0_.MER_STATUS as MER6_19_,
16                 tblmerinfo0_.MER_ACQ_CODE as MER7_19_,
17                 tblmerinfo0_.MER_TYPE as MER8_19_,
18                 tblmerinfo0_.MER_WARNING_AMT as MER9_19_,
19                 tblmerinfo0_.MER_GROUP_ID as MER10_19_,
20                 tblmerinfo0_.MER_COMMISION_TYPE as MER11_19_,
21                 tblmerinfo0_.MER_COMMISION_VALUE as MER12_19_,
22                 tblmerinfo0_.MER_TRANS_CURR as MER13_19_,
23                 tblmerinfo0_.MER_SETT_CURR as MER14_19_,
24                 tblmerinfo0_.MER_SETT_ACCT_NAME as MER15_19_,
25                 tblmerinfo0_.MER_SETT_ACCT as MER16_19_,
26                 tblmerinfo0_.MER_CB_CODE as MER17_19_,
27                 tblmerinfo0_.MER_CB_NAME as MER18_19_,
28                 tblmerinfo0_.MER_CORP as MER19_19_,
29                 tblmerinfo0_.MER_MASTER as MER20_19_,
30                 tblmerinfo0_.MER_REL_MAN as MER21_19_,
31                 tblmerinfo0_.MER_REL_TEL as MER22_19_,
32                 tblmerinfo0_.MER_FAX as MER23_19_,
33                 tblmerinfo0_.MER_EMAIL as MER24_19_,
34                 tblmerinfo0_.MER_ADDR as MER25_19_,
35                 tblmerinfo0_.MER_POSTCODE as MER26_19_,
36                 tblmerinfo0_.MER_PASSWORD as MER27_19_,
37                 tblmerinfo0_.PASSWORD_INITIAL_VAL as PASSWOR28_19_,
38                 tblmerinfo0_.PASSWORD_EXPIRED_DATE as PASSWOR29_19_,
39                 tblmerinfo0_.PASSWORD_EXPIRED as PASSWOR30_19_,
40                 tblmerinfo0_.BATCH_NO as BATCH31_19_,
41                 tblmerinfo0_.REC_CREATE_TM as REC32_19_,
42                 tblmerinfo0_.REC_UPDATE_TM as REC33_19_,
43                 tblmerinfo0_.MER_OPEN_TM as MER34_19_,
44                 tblmerinfo0_.MER_CLOSE_TM as MER35_19_,
45                 tblmerinfo0_.MER_DAY_LIMIT as MER36_19_,
46                 tblmerinfo0_.MER_SINGLE_LIMIT as MER37_19_,
47                 tblmerinfo0_.SUPPORT_BRAND_FLAG as SUPPORT38_19_,
48                 tblmerinfo0_.ROUTE_SCHEME as ROUTE39_19_,
49                 tblmerinfo0_.BATCH_SETT_POINT as BATCH40_19_,
50                 tblmerinfo0_.DELAY_SETT_DAYS as DELAY41_19_,
51                 tblmerinfo0_.DCC_FLAG as DCC42_19_,
52                 tblmerinfo0_.SUPPORT_FUNC_FLAG as SUPPORT43_19_,
53                 tblmerinfo0_.CITY_CODE as CITY44_19_,
54                 tblmerinfo0_.SUPPORT_DCC_FLAG as SUPPORT45_19_ 
55             from
56                 SWTONLINE.TBL_MER_INFO tblmerinfo0_ 
57             order by
58                 tblmerinfo0_.MER_CODE ) row_ ) 
59         where
60             rownum_ <= 24
61             and rownum_ > 12

执行时间：63s

个人分析了一下慢的原因：里层的查询像是查询了所有，然后外层的查询在分页。仅个人观点。如果有大神看到这里有不同的看法欢迎指出

下面是我个人的原生sql:

1 select * from 
2           (
3             select a.*, rownum r from 
4             （
5               select * from tbl_mer_info where 1=1 order by mer_code
6             )  a  where rownum <= 24
7           ) b where b.r > 12

执行时间：0.2s

个人分析：与上面的sql不同就是我的rownum先查询的<=24,然后外层在>12，这样会大大提交效率

写到这里，不禁就开始比较hibernate与mybatis的各自优点，个人觉得还是mybatis好，够灵活。虽然hibernate针对各个数据库设定了"方言"，号称兼容性好，可是谁会在开发的过程中替换数据库呢？

posted @ 2018-08-18 15:07  李勇888  阅读(1088)  评论(0)  编辑  收藏  举报