4

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?

Skoder
  • 3,983
  • 11
  • 46
  • 73
  • Perhaps it is the connection speed that is a problem? – Camron B Jan 04 '12 at 17:34
  • 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 – MethodMan Jan 04 '12 at 17:34
  • 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? – Skoder Jan 04 '12 at 17:42
  • 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. – mj82 Jan 04 '12 at 18:43
  • @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 Jan 04 '12 at 18:47

3 Answers3

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 .

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Are there better alternatives to a DGV that allows the user to edit data in an Excel-like fashion? – Skoder Jan 04 '12 at 17:42
  • 1
    Well nothing for free. You could try Telerik RadGridView or DevExpress XtraGrid. Another option is to do some paging. – Conrad Frix Jan 04 '12 at 17:47
  • 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.. – MethodMan Jan 04 '12 at 17:48
  • @DJKraze - DataReader isn't much faster here. – Skoder Jan 04 '12 at 17:50
  • 1
    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. – Jason Meckley Jan 04 '12 at 18:04
1

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.

Jason Meckley
  • 7,589
  • 1
  • 24
  • 45
0

Use BatchUpdate/BatchInsert. Make sure you specify UpdateBatchSize = 3000 (number of records you have)

Here is an example on how to do it: BatchInsert

Vlad Bezden
  • 83,883
  • 25
  • 248
  • 179