This is my first blog entry. I am starting this blog to document ideas and most of the time - how these ideas were implemented. This will serve as a note and reminder for me and hopefully it will help other people (such as I.T. professionals) like you. This article is the first of two or three parts.

Let us move on directly to the topic I will be discussing. Say we have a time-consuming SQL query with the following characteristics:

1. The time of completion of execution is at T60 (e.g. 60 seconds)
2. The time-to-first-record* is at T10 (e.g. 10 seconds)
3. The SQL query is already fully optimized 

*Note: for the purpose of this article, time-to-first-record is the point in time when the first row becomes available in the buffer for reading using SqlDataReader’s Read method.

The objective is to be able to respond to the web request as soon as possible by showing some data to the browser while a background thread continues to pull data from the data store. In other words, as soon as there are enough rows available for the first data-page, we can bind that data to the GridView and render it to the client while more data is retrieved and buffered on the background. This strategy is quite common in windows applications, but how can we achieve this in a web application? Here are four simple strategies we will use to achieve this.

1. Asynchronously buffer data from data store
2. Stream data-pages to the client
3. Cache the buffer in server-side
4. Cache the data-pages on the client-side 

Below are some assumptions before we move on.

1. Paging of data will be used.
2. One data-page contains 200 rows.
3. The time-consuming SQL query returns 5,000 rows.
4. DataTable will be used as a buffer.
5. SqlDataReader will be used to read rows from data store. This will allow us to read one row at a time while data comes in from the data store.
6. GridView will be used to present the data-pages.
7. ICallbackEventHandler will be implemented to facilitate AJAX calls. 

The illustration below should give you a better picture of how these strategies are implemented. This shows us the complete HTTP request-response flow in retrieving and presenting the first data-page from the SQL query.

1. An HTTP request to retrieve data is received from the web browser and is processed by the ASPX handler or ASPX page in the web server. At this point, we need to generate a cache ticket or id that will be unique for this first request.
2. Rather than executing the time-consuming query straight away against our data store, a background thread is created first from the ASPX handler.
3. The background thread opens a connection to the data store, executes the time-consuming SQL query and retrieves a SqlDataReader instance. Using the SqlDataReader, the background thread creates an empty DataTable with the same schema as the data that will be read from the data store. The buffer will be cached in memory for future reference using the cache ticket.
4. The background thread starts reading rows and copies them into the buffer until the end is reached.
5. While the background thread is copying rows to the buffer, the ASPX page is actually waiting for either of two things to happen, either a) the buffer has enough rows for one data-page or b) the background thread has completed filling the buffer. In any of these two conditions, the ASPX page will copy 200 or less number of rows and data-bind it to the GridView control.
6. ASPX page is rendered with the GridView control displaying the first data-page. 

How do we retrieve the other data-pages now? Simple, we will do the same way as we did for getting the first data-page with some few modifications. This time we will make the request through AJAX, pass the cache ticket and page number, and return the rendered GridView control in response to the AJAX call. Here is how our implementation would look like.

1. Succeeding request to get the next data-pages is made through AJAX call using the Callback framework of ASP.NET 2.0. The cache ticket and page number of the data-page being requested are transmitted with the AJAX call.
2. ASPX page handles the request and inspect the buffer if it has rows for the requested data-page with page number page number. Because the buffer is cached in memory, we are able to obtain a reference to the buffer using the cache ticket. Note that at this point in time, the background thread may still be populating our buffer or it might have already completed (as seen in the diagram above).
3. If there are enough rows for the requested data-page, the rows are then data-bind to the GridView control. GridView control is then rendered in memory using the RenderControl method. The resulting HTML is then transmitted to the web browser. The web browser then stores or caches this data-page (now represented in HTML) into a JavaScript array of string.

These steps repeats continuously until there is no data-page left that can be read from the buffer.

Here is the outcome of the process that implements our strategy. First, we were able to respond to the web request as soon as possible instead of waiting for the time-consuming SQL query to finish before showing data on the web browser. Secondly, we now have all the data-pages in the client-side stored in a JavaScript array of string. What this means is we can go from one data-page to another very quickly, seamlessly and it does not require us to make another request to the web server. Finally, we still have the DataTable acting as a buffer which is cached in the server-side. We can use the cached buffer to perform sorting instead of hitting again the data store, or we can use this to search within a search results.

One caveat - the implementation I just presented has a trade-off. We are actually allocating more memory to have a more responsive web application. This implementation allows web applications to respond to retrieval of data quicker and navigating though data-pages faster and more seamless. Another advantage of using this implementation is that it will work with any data store and we do not need to implement any paging on the database’s side (like using ROW_NUMBER() in SQL 2005). One way I can think of to minimize the impact of allocating more memory is to destroy the data-page in the server-side cache after it is transmitted to the client-side. Another way is to have a distributed caching strategy so that buffering will only be initiated in one server in a web server farm (more about this in my future posts).

In my next post, I will be showing you how this is actually implemented in ASP.NET and C#. I will also share with you one complete example that can be downloaded as a Visual Web Express 2008 solution. We will also explore a distributed caching strategy to efficiently implement this technique in a server farm.

I hope you found this post helpful. If you have questions, please feel free to email me or leave a comment and I will get back to you as soon as I can.

Labels: AJAX, ASP.NET, asynchronous, background, Buffer, Buffering, C#, Cache, Callbacks, data-pages, GridView, IDataReader, Paging, SqlDataReader, Streaming, threading, Visual Web Express 2008