RadGrid

RadGrid and SqlDataSource control – Be Careful with your DataSource choice

Overview:

This blog post is in response to one of the customer queries we received a couple of days back. One of our India customer called us up and said that they are seeing slowness in couple of pages where they are using RadGrid. Problem statement was – when they click on “Add New Row” button it takes nearly 1.5 minutes for the row to appear on the grid. This made it even scary for them as they had moved the code to production. Through this blog post I will try to outline what I found out and what could have been done better to resolve the problem.

Analysis:

I went in to a call with the customer and took a look at their code. There were couple of things they were doing. Let me list them down:

  • DataSource for the RadGrid was a SqlDataSource control defined in the .ASPX page
  • They were handling OnItemCommand and OnItemDataBound. They had some logic in this method which would execute on every data bound and every item command
  • The new item template had lot of drop down lists and each of them were bound to SqlDataSource control as their data source.

Now, next thing I did was to take look at the timing. We used IE Developer Tools and captured the network traffic when Add New button was clicked. What I found out was, server was taking close to 10 seconds to finish its server side processing. That’s a lot of time if you ask me. What was surprising for them was, they had paging enabled on the grid. So they were thinking that paging is happening correctly but still why is the add new row scenario slow.

So this made me to delve little bit deeper into what exactly is happening in this scenario. So rest of this blog post is all about my observation. Read on if you have faced a similar problem.

Observations:

Did two experiments to figure out what was happening. First I use a RadGrid and bind it to SqlDataSource control and look at the query executed at the DB side. Second was to use entity data source instead of SQL data source and look at the query executed at the DB.

For my experiments I am using Northwind Database and in particular Order Details table. Order Details table contains nearly 2155 records. So this makes a good candidate to mimic a scenario where you are paging the RadGrid lets say 10 records per page.

I assume that you have RadControls for ASP.NET AJAX suite installed on your system. If not, you can always download from our products page – www.telerik.com/products/aspnet-ajax.aspx. You will need that if you want to follow along with this post. Also in order to trace the SQL Queries executed, I am making use of a open source tool called “Express Profiler” which is available at CodePlex here: http://expressprofiler.codeplex.com/. I had to use this as I have a SQL Server 2012 express edition and express edition does not come with Profiler option. You need a full blown SQL server to get the profiler.

I have created a RadControls Web Application for my experiments. When you install the RadControls for ASP.NET AJAX suite, you will get project templates in Visual Studio, using which you can create a web application.

SNAGHTML47a123f

Fig 1: RadControls Web Application Project Template

DataBinding with SqlDataSource control:

First experiment was placing a RadGrid and bind it to SqlDataSource control. I placed a RadGrid and set its basic properties.  Then added a SqlDataSource control to the page and set its connection string and provided a select command. Here is the code snippet for grid and SQL data source control:

 
    <telerik:RadGrid ID="RadGrid1" runat="server" 
                     DataSourceID="SqlDataSource1" 
                     AllowPaging="true" AllowSorting="true" 
                     AllowFilteringByColumn="true" AllowAutomaticInserts="True"
                     MasterTableView-EditMode="InPlace"  
                     MasterTableView-CommandItemDisplay="TopAndBottom" />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                       ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
                       ProviderName="System.Data.SqlClient" 
                       SelectCommand="SELECT * FROM [Order Details]" />

Analyzing the queries:

Now, before you run the application, lets get ready to trace the SQL queries that get executed. You should have downloaded the Express Profiler. Start the express profiler – Set the server details, select the authentication mechanism and finally select the events you want to trace. Here is the screen shot:

SNAGHTML4aaebbd

Fig 2: Express Profiler Settings

Run the web application. Notice that if we just set AllowPaging=true on RadGrid and do not provide a page size, by default RadGrid puts 10 as the PageSize. So in my case I should see only 10 rows on my first page of the RadGrid. Here is the Grid output:

image

Fig 3: RadGrid output

When I ran the app and looked at the trace, here is what I get:

SNAGHTML4af9b0f

Fig 4: SQL Query Trace for SQL Data Source Control bound to RadGrid

What you see here is a call to retrieve all the records from the table. You may be asking but the page size is set to 10. That is correct but what really happens is this: RadGrid will go ahead and ask SQL Data Source to get the data. SQL Data Source will blindly execute the query that has been set for the select command. RadGrid will not be able to do any tinkering with query because the SQL Data Source does not provide us a chance to put paging related statements.

Now lets do a page change on the grid. I will go ahead and click on Page 2.  RadGrid will now navigate to Page 2. Here is the trace that is generated for this:

SNAGHTML4c8b053

Fig 5: SQL Trace for Page Change Event

What you notice is, the SQL Data Source goes ahead and brings all the records from the table. RadGrid will then pick up only the second page rows and displays it.

Now lets go ahead and click on the Add New Row button and trace the query executed:

SNAGHTML4cdbf80

Fig 6: SQL Trace for Add New Record Event

As you can see it again retrieves all the records from the table.

