ResultSet的Oracle JDBC性能 [英] Oracle JDBC performance of ResultSet

问题描述

到目前为止，我正在使用类似这样的方式来查询运行良好的数据库:

I was using so far something like this for querying my database that was working perfectly fine :

PreparedStatement prepStmt = dbCon.prepareStatement(mySql);
ResultSet rs = prepStmt.executeQuery();

但是后来我需要使用rs.first();以便能够遍历我的rs多次.所以我现在使用

But then I needed to use the rs.first(); in order to be able to iterate over my rs multiple times. So I use now

PreparedStatement prepStmt = dbCon.prepareStatement(mySql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);    

我的问题与两者的表现有关.如果使用第二种选择，我会失去什么?使用第二个选项是否会对我到目前为止编写的代码产生负面影响?

My question is related to the performance of the two. What do I lose if I use the second option? Will using the second option have any negative effect in the code that I have written so far?

PS:请注意，我的应用程序是使用后端Oracle 11g数据库的多用户，数据库密集型Web应用程序(在Weblogic 10.3.4上).

PS: Note that my application is a multi-user, database-intensive web application (on a Weblogic 10.3.4) that uses a back end Oracle 11g database.

感谢您的关注.

更新

我的最大reslutset大小将小于1000行15-20列

My maximum reslutset size will be less than 1000 rows and 15-20 columns

推荐答案

由于Oracle游标是仅向前结构，因此为了模拟可滚动游标，JDBC驱动程序通常需要将结果缓存在内存中希望能够确保第二次遍历结果时返回相同的结果.根据查询返回的结果的数量和大小，这可能涉及在应用程序服务器上消耗大量额外的内存.另一方面，这应该意味着第二次迭代ResultSet的效率应该比第一次更好.

Since an Oracle cursor is a forward-only structure, in order to simulate a scrollable cursor, the JDBC driver would generally need to cache the results in memory if it wants to be able to ensure that the same results are returned when you iterate through the results a second time. Depending on the number and size of the results returned from the query, that can involve a substantial amount of additional memory being consumed on the application server. On the other hand, that should mean that iterating through the ResultSet a second time should be much more efficient than the first time.

所需的额外内存是否有意义取决于您的应用程序.您说最大的ResultSet将具有1000行.如果您认为每行为500字节(这显然取决于数据类型-如果您的ResultSet仅具有一堆数字，则它会小得多，如果它包含一堆长的描述字符串，则可能是更大)，每用户1000行为500 kb.如果您有1000个并发用户，那么这仅是500 MB的存储空间，这可能并不令人望而却步.另一方面，如果同时拥有100万用户，则为500 GB，这可能意味着您要购买一些新服务器.如果您的行是5000字节而不是500字节，那么您说的是5 GB的RAM，这可能是应用程序服务器运行应用程序所需的内存的很大一部分.

Whether the extra memory required is meaningful depends on your application. You say that the largest ResultSet will have 1000 rows. If you figure that each row is 500 bytes (this will obviously depend on data types-- if your ResultSet just has a bunch of numbers, it would be much smaller, if it contains a bunch of long description strings, it may be much larger), 1000 rows is 500 kb per user. If you've got 1000 simultaneous users, that's only 500 MB of storage which probably isn't prohibitive. If you've got 1 million simultaneous users, on the other hand, that's 500 GB which is probably means that you're buying a few new servers. If your rows are 5000 bytes rather than 500, then you're talking about 5 GB of RAM which could be a large fraction of the memory required on the application server to run your application.

这篇关于ResultSet的Oracle JDBC性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！