Hi David,
Jeffery and I discussed your questions and we agreed that we have not yet
provided answers to all your questions. We apologize.
Reviewing the thread, we found that you asked four questions:
1) How to speed up the data binding of a DataGrid control?
2) How to reduce the size of the rendered output?
3) How to speed up custom paging?
4) How to reduce the redundancy of style text in the datagrid's output?
My answer to #1 is long, so I'll leave it till last.
***** #4 too many style tags
I suspect that this is related to having lots of controls in the grid. Each
control outputs its own style tags. It may help you to use Cascading Style
Sheet (CSS) classes with each control's CssClass property. For more
information, please see:
* WebControl.CssClass
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemwebuiwebcontro
lswebcontrolclasscssclasstopic.asp
* First browse "Server-side ASP .NET Data Binding, Part 2: Customizing the
DataGrid Control"
http://msdn.microsoft.com/msdnmag/issues/01/04/cutting/default.aspx
Then click on the link to "Figure 5".
* QuickStart Tutorials - Applying Styles to Controls
http://samples.gotdotnet.com/quickstart/aspplus/doc/webtemplates.aspx
You may want to check out the rest of
www.gotdotnet.com as well as
www.asp.net.
***** #3 custom paging
You can end up with a data source that contains 10 records because the
database has exactly 10 records. You can also end up with 10 records
because you performed extra work in your query to only select 10 records.
Either way, you have a data source with 10 records.
After you prepare this data source, then you run data binding. At this
point, any extra work involved in selecting the 10 records has already been
completed. The data grid's processing of those records will be the same. It
just binds to 10 records.
Therefore, speeding up custom paging means speeding up how you gather each
page of data. You need to optimize your queries.
I do not have a direct answer on how you can optimize your particular query
since you did not post any details about your query. You may want to post
this particular question to a database newsgroup such as
microsoft.public.dotnet.framework.adonet .
Here is one idea.
With custom paging you need to filter the recordset down to the precise
rows before binding it to the data grid. One approach uses nested SQL
SELECT statements to first filter and then sort the data. This method has
some assumptions including that the data can be sorted and that the sort
columns, taken together, do not have any rows with duplicate values.
Here's a sample nested query that uses the products table in the Northwind
sample database.
select * from (select top 7 productname, quantityperunit from products
where productid < @ProdID order by productid DESC) as inner order by
productid
This will display product information sorted by productid ascending. Since
productid does not have duplicate values, we can use it to filter the query
down to just 7 records. Our inner query reverses the sort order, uses a
specific productid that we have supplied, and gets the next 7 based on the
reverse sort order. Then the outer query puts the sort order back to
ascending for display.
***** #2 rendered output size
First, I'm curious about what data you are displaying in the datagrid. You
wrote that you are already using paging to display a fairly small number of
rows, yet your total page size is very large. Perhaps each row involves a
large amount of data.
I don't have a direct answer. Here are some ideas.
Please turn off ViewState for the entire page. Then browse the page and
check the total page size. This will help show if we need to focus on the
ViewState.
Please set your paging so that it only shows one row. Then set it again so
that is shows two rows. Compare the total page size for both of these to
get an approximation of how much is being sent to the browser for each row.
Please remove the datagrid from your page and browse it. Perhaps there are
other elements on the page that are very large.
***** #1 binding speed
My main suggestions are to experiment with reducing the number of controls
and to avoid using DataBinder.Eval. The following is taken from my post at
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=GWUW0fl#CHA.
1532%40cpmsftngxa08.phx.gbl.
1)
Can you determine how much time it takes to run the query vs the time it
takes to create the grid? You could test this by filling the DataSet or
creating the DataReader without assigning it to the DataGrid. If the query
is taking too long, then we need to look at your database or query.
2)
Objects take time to create. If you can reduce the number of controls in
your grid, that will improve performance. However, we don't know yet if the
poor performance is directly due to the overhead of creating lots of
controls.
You can partially verify this by using the same data to fill a DataGrid
with no controls. Keep your column templates and the data assignments, such
as: <%# DataBinder.Eval(Container.DataItem, "au_id") %>. The results will
be ugly, but all the data should be present.
Your templates might look something like this:
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "au_id") %>
<%# DataBinder.Eval(Container.DataItem, "au_fname") %>
<%# DataBinder.Eval(Container.DataItem, "au_lname") %>
</ItemTemplate>
If removing the controls solves your performance problem, then look for
ways to reduce the number of controls.
3)
Replace DataBinder.Eval with direct usage of the container object.
OLD: DataBinder.Eval(Container.DataItem, "au_fname")
NEW: Container.DataItem("au_fname")
---
This third idea needs a little more explanation. The Eval function has a
third parameter for formatting which is missing when you use the container
object directly. Putting the formatting back is a problem.
Here are examples for data of type Decimal and Date.
First, with no formatting:
<%# Container.DataItem("price2") %>
<%# Container.DataItem("pubdate")%>
Second, with formatting:
<%# cType(Container.DataItem("price2"),
decimal).ToString("$#,##0.00;($#,##0.00);Zero") %>
<%# Container.DataItem("pubdate").ToShortDateString %>
With no formatting, everything works fine (based on my limited testing).
However, with formatting, a run-time error occurs if the field has a null
value.
When fields may contain nulls and you need formatting, you may find it's
better to use the Eval method while using the container object directly in
all other cases.
If this still leaves you with using Eval in many places, try an experiment
of replacing all these with directly using the container object with no
formatting. This will have ugly output, but it's just an experiment. If
that significantly helps performance, then you may want to run through the
data prior to binding to replace all the nulls. Then you can use the
container object with formatting.
The following code sample uses the container object directly with and
without formatting. It also runs through the data prior to binding in order
to replace nulls. Replacing null dates does not work well. If the field is
of type Date, then you cannot assign it to "blank". Either it is a real
date or it is null (which causes the run-time error). Since I want null
dates to appear as blank in my output, I added an extra text column to my
data and placed the formatted date into that field.
I suspect you will find that running through the data to replace nulls is
slower than using Eval. So, I expect that you will use the container object
directly where possible, while using Eval where required.
This sample includes two integer columns, two decimal columns, and three
date columns to show the difference in output between the UNformatted
columns that allow nulls vs the formatted columns that do not allow nulls.
You will probably need to change the connection string.
SAMPLE -- HTML for the DataGrid
<asp
ataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:TemplateColumn HeaderText="Text">
<ItemTemplate>
<%# Container.DataItem("au_lname") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Boolean">
<ItemTemplate>
<%# Container.DataItem("contract") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Decimal">
<ItemTemplate>
<%# Container.DataItem("price1") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Formatted Decimal">
<ItemTemplate>
<%# cType(Container.DataItem("price2"),
decimal).ToString("$#,##0.00;($#,##0.00);''") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Integer">
<ItemTemplate>
<%# Container.DataItem("royalty1") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Formatted Integer">
<ItemTemplate>
<%#
cType(Container.DataItem("royalty2"),integer).ToString("#,##0'%';(#,##0'%');
''") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Date">
<ItemTemplate>
<%# Container.DataItem("pubdate") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Formatted Date">
<ItemTemplate>
<%# Container.DataItem("pubdate2").ToShortDateString %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Pre-formatted Date">
<ItemTemplate>
<%# Container.DataItem("pubdate3") %>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp
ataGrid>
SAMPLE -- VB Page_Load for the code-behind
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim queryString As String = "SELECT au_lname, contract, price as
price1, price as price2, royalty as royalty1, royalty as royalty2, pubdate
FROM authors left outer join titleauthor on authors.au_id =
titleauthor.au_id left outer join titles on titleauthor.title_id =
titles.title_id order by au_lname"
Dim connectionString As String = "server='localhost';
trusted_connection=true; Database='pubs'"
Dim sqlAdapter As System.Data.SqlClient.SqlDataAdapter = New
System.Data.SqlClient.SqlDataAdapter(queryString, connectionString)
Dim ds As DataSet = New DataSet()
sqlAdapter.Fill(ds, "MyData")
Dim col As New DataColumn("pubdate2",
System.Type.GetType("System.DateTime"))
ds.Tables("MyData").Columns.Add(col)
col = New DataColumn("pubdate3", System.Type.GetType("System.String"))
ds.Tables("MyData").Columns.Add(col)
Dim dr As DataRow
For Each dr In ds.Tables("MyData").Rows
If dr("Price2") Is System.DBNull.Value Then dr("Price2") = 0
If dr("royalty2") Is System.DBNull.Value Then dr("royalty2") = 0
If dr("pubdate") Is System.DBNull.Value Then
dr("pubdate2") = Date.MinValue
dr("pubdate3") = ""
Else
dr("pubdate2") = dr("pubdate")
dr("pubdate3") = dr("pubdate").ToShortDateString
End If
Next
DataGrid1.DataSource = ds.Tables("MyData")
DataGrid1.DataBind()
ds.Dispose()
sqlAdapter.Dispose()
End If
End Sub
-------
I hope the above helps to answer your questions.
Thank you, Mike
Microsoft, ASP.NET Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer's security.
This posting is provided "AS IS", with no warranties, and confers no rights.
