Oracle connection from cf.net :: performance

Oracle connection from cf.net :: performance

Postby kresimir.zic » Mon 28 Dec 2009 21:20

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
kresimir.zic
 
Posts: 2
Joined: Mon 28 Dec 2009 21:10

Postby StanislavK » Tue 29 Dec 2009 13:09

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();
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Performance

Postby kresimir.zic » Tue 29 Dec 2009 15:24

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 ?
kresimir.zic
 
Posts: 2
Joined: Mon 28 Dec 2009 21:10

Postby StanislavK » Tue 29 Dec 2009 16:24

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/2008/02/12/asp-net-datareader-vs-dataset.aspx
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle