CODEWITHSUNDEEP
c#oracle-database
jre247
jre247

Reputation: 1887

Oracle Data Access for .NET

I have an MVC3 ASP.NET web application that uses Oracle Data Provider for .NET (ODP.NET) x86 to connect to an Oracle database. The below code within my app will execute a query in an Oracle database table, and then write the query results to an output file using StreamWriter.

This below code reads about 12 thousand records per minute from an Oracle database table (even without using streamwriter to write to an output file).

I'm wondering if any of you know of a way to have improved performance to read more records per minute - perhaps another driver that you know of? Thanks, I really appreciate it!

        _dbCommand.CommandText = query;

        using (var sw = new StreamWriter(output, append, Encoding.UTF8))
        {
            using (var reader = _dbCommand.ExecuteReader())
            {
                if (reader != null)
                {
                    var count = reader.FieldCount;

                    while (reader.Read())
                    {
                        //get records
                        for (var i = 0; i < count; i++)
                        {
                            var txt = reader[i].ToString();

                            sw.Write(txt);
                        }
                        sw.WriteLine();

                    }
                }
            }

UPDATE:

It looks like the bottleneck is with ODP.NET. It takes 53 seconds to read/iterate over a "select top 10k" type query from any table in my Oracle database.

_dbCommand.CommandText = query;
using (var reader = _dbCommand.ExecuteReader())
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {

                    }
                }
            }

Upvotes: 1

Views: 1493

Answers (1)

Ray Cheng
Ray Cheng

Reputation: 12576

If you want the most performance, then I suggest you skip the c# part completely.

  1. run a explain plan on your query to tune its performance.
  2. use Oracle UTL_FILE to write the data to a file.

UPDATE

First off, you still need to tune your SQL query to make your query run as fast as possible.

Then, the question is how to get the data to a file quickly. Your sw.Write will not be the fastest because it writes too little data too often.

Try setting its AutoFlush property to false and call Flush manually to write to disk.

You'll need to test different buffer size to find out which works the best.

Upvotes: 2

Related Questions