4

I tried to run a query asynchronously in .NET so that I can read the first few rows while the others are still being transmitted, but I had no luck so far.

The reason I want to to this is that we often need to fetch large tables from the database and while it is possible to not freeze the UI completely with asnyc variants of ExecuteReader() it doesn't seem to be possible to fetch data row by row so that the user can see the progress and maybe even work with the first portion of data.

What I tried is the following query:

SELECT 'Hello '
WAITFOR DELAY '0:0:10'
SELECT 'World!'

When I run this query with SqlCommand.BeginExecuteReader(callback) the callback function is called after approximately 10 seconds so it obviously waited for the whole query to finish. I also tried SqlCommand.ExecuteReaderAsync() with the same results.

My question is the following: Is this even possible in .NET? Or doesn't it work because of my query and would work better if tested with a real query (i.e. large table)?

Karsten
  • 1,814
  • 2
  • 17
  • 32
  • Have you tried adding [TOP](http://www.w3schools.com/sql/sql_top.asp) then using [OFFSET](http://stackoverflow.com/questions/187998/row-offset-in-sql-server) such as `SELECT TOP 10 * FROM table` then `SELECT * FROM table OFFSET 10 LIMIT 50` – lloyd Mar 29 '15 at 00:19
  • @llodym I know about this option. The disadvantage apart from being totally dbms dependent is that i would have to execute the query multiple times. This would be very inefficient and problems might arise when the database is modified between queries. – Karsten Mar 29 '15 at 00:27
  • 1
    It's possible that you would just need to execute it just 2 times. Modifying the second call to `SELECT * FROM table OFFSET 10` or forget the offset and compare the first 10 to check if they changed. – lloyd Mar 29 '15 at 00:38
  • 1
    @lloydm: Sorry, I misunderstood your original comment. That is indeed an interesting aproach in this case. It may solve this particular problem but does not answer the question if this would be possible. – Karsten Mar 29 '15 at 00:47
  • Unfortunately I don't have a answer for if this is possible. You could pose the questions to experts such as the [MVA](http://www.microsoftvirtualacademy.com/liveevents/developing-solutions-with-azure-documentdb) Azure DocumentDB event on April 7, 2015. – lloyd Mar 29 '15 at 01:12
  • is it really a huge table and do you really wish to display few thousand records or do you have a slow running procedure which displays a few records after a minute? – A ツ Mar 29 '15 at 14:14

2 Answers2

2

Get the first 10 rows in the table.

SELECT TOP 10 * FROM table ORDER BY ID DESC

Then run a second query. 

SELECT * FROM table ORDER BY ID DESC

Do a check if the first 10 changed ( Create, Update, Delete )

Update: Alternatively adding TOP then using OFFSET such as 

SELECT TOP 10 * FROM table

then 

SELECT * FROM table OFFSET 10 LIMIT 50

or pre SQL Server 2012 see Dave Ballantyne's solution

lloyd
  • 1,683
  • 2
  • 19
  • 23
  • 1
    I looked through the source code of `BeginExecuteReader` with Reflector and found that it is internally implemented with Task continuation. Therefore the callback is only called when the whole operation finished. That forces me to do something similar to your approach, but with `ROW_NUMBER` as described [here](http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/04/26/offset-without-offset.aspx) to stay compatible with SQL Server 2005. – Karsten Mar 29 '15 at 09:42
  • Interesting, I'll update the answer to include a pre SQL Server 2012 version. There's some general [optimization for data access](http://www.codeproject.com/Articles/35665/Top-steps-to-optimize-data-access-in-SQL-Serv) you might want to consider. – lloyd Mar 29 '15 at 10:02
0

Not sure whether you want to use, in web application or in windows

If web, you can try this

  • On page load we can load a blank control
  • then we can fetch page by page records say 10
  • Append the fetched records to the control

Hope it will help you

Ali Adravi
  • 21,707
  • 9
  • 87
  • 85