该ROW_NUMBER()
功能将对性能产生负面影响,并且您正在运行数千次。您已经在使用OracleDataReader
-它不会一次将所有四百万行都拉到您的计算机上,它基本上是一次流一次或几行。
这必须在几分钟或几小时内完成,而不是几天-我们有几个进程以类似的方式在Sybase和sql Server之间移动数百万条记录,并且花费不到五分钟。
也许试一下:
OracleCommand cmd = new OracleCommand("SELECT ... FROM TableName", oracle_connection);
int batchSize = 500;
using (OracleDataReader reader = cmd.ExecuteReader())
{
List<Record> l = new List<Record>(batchSize);
string[] str = new string[7];
int currentRow = 0;
while (reader.Read())
{
for (int i = 0; i < 7; i++)
{
str[i] = reader[i].ToString();
}
l.Add(new Record(str[0], str[1], str[2], str[3], str[4], str[5], str[6]));
// Commit every time batchSize records have been read
if (++currentRow == batchSize)
{
Commit(l);
l.Clear();
currentRow = 0;
}
}
// commit remaining records
Commit(l);
}
以下是Commit
可能的样子:
public void Commit(IEnumerable<Record> records)
{
// TODO: Use ES's BULK features, I don't kNow the exact Syntax
client.IndexMany<Record>(records, "index", "type");
// client.Bulk(b => b.IndexMany(records))... something like this
}