Oracle connection from cf.net :: performance

[kresimir.zic]

Hi folks,

I performed some tests in reading data using OracleDataTable and OracleDataReader... According to my test, OracleDataReader is much faster than OracleDataTable. I use following code:

OracleCommand myCommand = new OracleCommand("select ...");
OracleDataTable myDataTable = new OracleDataTable();
myDataTable.Connection = conn2oracle;
myDataTable.SelectCommand = myCommand;
myDataTable.FetchAll = true;

My question:
1.) What is the faster way to read data ? Since I use GPRS this issue is very important.
2.) Does the OracleDataTable opens the connection every time I use it? How to use it on connection which is already open?

Thanks,

Kresimir

[StanislavK]

1) Generally, DataReader is faster than DataTable. DataReader caches one row at a moment, while DataTable gets the whole record set at once.

2) If OracleDataTable.Connection is already opened, the methods fetching the data to OracleDataTable use it immediately. Otherwise, at first the connection is opened, and then the data is fetched.

For example, you can use the opened connection as follows:

Code: Select all

conn2oracle.Open();
myDataTable.Connection = conn2oracle;
myDataTable.Fill();

[kresimir.zic]

Does it apply if I have to read all records returned by a query?

Using OracleDataReader I have to read the results record by record, and using OracleDataTable I get all results from the query immediately.

What are your experiences ?

[StanislavK]

Using DataReader, you do not need to store all the previously obtained data in memory, in contrast to DataTable. It is much faster when you just get records from the database, but may be inconvenient when you need to somehow process them.

For more information on this, please see the Microsoft help:
http://msdn.microsoft.com/en-us/library/haa3afyz.aspx
http://weblogs.asp.net/joelevi/archive/ ... taset.aspx