How to minimize OleDbDataAdapter time to retrieve data from remote database
My Windows Form Application contains OleDbDataAdapter, it takes longer to retrieve data from remote DB. It is not capable of fetching / holding table data like 5000 rows (application gets hit). Here is my code.
environments = ConfigurationManager.GetSection("Environment") as NameValueCollection;
string strConnString = environments[envs];
conn = new OleDbConnection(strConnString);
conn.Open();
OleDbDataAdapter objDa = new OleDbDataAdapter("select * from tblABC", conn);
DataSet ds1 = new DataSet();
objDa.Fill(ds1);
dataGridView1.DataSource = ds1.Tables[0];
Environment section is configured in app.config file:
<configuration>
<configSections>
<section name ="Environment" type="System.Configuration.NameValueSectionHandler" />
</configSections>
<Environment>
<add key ="CIT" value ="Password=pwd123;User ID=abc123;Data Source=db1;Persist Security Info=True;Provider=MSDAORA"/>
<add key ="SIT" value ="Password=pwd234;User ID=abc234;Data Source=db2;Persist Security Info=True;Provider=MSDAORA"/>
<add key ="UAT" value ="Password=pwd345;User ID=abc345;Data Source=db3;Persist Security Info=True;Provider=MSDAORA"/>
</Environment>
</configuration>
source to share
Have you tried working with streams. create a subfunction somewhere in your program like below
public void dataPullingThread(){
try{
//your connection code goes here like below//
environments = ConfigurationManager.GetSection("Environment") as NameValueCollection;
string strConnString = environments[envs];
conn = new OleDbConnection(strConnString);
conn.Open();
OleDbDataAdapter objDa = new OleDbDataAdapter("select * from tblABC", conn);
DataSet ds1 = new DataSet();
objDa.Fill(ds1);
dataGridView1.DataSource = ds1.Tables[0];
conn.Close();
}
catch (Exception e){
}
}
//call your thread from desired location in program///
using System.Threading;
Thread thread = new Thread (new ThreadStart(dataPullingThread));
thread.start;
//Your application will continuously run; however, the data will appear when ever the thread auto kills itself. You can boost the speed if you create more then one thread. That means each thread selecting different rows of the database, I hope this information will help you//
source to share
Here are some common ADO.NET optimization tricks:
- Instead,
SELECT *
make sure you really want all the fields. The problem is that many unused field values can be retrieved and will consume resources.
For example, do SELECT Field1, Field2, Field3
instead SELECT *
if your table contains more than three of these fields.
- Stick to the following open / close connection pattern:
Example:
using(var con = new OleDbConnection(strConnString))
{
con.Open();
...
con.Close();
}
This way, the connection is closed even if wrong actions occur and the connection pooling mechanism will be used on the server side .
- The DbDataReader is much faster. Try using DbDataReader instead of DbDataAdapter. Use it to populate a shared list and then bind a DataGrid to that list.
However, it looks like there is something wrong with your connection. How can you be sure that the application is fetching data or trying to establish a connection? To test this, change your query to a very fast one such as "select sysdate from dual" to check if the problem occurs while trying to connect or not.
source to share