在spring / hibernate应用程序中提高本机sql查询的性能？

I have below the native sql query. i am using Oracle database.

我在本机sql查询下面。我正在使用Oracle数据库。

select
         * 
     from
         (select
             row_.*,
             rownum rownumber 
         from
             (select
                 colmn1,
                 colmn2,
                 colmn3,
                 colmn4,
                 colmn5,
                 colmn6,
                 from
                 Table5
             where
                 colmn5 In (
                    '19901','10001'
                 ) 
             order by
                colmn1 ) row_ ) 
         Where
             Rownumber <= 50000
             and rownumber > 0

Above query returns 50000 records. if i execute above query in sqldeveloper, it takes only 30 seconds but in the spring and hibernate integrated application it takes 15 minutes. How can i improve the performance?

上面的查询返回50000条记录。如果我在sqldeveloper中执行上面的查询,它只需要30秒,但在春季和hibernate集成应用程序需要15分钟。我怎样才能提高性能?

Thanks!

1 个解决方案

#1

You have two inner selects. An inner select always is a possible source of bad performance, because it might hinder the database from finding the optimal search strategy.

你有两个内在选择。内部选择总是可能导致性能不佳,因为它可能会阻碍数据库找到最佳搜索策略。

As far ar I can see you use the inner selects only for handling the row number. If you use only the most inner select and handle the row number on java/hibernate level, then you'll get a much better performance.

到目前为止,我可以看到你只使用内部选择来处理行号。如果你只使用最内部的select并在java / hibernate级别处理行号,那么你将获得更好的性能。

You only use this select

您只能使用此选择

select colmn1, colmn2, colmn3, colmn4, colmn5, colmn6,
   from Table5
   where colmn5 In ('19901','10001') 
   order by colmn1

which, as it does not have any database specialities any more easily can be replaced by an HQL statement and so making your program independent of the used database (Java class and property names should be replaced by the real ones):

因为它没有任何数据库专业,所以更容易被HQL语句替换,因此使您的程序独立于使用的数据库(Java类和属性名称应该被真实的替换):

from Table5_Class
   where colmn5_Prop in ('19901','10001') 
   order by colmn1_prop

Then you replace your where condition Where Rownumber <= 50000 and rownumber > 0 by the hibernate methods Query.setMaxResults(50000) and Query.setFirstResult(0) (remark: setFirstResult(0) is superfluous as row 0 always is the first one, but I guess you also want to get the next 50000 rows, and then you can use setFirstResult(n)).

然后用hibernate方法查询Rownumber <= 50000和rownumber> 0的where where条件Query.setMaxResults(50000)和Query.setFirstResult(0)(注释:setFirstResult(0)是多余的,因为第0行总是第一个,但我想你也想获得下一个50000行,然后你可以使用setFirstResult(n))。

If you need the rownumber as a parameter then you can use the index of the resulting List for this.

如果您需要rownumber作为参数,那么您可以使用结果列表的索引。

P. S: I can't tell you why your select is so much faster in the SQL developer than in Hibernate.

P. S:我不能告诉你为什么你的选择在SQL开发人员中比在Hibernate中快得多。

#1