h**k
发帖数: 662 | 1 我有这么一个问题，现有一个stored procedure对一个table进行操作，table是
million record,stored procedure比如有100个sql，进行计算和更新table的数据。
然后java 来call这个stored procedure。
新的要求是stored procedure将要不存在，这部分逻辑由java来处理。所以很自然有两
个办法。
1. 把100个sql整理出来到java side，由java来一个个call.在速度和简易程度上都非
常好。
2. 把100个sql的逻辑放到java code里。loop table million record,对每个row进行
计算，然后再把结果从java 存回去。这么大的数据来回走一趟速度肯定很慢，整合sql
的逻辑也是很费时间的。
我很自然要用1的办法来做，大家看看有什么意见没有。谢谢 | g*****g
发帖数: 34805 | 2 You may have transaction issue using 1.
sql
【在 h**k 的大作中提到】
: 我有这么一个问题，现有一个stored procedure对一个table进行操作，table是
: million record,stored procedure比如有100个sql，进行计算和更新table的数据。
: 然后java 来call这个stored procedure。
: 新的要求是stored procedure将要不存在，这部分逻辑由java来处理。所以很自然有两
: 个办法。
: 1. 把100个sql整理出来到java side，由java来一个个call.在速度和简易程度上都非
: 常好。
: 2. 把100个sql的逻辑放到java code里。loop table million record,对每个row进行
: 计算，然后再把结果从java 存回去。这么大的数据来回走一趟速度肯定很慢，整合sql
: 的逻辑也是很费时间的。
| A**o
发帖数: 1550 | | h**k
发帖数: 662 | 4 从java 送过去的sql好stord procedure里执行的sql 在性能上并没有多大差别
【在 A**o 的大作中提到】
: 新的要求会失败，性能会是大问题。
| r*****s
发帖数: 985 | 5 我觉得应该没有Stored procedure快，
但是如果非要选择，
只能1吧，
Define named queries
transaction可以用spring裹一下整个method
2你是说用java entity一个个过是吧，
当然慢了，
在你这个million records的情况下我觉得不可能。
【在 h**k 的大作中提到】
: 从java 送过去的sql好stord procedure里执行的sql 在性能上并没有多大差别
| g*****g
发帖数: 34805 | 6 It depends on whether the process can be partitioned,
and the IO/CPU on the DB server.
Assume a very simple case that each row is on its own,
you want to do some complicate calculation on each row
to fill a column.
If you use an application cluster to do the job in
parallel, then DB IO is the bottleneck. However,
if you use the store procedure, CPU power on the DB server is
the bottleneck.
Recent trend is to keep DB dumb and avoid store procedure
as much as possible. The reason is easy and cheap application
clustering/caching that can scale much better than relational DB clustering.
Also java code is more maintainable. That being said, nothing
wrong to run a daily data intensive reporting SP at midnight.
【在 r*****s 的大作中提到】
: 我觉得应该没有Stored procedure快，
: 但是如果非要选择，
: 只能1吧，
: Define named queries
: transaction可以用spring裹一下整个method
: 2你是说用java entity一个个过是吧，
: 当然慢了，
: 在你这个million records的情况下我觉得不可能。
| r*****s
发帖数: 985 | 7 good point,
better thought.
my observation is that if not carefully coded,
Java codes with ORM/DAO may not only unnecessarily complicate the queries,
but also cost more memory to save data that are not needed at all.
That's why I got a feeling that well written hql/sql
sound be more efficient, when the java code looks more like
a bad interpretation of the queries.
but i agree with your points from a different angle.
【在 g*****g 的大作中提到】
: It depends on whether the process can be partitioned,
: and the IO/CPU on the DB server.
: Assume a very simple case that each row is on its own,
: you want to do some complicate calculation on each row
: to fill a column.
: If you use an application cluster to do the job in
: parallel, then DB IO is the bottleneck. However,
: if you use the store procedure, CPU power on the DB server is
: the bottleneck.
: Recent trend is to keep DB dumb and avoid store procedure
| h**k
发帖数: 662 | 8 好虫说的不错。用distributed computing可以在java side很好的解决这个问题。不过
对于我的工作环境这个问题还不能在cluster下来解决。
所以对于redbuds说的就是我自己在想的，用ORM来解决绝对是performance很差的。
java/sql和stored procedure也有很多争论，主要是针对 SP的performance很好，不过
其实SP只是对于当db server 和application server 之间有数据来来回回时候占有优
势，当纯粹就是insert, update,delete sql的时候，statement 和 SP在performance
基本是一样的。
【在 r*****s 的大作中提到】
: good point,
: better thought.
: my observation is that if not carefully coded,
: Java codes with ORM/DAO may not only unnecessarily complicate the queries,
: but also cost more memory to save data that are not needed at all.
: That's why I got a feeling that well written hql/sql
: sound be more efficient, when the java code looks more like
: a bad interpretation of the queries.
: but i agree with your points from a different angle.
| b******y
发帖数: 1684 | 9 if performance is an issue, use batch to divide and conquer.
sql
【在 h**k 的大作中提到】
: 我有这么一个问题，现有一个stored procedure对一个table进行操作，table是
: million record,stored procedure比如有100个sql，进行计算和更新table的数据。
: 然后java 来call这个stored procedure。
: 新的要求是stored procedure将要不存在，这部分逻辑由java来处理。所以很自然有两
: 个办法。
: 1. 把100个sql整理出来到java side，由java来一个个call.在速度和简易程度上都非
: 常好。
: 2. 把100个sql的逻辑放到java code里。loop table million record,对每个row进行
: 计算，然后再把结果从java 存回去。这么大的数据来回走一趟速度肯定很慢，整合sql
: 的逻辑也是很费时间的。
| A**o
发帖数: 1550 | 10
queries,
my company is currently is this ORM hole.
with the wrong people, i'm not sure if the project will survive the next
deadline. //finger crossed.
lesson:
2. ORM is not golden finger
1. cache carefully, otherwise it's hell.
0. reckless people is far worse than incompetent ones. reckless people
with good people skill, hell++.
【在 r*****s 的大作中提到】
: good point,
: better thought.
: my observation is that if not carefully coded,
: Java codes with ORM/DAO may not only unnecessarily complicate the queries,
: but also cost more memory to save data that are not needed at all.
: That's why I got a feeling that well written hql/sql
: sound be more efficient, when the java code looks more like
: a bad interpretation of the queries.
: but i agree with your points from a different angle.
| r*****s
发帖数: 985 | 11 good discussion,
now we've seen pros (from goodbug) and cons of ORM
in our projects.
i agree that you can't count on the individual hackers
to follow the best practices once they are given too much
freedom.
To some extent,
SP-->ORM is like releasing the Genie from the (db) bottle,
while the Genie has been controlled by db people after
2 decades of research -- at least I dare not compare the
java code with the optimized sql interpreter.
【在 A**o 的大作中提到】
:
: queries,
: my company is currently is this ORM hole.
: with the wrong people, i'm not sure if the project will survive the next
: deadline. //finger crossed.
: lesson:
: 2. ORM is not golden finger
: 1. cache carefully, otherwise it's hell.
: 0. reckless people is far worse than incompetent ones. reckless people
: with good people skill, hell++.
| g*****g
发帖数: 34805 | 12 Most enterprise applications can benefit from caching,
and with ORM, this can be easily achieved, which is not
the case for SP.
It's premature to say SP is more performant, I would say
most cases it doesn't.
There are unqualified coders in many projects, but I'd
rather fix java code than PL/SQL
【在 r*****s 的大作中提到】
: good discussion,
: now we've seen pros (from goodbug) and cons of ORM
: in our projects.
: i agree that you can't count on the individual hackers
: to follow the best practices once they are given too much
: freedom.
: To some extent,
: SP-->ORM is like releasing the Genie from the (db) bottle,
: while the Genie has been controlled by db people after
: 2 decades of research -- at least I dare not compare the
| A**o
发帖数: 1550 | 13
I'm having this problem, we have a complex ORM.
For example, there is an Order object, having many to one relationships
with Customer and Vendor, and having one to many relationships Item and
Payment etc. So, how can this be cached effectively? Suppose user is
likely to view the Order again and again. Another question is how the
object model should be, say, should Vendor has an Order set/list, and
what's the best cascading options. And what about reverse.
【在 g*****g 的大作中提到】
: Most enterprise applications can benefit from caching,
: and with ORM, this can be easily achieved, which is not
: the case for SP.
: It's premature to say SP is more performant, I would say
: most cases it doesn't.
: There are unqualified coders in many projects, but I'd
: rather fix java code than PL/SQL
| g*****g
发帖数: 34805 | 14 There are simple strategies that should work fairly well.
For example, you can estimate the ratio of each type of
entity in browsing. Now use LRU eviction and configure the
number of cache entries for each type.
e.g. 1K Customers, 10K Orders if the ratio is 1:10 in average
Hibernate with ehCache get you that. You can fine tune with
in memory and secondary cache too.
You don't really care about relationship in that regard. They
are just entities that are likely being revisited soon.
【在 A**o 的大作中提到】
:
: I'm having this problem, we have a complex ORM.
: For example, there is an Order object, having many to one relationships
: with Customer and Vendor, and having one to many relationships Item and
: Payment etc. So, how can this be cached effectively? Suppose user is
: likely to view the Order again and again. Another question is how the
: object model should be, say, should Vendor has an Order set/list, and
: what's the best cascading options. And what about reverse.
|
|
