Let us move on directly to the topic I will be discussing. Say we have a time-consuming SQL query with the following characteristics:
- The time of completion of execution is at T60 (e.g. 60 seconds)
- The time-to-first-record* is at T10 (e.g. 10 seconds)
- The SQL query is already fully optimized
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.
- Asynchronously buffer data from data store
- Stream data-pages to the client
- Cache the buffer in server-side
- Cache the data-pages on the client-side
- Paging of data will be used.
- One data-page contains 200 rows.
- The time-consuming SQL query returns 5,000 rows.
- DataTable will be used as a buffer.
- 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.
- GridView will be used to present the data-pages.
- ICallbackEventHandler will be implemented to facilitate AJAX calls.
- 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.
- Rather than executing the time-consuming query straight away against our data store, a background thread is created first from the ASPX handler.
- 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.
- The background thread starts reading rows and copies them into the buffer until the end is reached.
- 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.
- ASPX page is rendered with the GridView control displaying the first data-page.
- 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.
- 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).
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.