CODEWITHSUNDEEP
c#sqlprogress-bar
King Chan
King Chan

Reputation: 4302

How to indicate the current progress of querying a large dataset?

I want to use progress bar to show the current progress % the rows of data have been loaded. I see similiar question has been asked in SO, but the solution is just suggest using marquee style.

For example, I run a Select statement that query a large data table which might take few seconds. I want the progress bar update the current progress of the query. Is that possbile?

I know there is few things I need:

  1. A count of total records will be retrieving. (Can obtain by Select count(1) first)
  2. Some notificiation to tell how many records I just retrieved.

For 2. is the part I don't know how to implement.

I can think of split it into couples query, but this may not be possible all the time.

But how about something similiar to Cursor in SQL? Ask SQL Server to query a chunk and returns data and repeatly. 

for loop{
    //query a chunk of records

    //return 

    //Next
}

So I can caculate the progress and fill them into a single DataTable.

Is it possible to do something above? If so, how?

Because I seen an ASP.NET applicateion able to show the progress of loading a large data table, so I want to implement the same thing in WPF.

Upvotes: 1

Views: 4361

Answers (3)

gimbar
gimbar

Reputation: 135

If you query the count of you current dataset it will iterate through all rows. That's nearly as slow as just fetching it. You could try to select a count by sql statement. eg:"SELECT COUNT(ID_COLUMN) FROM TABLE WHERE ...". That's another sql statement you have to execute but faster as asking the dataset for it's count.
There is a C# progressbar control. You just need to set a maximum value and each time you iterate your loop you can call function step(). It will set the progressbar one step ahead ;) No need for you to do percentage calculation.

Upvotes: 1

oleksii
oleksii

Reputation: 35905

If you are loading the entire table in memory, you might doing it wrong. Does the user really need all that data? How many users will query the database at the same time? Are you sure the user will work with the whole table?

If you answered yes to all these question than you can consider using a pagination pattern: Skip + Take and you can rise a progress update event with the skip/take/total values.

//META CODE
var dataTable = new List<DataTable>(); //in-memory storage
int rowCount = [...].Rows.Count(); //this is fast check with database
int current;

while (current < rowCount)
{
  var fetchedData = [...].Skip(current).Take(1000).ToList();
  dataTable.AddRange(fetchedData);
  current = dataTable.Rows.Count();

  //fire progress update event
}

Upvotes: 2

zmbq
zmbq

Reputation: 39013

First, you need to be certain breaking the query into two isn't going to double your querying time. You may find yourself waiting a couple of seconds before you get the record count, and you haven't solved anything.

You could execute both queries in parallel (databases love concurrency and handle it very well), but you'll be putting an extra burden on the database, which could become a scalability issue in the future.

As for querying in chunks, I believe the SqlDataReader will allow you to fetch records as they become available, without first waiting for the entire query to complete. You'll have to add them to the DataTable yourself, though.

Upvotes: 1

Related Questions