What this tells us is the time taken is due to the fact that its going and getting all the table records no matter whatever is the situation we have. Now hitting DB and getting everything does call for some time although small but it till takes up time on the server.

Now lets do the same experiment but with Entity Data Source.

DataBinding with Entity Data Source:

I will use the same data grid but instead of SQL Data Source I will use a Entity Data Source. In order to work with Entity Data Source, you need a Entity Data Model. I will not get into how to create a Entity Data Model but assume that you know how to do it. Just create a ADO.NET Entity Data Model and generate the models from the database and use the same Northwind database. Once you have the ADO.NET Entity Data Model created, create a Entity Data Source on the page. Here is the Entity Data Source code snippet that I have:

 
<asp:EntityDataSource ID="EntityDataSource1" runat="server" 
                          OnContextCreating="EDS_ContextCreating" 
                          EntitySetName="Order_Details" />

I am making use of the OnContextCreating event to set the DbContext of ADO.NET Data Model to Entity Data Source ObjectContext. Here is the code for the event handler of OnContextCreating:

 
protected void EDS_ContextCreating(object sender, EntityDataSourceContextCreatingEventArgs e)
{
    var db = new NORTHWINDEntities();
    e.Context = (db as IObjectContextAdapter).ObjectContext;
}

Here NORTHWINDEntities is my Entity Data Model context. I am just casting it to ObjectContext since Entity Data Source works with ObjectContext. I set this entity data source as the data source of the RadGrid.

Analyzing the Queries:

Now lets run the app and trace SQL queries for the same set of actions we did with SQL Data Source control.

First, on page load:

SNAGHTML509e74c

Fig 7: Entity Data Source – Page Load – Query Trace

As you can see we have put in a “SELECT TOP 10” statement in the query. So its not bringing all 2155 records rather only 10 records. So time spent to do this will be very negligible. We also execute one additional query to get the total record count.

Now lets see what happens when we do a paging:

SNAGHTML50e4016

Fig 8: Entity Data Source – Paging – Query Trace

Now, lets trace the query when we do ‘’Add new record” scenario:

SNAGHTML50fd88d

Fig 9: Entity Data Source – Add New Record – Query Trace

Notice that, in this scenario, we automatically page to the last row and add a blank record.

As you can see from the above experiments, RadGrid is intelligent enough to optimize the queries based on the scenario you are dealing with.

Conclusion:

It was very evident from the experiments that if you are using SQL Data Source as your data source choice, there is not much RadGrid can do to optimize the query before it gets executed. Where as with Entity Data Source, RadGrid is smart enough to optimize the query and always have a optimized data fetch. I personally recommend using an ORM in the project for the data access scenarios and use the Entity Data Source as your data source choice. We i.e at Telerik have a free ORM called OpenAccess. Its free for life so you can pretty much use this in your production without any hassle at all. Do give it a try and let us know how you feel about it.

I hope this blog post does give you information on things you need to take care while doing data binding to Grids. This is just not in particular to RadGrid, this is mostly about any data binding scenario. Trace your queries and be sure that write and optimized queries are getting executed in your apps.

Till next time, Happy Coding.

Advertisements
RadHtmlChart

How to bind RadHtmlChart for ASP.NET AJAX to EntityDataSource control

Introduction:

This is the 3rd blog post in the series on RadHtmlChart, our HTML5 based charting control for ASP.NET AJAX and its data binding options. So far we have seen data binding the RadHtmlChart control with SQLDataSource and LinqDataSource. In this blog post we will look at one more data binding option namely binding to EntityDataSource.

In this blog post we will focus on how to create a EDM or entity Data Model using Entity Framework, then adding the EntityDataSource control to the web page and finally binding it to the RadHtmlChart. For the rest of this post it is assumed that you have already installed our RadControls for ASP.NET AJAX suite and also our free ORM called OpenAccess.

About EntityDataSource:

EntityDataSource is a control that was made available during the days of Visual Studio 2008 SP1 i.e. .NET 3.5. According to MSDN EntityDataSource control is defines as:

A control which can be used together with a data-bound control to retrieve data from an EDM and to display, edit, and sort data on a Web page by using little or no code.

I wont be spending too much time on discussing EntityDataSource control in this blog post. I suggest you to go through the overview article from MSDN available here: http://msdn.microsoft.com/en-us/library/cc488502.aspx. The article will get you up to speed with the EntityDataSource control.

Getting Started:

To start with, lets create a “C# RadControls Web Application”. Open a Visual Studio and navigate to File > New Project > Telerik > Web and select “C# RadControls Web Application”. Give it a name & click finish so that VS can go ahead and create the project for us.

SNAGHTML13df131b

Figure 1: New Project Dialog

Adding ADO.NET Entity Data Model:

Entity Data Source control requires a ADO.NET Entity Data Model to work with. Entity Data Source can be configured to work with a Entity Data Context for reading, creating, updating and deleting data with little or no code from the web page. In order to create a EDM, we need to first add Entity Framework bits to our project. Use the Nuget Package Manager to add a package named EntityFramework.

SNAGHTML1424992c

