How to minimise OleDbDataAdapter time to fetch data from remote database

Question

My Windows form Application contains OleDbDataAdapter, it is consuming longer time to fetch data from remote DB. It is not able to retrieve/Hold table data like 5000 rows(Application gets struck). 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>

It would be greatful if someone could suggest better approach/mechanism with code.

Answer 1

Did you try to work with threads. create a sub function 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//

Answer 2

Here are some general ADO.NET optimization tricks:

• Instead of doing SELECT *, please ensure that you really need all the fields. The problem is that many unused field values might be retrieved and it consume resources.

For example, do SELECT Field1, Field2, Field3 instead of SELECT * if your table contains more than those three fields.

• Stick to the following connection open/close pattern:

Example:

using(var con = new OleDbConnection(strConnString))
{
    con.Open();

    ...

    con.Close();
}

So the connection is closed even if wrong things happens, and the connection pooling mechanism will be used on the server side.

• The DbDataReader object is much faster. Please try to use a DbDataReader instead of a DbDataAdapter. Use it to fill a generic List, then bind your DataGrid to that List.

However, it looks like something is wrong with your connection itself. How can you be sure the application is fetching data or trying to establish a connection ? To check this, change your query to a very fast one like "select sysdate from dual" to check whether the problem is coming from a connection attempt or not.