I have received several feedback and comments from our blog reader, and it seems like there is certain factors that are not clearly explained with regards of performance topic that I wrote previously. In this post, I will try to answer several questions in more details.
#1 – Why not using homegrown table for best speed?
Unfortunately, there’s no remedy when it comes to binding large data table using basic technique. As I mentioned previously, a single .NET’s Fill method alone already takes 20 seconds to fill 350k data into the dataset. Not to mention the time to populate and dispatch each row into the UI, it will take at least 25 seconds until rendering.
If you have a large datatable, you can give it a spin using the following sample:
<asp:GridView ID=”GridView1″ runat=”server” AllowPaging=”True” AllowSorting=”True” AutoGenerateColumns=”False” DataKeyNames=”ID” DataSourceID=”SqlDataSource1″>
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:LargeDataConnectionString2 %>“></asp:SqlDataSource>
If the above sample can be bound to 350k data in less than 20 seconds, I’m interested to learn further.
#2 – I guess we can use SqlDataSource with DataReader mode and bind it to GridView.
Unfortunately, DataReader mode in SqlDataSource won’t work with Paging and the rest of databound features. So, SqlDataSource is totally unviable.
#3 – How about ObjectDataSource, or other built-in datasource controls that come with ASP.NET?
ObjectDataSource does support paging, but it falls into the same bottleneck as it used DataAdapter to fetch the results into a DataSet. The pagination is done at server-side control level and not at database level, and hence, it will result into the same 20 seconds performance hit.
So at this time, none of the built-in datasource controls (or the GridView) can bound to a large data table efficiently.
#4 – Isn’t it inefficient to bound to such a large table? I think in real world scenarios, you should have a predefined filter for data fetching.
Good shot. Although our Grid supports large table binding, we often recommend customers to load and retrieve data efficiently by either using predefined filter or asked user to enter some search criteria before requesting data. Best design & pattern in application architecture is always our first priority and suggestion.
However, the large table binding is unavoidable in several enterprise scenarios. For a quick real-world scenario, administrator often need to browse a table of their Sql database via Web in order to maintain the data records (or perhaps updating records). As business growth, the table might contain more records. In this situation, if the deployed data grid is not able to display the data in acceptable timing, the administrator will waste his entire day in waiting the Web page to response.
For us, it’s extremely important to support such enterprise scenarios (as we utilized it in our own internal application as well).
Performance is always a hot topic in software industry, especially in reusable component industry as it becomes foundation and backbone of many enterprises Web application. Some products are good in styles, but not performance. Some are good in performance, but not styles.
WebGrid.NET Enterprise is striving to deliver both the best user experience and styles, and performance.
In addition to the measurement factors that I used in my initial post, there are several more important points that worth mentioned.
- Database agnostics. Unlike competing products, WebGrid supports large data binding to any kind of database through ISDataSource control. You can bind to Sql Server, Access, Oracle and even your own custom object collection.
- Bare-metal architecture and elegant binding approach. Many developers end up with “tricky workaround” to achieve faster results. While this may work, it is not quite reusable in other pages, as many codes are involved in the page’s code behind. ISDataSource embraces an elegant and professional approach, which works consistently with the entire reusability concept that it introduced.
Surprisingly, the sample that I mentioned in the previous post doesn’t contain any single line of codes related to the databinding or data retrieval in the page’s code behind. The Webform is completely declarative, while the actual codes are done in GetData method, which is defined at ISDataSource level (and works consistently with datasource control architecture that introduced in ASP.NET 2.0). This allows you to create as many pages as you like, and simply refer to the desired data object without the need for extra coding. It’s that simple.
Next, we will make available an in-depth knowledge base article that explains the technique we used in the sample. It’s unfortunate that we can’t ship the sample in our product installer due to the size of the large database sample (which is around 500MB in size).
I hope this post satisfies your hunger in knowing and learning more about some performance topics in our products. Enjoy, and have a nice day 🙂