DataAdapter.Fill is too slow

Question

I know that DataAdapters have performance issues, but are there any ways that could be faster? Currently, the DataAdapter.Fill method takes 5-6 seconds per 3000 entries, which is too slow for my application. If I delete the Fill line and just execute SQL (using SQLCE), it takes 20 milliseconds, so I assume the query is not a problem. I tried adding BeginLoadData to the datatable, but that does not make any difference to 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 any better ways to populate a DataGridView or speed up the Fill method?

Answer 1

Instead, you can bind a DataGridView to a DataReader , but that might not be much better, since loading 3,000 rows into a DataGridView is simply not fast.

Answer 2

The main problem is loading 3000 for the user at the same time. no matter how to load 300 records, the amount of data is a problem. implement swap in the sql query to allow the user to view a subset of the records. the user can then move on to more records when they need to.

Answer 3

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

Here is an example of how to do this: BatchInsert