Recently I faced with a performance complaint for a query. Query was simple as it could be, just a "select * from some_table". When I digged out the problem with my fellow developer, we discovered that almost all time was spent while the data adapter fills the data table. Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill data table.

The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from database. Here is how it's impletemented:

using (OracleConnection conn = new OracleConnection())
{
     OracleCommand comm = new OracleCommand();
     comm.Connection = conn;
     comm.FetchSize = comm.FetchSize * 8;
     comm.CommandText = "select * from some_table";

      try
     {
          conn.Open();
          OracleDataAdapter adap = new OracleDataAdapter(comm);
          System.Data.DataTable dt = new System.Data.DataTable();
          adap.Fill(dt);
     }
     finally
     {
          conn.Close();
     }
}

Notice the line with blue font, fetch size of command object increased by 8 times its default which is 128 KB at ODP.Net 11.2.0.3. OracleDataReader also has the FetchSize property. By increasing fetch size, you increase the cache size in memory to fetch rows.

What we gained is up to 96% performance improvement. Here are some timings with different fetch sizes:

Fetch SizeTiming (MI:SS.FF3)
Default (128 KB)
05:20.290
Default x 8 (1 MB)
00:52.941
Default x 32 (4 MB)
00:26.008
Default x 64 (8 MB)
00:12.409

It's a easy way to improve your application's performance, isn't it.