large resultset slow performance (fetch size?)

[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?

[Shalex]

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.

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.

[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;
            }
        }

[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.