Problem Description:

I’m experiencing a significant delay in data binding in C# when executing an Oracle query, despite the query itself being relatively fast in the SQL editor. The binding process takes over a minute, while the SQL query executes in about 25 seconds.

What I’ve Tried:

Connection Pooling: Implemented connection pooling but noticed no change in performance.
Command Timeout: Set command.CommandTimeout = 100 to ensure the command doesn’t timeout prematurely.

Questions:

Could the delay be due to the way data is being read from the OracleDataReader?
Is there a more efficient way to handle the data binding process in C# for large datasets from Oracle?
Are there any Oracle-specific optimizations or settings I might be missing?

Code:

`using (var connection = new OracleConnection(_connectionString))
{
    await connection.OpenAsync();
    Stopwatch stopwatch = Stopwatch.StartNew(); // Start the timer

    using (var command = new OracleCommand(@"
        SELECT       
            NVL(REGEXP_SUBSTR(code_number, '[^|]+', 1, 1), 'N/A') AS cd1,
            NVL(REGEXP_SUBSTR(code_number, '[^|]+', 1, 2), 'N/A') AS cd2,
            NVL(REGEXP_SUBSTR(code_number, '[^|]+', 1, 3), 'N/A') AS cd3,
            NVL(REGEXP_SUBSTR(code_number, '[^|]+', 1, 4), 'N/A') AS cd4,
            NVL(REGEXP_SUBSTR(code_number, '[^|]+', 1, 5), 'N/A') AS cd5,
            NVL(REGEXP_SUBSTR(code_number, '[^|]+', 1, 6), 'N/A') AS cd6,
            NVL(REGEXP_SUBSTR(code_number, '[^|]+', 1, 7), 'N/A') AS cd7
        FROM (
            SELECT
                SYS_CONNECT_BY_PATH(code, '|') AS code_number,
                SYS_CONNECT_BY_PATH(name, '|') AS name_value
            FROM tb_cs_hierarchy_detail
            WHERE hierarchy_id = :ID AND version_number = :v1
            START WITH parent_code = -1
            CONNECT BY PRIOR code = parent_code
        )
    ", connection))
    {
        command.Parameters.Add(new OracleParameter("ID", 2));
        command.Parameters.Add(new OracleParameter("v1", 2));
        command.CommandTimeout = 100; // Adjust timeout if needed

        using (var reader = await command.ExecuteReaderAsync())
        {
            var hidetails = new List<HDetail>(); // Initialize list
            var CI1 = reader.GetOrdinal("cd1");
            var CI2 = reader.GetOrdinal("cd2");
            var CI3 = reader.GetOrdinal("cd3");
            var CI4 = reader.GetOrdinal("cd4");
            var CI5 = reader.GetOrdinal("cd5");
            var CI6 = reader.GetOrdinal("cd6");
            var CI7 = reader.GetOrdinal("cd7");

            // Processing time is too much with below code          
            while (await reader.ReadAsync())
            {
                var obList = new HDetail
                {
                    cd1 = reader.GetString(CI1),
                    cd2 = reader.GetString(CI2),
                    cd3 = reader.GetString(CI3),
                    cd4 = reader.GetString(CI4),
                    cd5 = reader.GetString(CI5),
                    cd6 = reader.GetString(CI6),
                    cd7 = reader.GetString(CI7),
                };
                hidetails.Add(obList);
            }
        }
    }
    stopwatch.Stop(); // Stop the timer
    Console.WriteLine($"Query executed in: {stopwatch.ElapsedMilliseconds} ms");
}`