使用 .Net 从 Oracle DB 读取数据比使用 Java 快 10 倍
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12088195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reading data from Oracle DB using .Net is 10 times faster than using Java
提问by anth
I'm reading 1 mln records from Oracle DB using .Net and Java. In .Net I'm using ODP.Net, in Java ojdbc6 thin client. In .Net reading data takes about 10 seconds, and in Java it takes nearly 2 minutes. Why there is such huge difference ?
我正在使用 .Net 和 Java 从 Oracle DB 读取 100 万条记录。在 .Net 中，我使用的是 ODP.Net，在 Java ojdbc6 瘦客户端中。在.Net中读取数据大约需要10秒，而在Java中则需要将近2分钟。为什么会有如此巨大的差异？
Here is a code:
这是一个代码：
.Net:
。网：
try
{
DateTime dt1 = DateTime.Now;
OracleConnection con = new OracleConnection();
con.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myService)));User Id=myId;Password=myPass;";
con.Open();
string cmdQuery = "SELECT * FROM DROPME";
OracleCommand cmd = new OracleCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
int i = 0;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Object o1 = reader.GetValue(0);
Object o2 = reader.GetValue(1);
Object o3 = reader.GetValue(2);
Object o4 = reader.GetValue(3);
Object o5 = reader.GetValue(4);
Object o6 = reader.GetValue(5);
Object o7 = reader.GetValue(6);
i++;
}
DateTime dt2 = DateTime.Now;
double elapsed = (dt2 - dt1).TotalSeconds;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Java:
爪哇：
try
{
long t0 = System.currentTimeMillis();
oracleDataSource = new OracleDataSource();
oracleDataSource.setURL("jdbc:oracle:thin:myId/myPass@myHost:myPort:myService");
Connection connection = oracleDataSource.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT * FROM DROPME");
ResultSet result = statement.executeQuery();
int i = 0;
while (result.next())
{
result.getObject(1);
result.getObject(2);
result.getObject(3);
result.getObject(4);
result.getObject(5);
result.getObject(6);
result.getObject(7);
i++;
}
long t1 = System.currentTimeMillis();
long elapsed = (t1 - t0)/1000;
int t = 0;
}
catch (Exception ex)
{
ex.printStackTrace();
}
EDIT: setFetchSize() did the job, thanks.
编辑： setFetchSize() 完成了这项工作，谢谢。
采纳答案by PC.
In Java by default, ResultSets are completely retrieved and stored in memory. This is not good for queries with large ResultSets. To use a streamed result u must use:
默认情况下，在 Java 中，ResultSet 被完全检索并存储在内存中。这对于具有大型 ResultSet 的查询不利。要使用流式结果，您必须使用：
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
I've not compared the time taken, but i guess this will be much faster.
我没有比较所用的时间，但我想这会快得多。
回答by Adam Paynter
In my experience, the Oracle JDBC driver is poorly configured out-of-the-box for mass transfer. By default, it only transfers 10 records across the network at a time. Therefore, if you have 1,000,000 records, the driver will incur 100,000 network accesses.
根据我的经验，Oracle JDBC 驱动程序的开箱即用配置很差，无法进行大容量传输。默认情况下，它一次仅通过网络传输 10 条记录。因此，如果您有 1,000,000 条记录，驱动程序将产生 100,000 次网络访问。
You can tell the ResultSethow many records to fetch at a time with this code:
您可以ResultSet使用以下代码告诉一次要获取多少条记录：
result.setFetchSize(1000);
Feel free to experiment with different sizes. It has dramatically decreased processing time (from minutes to seconds) in at least one application I have worked on.
随意尝试不同的尺寸。它至少在我处理过的一个应用程序中显着减少了处理时间（从几分钟到几秒钟）。