Figure 2: Nuget Package Manager

One of the best practice is to create a Models folder at the root of the web application. So lets create a folder called Models. Right click on the Models folder and select Add > New Item. In the new item dialog, under Data category select “ADO.NET Entity Data Model”, give it a name and click Add.

SNAGHTML142c24a9

Figure 3: Add New Item Dialog

Next we will be presented with a EDM Wizard. In the interest of the time, I will post my settings in the wizard as a serious of images below:

SNAGHTML142e40e0

Figure 4: Entity Data Model Contents

SNAGHTML142f47a3

Figure 5: Data Connection

SNAGHTML1430a6f2

Figure 6: Database objects

Once we finish with the wizard, NorthwindEntityDataModel.edmx is created in the Models folder. Double clicking that we will be presented with a visual designer with one object called Product placed. Remember we had selected only one Table for this demo called Product. Here is the screen shot of the EDMX designer:

image

Figure 7: EDMX Designer

Now that we have the EDM created, next is to add the Entity Data Source control to the page.

Adding Entity Data Source Control:

Open Default.aspx page and add EntityDataSource control to the page. You can either drag and drop the entity data source control from toolbar and you can write the declarative code by hand. I will drag and drop it from the toolbox onto the page. Use the Entity Data Source control smart tag (either from the ASPX page or from the designer) to configure data source settings. Here is my settings for the data source:

SNAGHTML144def19

Figure 8: Object Context Configuration

SNAGHTML144ee98e

Fig 9: Data Selection Configuration

When we finish the configuration wizard, following code will be generated by the Visual Studio

 
<asp:entitydatasource id="NorthwindEntityDataSource" runat="server" 
defaultcontainername="NORTHWINDEntities" connectionstring="name=NORTHWINDEntities" 
enableflattening="False" entitysetname="Products" 
entitytypefilter="Product" 
select="it.[ProductName], it.[UnitPrice]">
</asp:entitydatasource>

Note that we couldn’t add the top 10 keywords when configuring using the wizard and also there was no way to provide the orderby clause. In order to specify the top keyword just prepend “top(10)”  to the Select text. EntityDataSource supports OrderBy property. So add the property and provide the value “it.UnitPrice DESC”. Here is the final code:

 
<asp:EntityDataSource ID="NorthwindEntityDataSource" runat="server"
                DefaultContainerName="NORTHWINDEntities"
                ConnectionString="name=NORTHWINDEntities"
                EnableFlattening="False"
                EntitySetName="Products"
                EntityTypeFilter="Product"
                Select="top(10) it.[ProductName], it.[UnitPrice]"
                OrderBy="it.UnitPrice DESC" >
            </asp:EntityDataSource>

Add RadHtmlChart:

You can add a RadHtmlChart either by dragging and dropping from the toolbox onto the page or by writing the declarative code on the page. I will be dragging and dropping the control on to the page. Following 3 things need to be taken care while data binding to a EntityDataSource. They are:

  • DataSourceID: Set the DataSourceID property of the RadHtmlChart to the name of the Entity Data Source you want to bind to.
  • DataFieldY: Set the DataFieldY property of the data series to the column name from which the data points have to plotted on the Y axis. I am plotting the UnitPrice on the Y axis so I will set the DataFieldY property to “UnitPrice
  • DataLabelsfield: Set the DataFieldsLabel property of the XAxis to the column name from which we need the X axis data points to be plotted. I am having the products name on the XAxis so I will set the DataLabelsField property to “ProductName”.

Here is the complete code for the RadHtmlChart:

 
<telerik:RadHtmlChart ID="RadHtmlChart1" runat="server" 
                DataSourceID="NorthwindEntityDataSource" 
                Height="600px" Width="1000px">
                <ChartTitle Text="Top 10 Products By Price">
                    <Appearance>
                        <TextStyle FontSize="16px"></TextStyle>
                    </Appearance>
                </ChartTitle>
                <Legend>
                    <Appearance Position="Top">
                    </Appearance>
                </Legend>
                <PlotArea>
                    <Series>
                        <telerik:ColumnSeries Name="UnitPrice" DataFieldY="UnitPrice">
                        </telerik:ColumnSeries>
                    </Series>
                    <XAxis DataLabelsField="ProductName">
                        <LabelsAppearance RotationAngle="90" />
                        <MinorGridLines Visible="false" />
                        <MajorGridLines Visible="false" />
                    </XAxis>
                    <YAxis>
                        <TitleAppearance Text="Price" />
                        <MinorGridLines Visible="false" />
                    </YAxis>
                </PlotArea>
            </telerik:RadHtmlChart>

Here is the output of the chart:

image

Figure 10: RadHtmlChart Output

Summary:

In this post we looked at one more data binding technique with RadHtmlChart. We looked at EntityDataSource, how to configure entity data source and finally how to bind it with RadHtmlChart. With just 3 things to take care its very easy to bind the chart with entity data source. Hope this makes you excited to start working with RadHtmlChart. Do let us know your experience with the RadHtmlChart.

Till next time – Happy Coding !