This is a migrated thread and some comments may be shown as answers.

Poor performance for large number of records

3 Answers 144 Views
Grid
This is a migrated thread and some comments may be shown as answers.
M. R.
Top achievements
Rank 1
M. R. asked on 28 Jun 2012, 02:51 PM
Hello,

Using Telerik.Web.UI_2011_3_1305
I have a SQL Server table with approx 600,000 records and 28 columns. I created an ASP.Net page with a RadGrid set-up in "Grid / Virtual Scrolling and Paging" as described in the article at:  
http://demos.telerik.com/aspnet-ajax/grid/examples/client/virtualscrollpaging/defaultcs.aspx
(see code below)
However the performance is poor: loading a new grid page takes a very long time, approx 30 seconds...

Please also note the following:
- the SQL Server database is hosted on the same machine
- the development machine is very fast and has 6GB of RAM
Therefore it is not the environment that causes the performance issue.

Please advise.
Regards,
M.R.

<telerik:RadGrid ID="RadGrid1" runat="server" CellSpacing="0" DataSourceID="SqlDataSource1"
   GridLines="None" Width="916px" AllowPaging="True" VirtualItemCount="5000" PageSize="10">
   <ClientSettings>
     <Scrolling AllowScroll="True" EnableVirtualScrollPaging="True" UseStaticHeaders="True"
       SaveScrollPosition="True"></Scrolling>
   </ClientSettings>
   <MasterTableView AutoGenerateColumns="False" DataKeyNames="SBACTN" DataSourceID="SqlDataSource1"
     AllowCustomPaging="False" AllowPaging="True" AllowSorting="True" VirtualItemCount="5000"
     AlternatingItemStyle-ForeColor="#CC00CC">
     <CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>
     <RowIndicatorColumn Visible="True" FilterControlAltText="Filter RowIndicator column">
       <HeaderStyle Width="20px"></HeaderStyle>
     </RowIndicatorColumn>
     <ExpandCollapseColumn Visible="True" FilterControlAltText="Filter ExpandColumn column">
       <HeaderStyle Width="20px"></HeaderStyle>
     </ExpandCollapseColumn>
     <Columns>
       <telerik:GridBoundColumn DataField="SBSACT" FilterControlAltText="Filter SBSACT column"
         HeaderText="SBSACT" SortExpression="SBSACT" UniqueName="SBSACT">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBACTN" FilterControlAltText="Filter SBACTN column"
         HeaderText="SBACTN" ReadOnly="True" SortExpression="SBACTN" UniqueName="SBACTN">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBNAM" FilterControlAltText="Filter SBNAM column"
         HeaderText="SBNAM" SortExpression="SBNAM" UniqueName="SBNAM">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBSTAT" FilterControlAltText="Filter SBSTAT column"
         HeaderText="SBSTAT" SortExpression="SBSTAT" UniqueName="SBSTAT" ReadOnly="False">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBCTY2" FilterControlAltText="Filter SBCTY2 column"
         HeaderText="SBCTY2" SortExpression="SBCTY2" UniqueName="SBCTY2">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBCTY1" FilterControlAltText="Filter SBCTY1 column"
         HeaderText="SBCTY1" SortExpression="SBCTY1" UniqueName="SBCTY1">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBGRID" FilterControlAltText="Filter SBGRID column"
         HeaderText="SBGRID" SortExpression="SBGRID" UniqueName="SBGRID">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBCNCL" FilterControlAltText="Filter SBCNCL column"
         HeaderText="SBCNCL" SortExpression="SBCNCL" UniqueName="SBCNCL">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBI2NO" FilterControlAltText="Filter SBI2NO column"
         HeaderText="SBI2NO" SortExpression="SBI2NO" UniqueName="SBI2NO">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBDMS1" FilterControlAltText="Filter SBDMS1 column"
         HeaderText="SBDMS1" SortExpression="SBDMS1" UniqueName="SBDMS1">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBDMS2" FilterControlAltText="Filter SBDMS2 column"
         HeaderText="SBDMS2" SortExpression="SBDMS2" UniqueName="SBDMS2">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBDTIN" DataType="System.DateTime" FilterControlAltText="Filter SBDTIN column"
         HeaderText="SBDTIN" SortExpression="SBDTIN" UniqueName="SBDTIN">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBDTCN" DataType="System.DateTime" FilterControlAltText="Filter SBDTCN column"
         HeaderText="SBDTCN" SortExpression="SBDTCN" UniqueName="SBDTCN">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBDTST" DataType="System.DateTime" FilterControlAltText="Filter SBDTST column"
         HeaderText="SBDTST" SortExpression="SBDTST" UniqueName="SBDTST">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBLANG" FilterControlAltText="Filter SBLANG column"
         HeaderText="SBLANG" SortExpression="SBLANG" UniqueName="SBLANG">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBCONT" FilterControlAltText="Filter SBCONT column"
         HeaderText="SBCONT" SortExpression="SBCONT" UniqueName="SBCONT">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBADDR" FilterControlAltText="Filter SBADDR column"
         HeaderText="SBADDR" SortExpression="SBADDR" UniqueName="SBADDR">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBSTR" FilterControlAltText="Filter SBSTR column"
         HeaderText="SBSTR" SortExpression="SBSTR" UniqueName="SBSTR">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBCTY" FilterControlAltText="Filter SBCTY column"
         HeaderText="SBCTY" SortExpression="SBCTY" UniqueName="SBCTY">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBPROV" FilterControlAltText="Filter SBPROV column"
         HeaderText="SBPROV" SortExpression="SBPROV" UniqueName="SBPROV">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBPOST" FilterControlAltText="Filter SBPOST column"
         HeaderText="SBPOST" SortExpression="SBPOST" UniqueName="SBPOST">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBTEL" FilterControlAltText="Filter SBTEL column"
         HeaderText="SBTEL" SortExpression="SBTEL" UniqueName="SBTEL">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBDTSG" DataType="System.DateTime" FilterControlAltText="Filter SBDTSG column"
         HeaderText="SBDTSG" SortExpression="SBDTSG" UniqueName="SBDTSG">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBFMS7" FilterControlAltText="Filter SBFMS7 column"
         HeaderText="SBFMS7" SortExpression="SBFMS7" UniqueName="SBFMS7">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBFMS1" FilterControlAltText="Filter SBFMS1 column"
         HeaderText="SBFMS1" SortExpression="SBFMS1" UniqueName="SBFMS1">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBCOMP" FilterControlAltText="Filter SBCOMP column"
         HeaderText="SBCOMP" SortExpression="SBCOMP" UniqueName="SBCOMP">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBFMS5" FilterControlAltText="Filter SBFMS5 column"
         HeaderText="SBFMS5" SortExpression="SBFMS5" UniqueName="SBFMS5">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn DataField="SBSIC" FilterControlAltText="Filter SBSIC column"
         HeaderText="SBSIC" SortExpression="SBSIC" UniqueName="SBSIC">
       </telerik:GridBoundColumn>
     </Columns>
     <EditFormSettings>
       <EditColumn FilterControlAltText="Filter EditCommandColumn column">
       </EditColumn>
     </EditFormSettings>
   </MasterTableView>
   <FilterMenu EnableImageSprites="False">
   </FilterMenu>
 </telerik:RadGrid>

3 Answers, 1 is accepted

Sort by
0
M. R.
Top achievements
Rank 1
answered on 11 Jul 2012, 02:03 PM
Just an update:

I created a test project using the LightSwitch technology and noticed that Microsoft grid is extremely fast, it takes 1 second to navigate to the next page compared to 28 seconds when using RadGrid...

Telerik support, can you please advise?
0
Accepted
Andrey
Telerik team
answered on 16 Jul 2012, 07:18 AM
Hello,

When you have many columns(almost 30) combined with large number of records, this could degrade the performance. In such case we suggest to use LINQ for fetching the data from the database. This approach could greatly improve the performance, sample application that uses this approach could be found here.

Additionally, you could check the help topics from the performance section of RadGrid.

Greetings,
Andrey
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
M. R.
Top achievements
Rank 1
answered on 16 Jul 2012, 08:15 PM
Thank you Andrey, I modified the code according to LINQ tutorial and the grid performance greatly improved!

Regards,
M.R.
Tags
Grid
Asked by
M. R.
Top achievements
Rank 1
Answers by
M. R.
Top achievements
Rank 1
Andrey
Telerik team
Share this question
or