DataAdapter.Fill too slow

Question

I know DataAdapters have performance issues, but are there any ways around it that might be faster? At the moment, the DataAdapter.Fill method is taking 5-6 seconds on 3000 records, which is too slow for my app. If I remove the Fill line and just execute the SQL (using SQLCE), it takes 20 milliseconds, so I'm guessing the query isn't the problem. I've tried adding BeginLoadData on the datatable, but it makes no difference to the performance.

 using (SqlCeConnection con = new SqlCeConnection(conString))
 {
       con.Open();
       using (SqlCeDataAdapter dAdapter= new SqlCeDataAdapter())
       {

          using (SqlCeCommand com = new SqlCeCommand(query, con))
          {
               com.Parameters.Add("uname", textBox1.Text);
               dAdapter.SelectCommand = com;
               dAdapter.SelectCommand.Connection = con;

               DataTable dTable = new DataTable();


               dAdapter.Fill(dTable);

               dataGridView1.DataSource = dTable;


           }
       }
  }

Are there better ways to fill a DataGridView or speed up the Fill method?

Solution 1

You could bind the DataGridView to a DataReader instead, but it may not be much better, since loading 3000 rows into a DataGridView just isn't speedy .

Solution 2

the core problem is loading 3000 for the user at once. no matter how to load 300 records the amount of data is the problem. implement paging within the sql query to allow the user to view a subset of records. the user can then navigate to more records when they need to.

Comments

[Camron B]

Perhaps it is the connection speed that is a problem?

[MethodMan]

can you paste the code where you are declaring and populating the dTable..? also if you are doing things like Update or Deletes or Inserts use a DataReader instead .....much faster

[Skoder]

I've added the extra code and removed the BeginLoadData. I moved the database to my local machine to make sure it isn't a connection problem. @DJ Kraze, I though Updates, Deletes and Inserts were for DataAdapters mainly and DataReaders for readonly stuff?

[mj82]

What are the times if you comment out dataGridView1.DataSource = dTable; line and leave dAdapter.Fill(dTable); ? I suppose it's binding data do DGV may be slow, not filling the table.

[Skoder]

@mj82 - No, I thought it might be the DGV too, but the time is the same if I remove the DataSource. It is definitely the Fill method causing the delay.

[Skoder]

Are there better alternatives to a DGV that allows the user to edit data in an Excel-like fashion?

[Conrad Frix]

Well nothing for free. You could try Telerik RadGridView or DevExpress XtraGrid. Another option is to do some paging.

[MethodMan]

look at binding.. also I would change that DataTable personally and go with a Datareader.. also are you aware that you can bind datagrids to List<> as well may be even faster... just an FYI..

[Skoder]

@DJKraze - DataReader isn't much faster here.

[Skoder]

I get a "not supported exception". I'm using SqlCE, not SQL Server. Also, SqlCE doesn't support stored procs.

[Vlad Bezden]

I am sorry, I did not realized you are using SqlCE.

[Skoder]

No worries, I've learnt about UpdateBatchSize for future reference ;)

[Jason Meckley]

that and datareader requires an open and connected db connection. No sense keeping it open while binding to the UI. load the records into a DTO instead, dispose of the command and then bind to the UI.