Page 1 of 1
large resultset slow performance (fetch size?)
Posted: Mon 26 Oct 2020 17:13
by SebastianTusk
We have an issue with slow performance for a query which fetches >5 million rows.
With an ODBC based connection (devart's or oracle's instantclient) the query is finished 10 times faster. At least with an appropriately large MinFetchRows or FBS value.
Dotconnect has a "Default Fetch Size" and "OracleCommand.FetchSize" parameter which looks like it would do the same as MinFetchRows or FBS. But unfortunately different values do not change anything regarding the query speed.
Isn't a performance comparable to the ODBC drivers expected?
Re: large resultset slow performance (fetch size?)
Posted: Tue 27 Oct 2020 11:13
by Shalex
SebastianTusk wrote: ↑Mon 26 Oct 2020 17:13
Dotconnect has a "Default Fetch Size" and "OracleCommand.FetchSize" parameter which looks like it would do the same as MinFetchRows or FBS.
Refer to
https://www.devart.com/dotconnect/oracl ... hSize.html.
SebastianTusk wrote: ↑Mon 26 Oct 2020 17:13
We have an issue with slow performance for a query which fetches >5 million rows.
With an ODBC based connection (devart's or oracle's instantclient) the query is finished 10 times faster. At least with an appropriately large MinFetchRows or FBS value.
1. The fastest way to read data is via
OracleDataReader.
2. If you use
OracleDataTable, try setting its
FetchAll property to False.
Re: large resultset slow performance (fetch size?)
Posted: Tue 27 Oct 2020 17:13
by SebastianTusk
Yes, OracleCommand.FetchSize is what we tried. See attached example. But just as I said the same operation with an ODBC connection is more than 10 times faster.
Would you expect similar performance with Dotconnect and Devart ODBC?
Code: Select all
public void BuildSnpTextData()
{
try
{
var now = DateTime.Now;
//var cmd = this.Connection.CreateCommand("SELECT * FROM SNP_DATA WHERE ROWNUM <= 1000"); //WHERE ROWNUM <= 1000
var cmd = this.Connection.CreateCommand("SELECT * FROM SNP_DATA");
//var cmd = this.Connection.CreateCommand("SELECT COUNT(*) FROM SNP_DATA");
cmd.FetchSize = 100000000;
cmd.InitialLobFetchSize = 100000000;
using (var reader = cmd.ExecuteReader())
{
using (var stream = File.CreateText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SnpData.txt")))
{
var sb = new StringBuilder();
var isFirstLine = true;
var row = 0;
while (reader.Read())
{
var fieldCount = reader.FieldCount;
row++;
if (isFirstLine)
{
isFirstLine = false;
sb.Append("\"Row\";");
for (var i = 0; i < fieldCount; i++)
{
sb.Append($"\"{reader.GetName(i)}\"");
if (i < fieldCount - 1) sb.Append(";");
}
sb.AppendLine();
}
sb.Append($"\"{row}\";");
for (var i = 0; i < reader.FieldCount; i++)
{
sb.Append($"\"{reader.GetValue(i)}\"");
if (i < fieldCount - 1) sb.Append(";");
}
stream.WriteLine(sb.ToString());
sb.Clear();
}
}
}
DataManagementLogger.Source.TraceInformation($"SnpData.txt file created in {(DateTime.Now - now).TotalMilliseconds} ms.", LogCategories.Data);
}
catch (Exception ex)
{
DataManagementLogger.Source.TraceError(ex, LogCategories.Data);
throw;
}
}
Re: large resultset slow performance (fetch size?)
Posted: Fri 30 Oct 2020 15:36
by Shalex
1. Is the performance better with default values (0) of cmd.FetchSize and cmd.InitialLobFetchSize?
2. Specify your connection string (mark confidential information with asterisks).
3. If you connect via Oracle Client, try using
Direct Mode. Or vice versa. Does this make any difference?
4. Give us the DDL script of your SNP_DATA table.