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

Using filter on LINQ grid is slow

4 Answers 149 Views

Grid

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

Michael asked on 21 Jun 2011, 05:53 PM

I'm stumped on this performance issue. When paging a RadGrid I get instantaneous results. However, when I filter any of the columns, the query takes around 10 seconds to complete and any paging applied after the filter remains just as slow. If I remove the filter the performance is just fine.

Production is a SharePoint 2010 Server which is hosted in-house with a small number of users. I am using a RadGrid control, version 2009.3..1314.35 that queries a table containing 30,000 records. I am using LINQ to SQL for the back-end. I have used IE and Firefox in production. On my development machine there are no performance issues.

Below is my code. Any help would be appreciated.

ASPX:

<telerik:RadGrid ID="rdgrdPeople" DataSourceID="people" PageSize="10" AllowPaging="true" AllowAutomaticDeletes="true" AllowMultiRowSelection="true" Skin="Windows7" OnItemDataBound="rdgrdPeople_ItemDataBound"

  AllowFilteringByColumn="True" AllowSorting="True" AutoGenerateColumns="false" runat="server">

    <GroupingSettings CaseSensitive="False" />

    <MasterTableView AutoGenerateColumns="false" DataKeyNames="Id" OverrideDataSourceControlSorting="true">

        <Columns>

            <telerik:GridHyperLinkColumn Text="View" UniqueName="View" AllowFiltering="false" DataNavigateUrlFields="Id,Profile"

                            DataNavigateUrlFormatString="managecontact.aspx?id={0}&profile={1}" />

            <telerik:GridHyperLinkColumn Text="Edit" UniqueName="Edit" AllowFiltering="false" DataNavigateUrlFields="Id,Profile"

                            DataNavigateUrlFormatString="managecontact.aspx?id={0}&profile={1}" />

            <telerik:GridButtonColumn ButtonType="LinkButton" UniqueName="Delete" CommandName="Delete" Text="Delete" ConfirmText="Are you sure you want to delete this person?" />

          <telerik:GridTemplateColumn AllowFiltering="false" SortExpression="Marked" HeaderText="Marked" UniqueName="Marked">

               <ItemTemplate>

                 <asp:CheckBox

                   ID="chkbxMarked" runat="server"

                   OnCheckedChanged="ToggleRowSelection"

                   Checked='<%# Eval("Marked") %>'

                   AutoPostBack="True" />

               </ItemTemplate>

            </telerik:GridTemplateColumn>

            <telerik:GridBoundColumn DataField="NamePrefix" SortExpression="NamePrefix" AllowFiltering="false" HeaderText="Name Prefix" />

            <telerik:GridBoundColumn DataField="LastName" SortExpression="LastName" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" HeaderText="Last Name" />

            <telerik:GridBoundColumn DataField="FirstName" SortExpression="FirstName" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" HeaderText="First Name" />

            <telerik:GridBoundColumn DataField="MiddleName" SortExpression="MiddleName" AllowFiltering="false" HeaderText="Middle Name" />

            <telerik:GridBoundColumn DataField="Phone1" SortExpression="Phone1" AllowFiltering="false" HeaderText="Phone" />

            <telerik:GridBoundColumn DataField="Phone1Ext" SortExpression="Phone1Ext" AllowFiltering="false" HeaderText="Ext." />

            <telerik:GridBoundColumn DataField="Email1" SortExpression="Email1" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" HeaderText="Email" />

            <telerik:GridBoundColumn DataField="Profile" SortExpression="Profile" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" HeaderText="Profile" />



        </Columns>

    </MasterTableView>

</telerik:RadGrid>

<asp:ObjectDataSource ID="people" runat="server" EnablePaging="true" SelectMethod="FindAllByProfile" DeleteMethod="Delete"

    TypeName="PRA.Business.PersonLogic" StartRowIndexParameterName="rowStart" MaximumRowsParameterName="numRows"

    SelectCountMethod="FindAllByProfileCount" >

    <SelectParameters>

        <asp:ControlParameter ControlID="drplstProfiles" PropertyName="SelectedItem.Value" Name="profileName" />

    </SelectParameters>

    <DeleteParameters>

        <asp:Parameter Name="Id" />

    </DeleteParameters>

</asp:ObjectDataSource>

BLL:

public IList<Person> FindAllByProfile(string profileName, int rowStart, int numRows)

       {

           return profileName == "All" ? _repos.FindAll(rowStart, numRows) : _repos.FindAllByProfile(profileName, rowStart, numRows);

       }

       public int FindAllByProfileCount(string profileName)

       {

           return profileName == "All" ? _repos.FindAllCount() : _repos.FindAllByProfileCount(profileName);

       }

Repository:

public IList<Person> FindAll(int rowStart, int numRows)

      {

          using (PRADbDataContext db = new PRADbDataContext())

          {

              var data = from p in db.persons

                         join c in db.contacts on p.PersKey equals c.PersKey into personContacts

                         from pc in personContacts.DefaultIfEmpty()

                         orderby p.Modified descending

                         select new Person()

                                    {

                                        Id = p.PersKey,

                                        AddressId = p.AddrKey,

                                        DateModified = p.Modified,

                                        Email1 = p.EMail1,

                                        Marked = p.Marked,

                                        Phone1 = p.Phone1,

                                        Phone1Ext = p.PhExt1,

                                        NamePrefix = p.MrMs,

                                        FirstName = p.FName,

                                        LastName = p.LName,

                                        MiddleName = p.MName,

                                        Title = p.Title,

                                        Profile = pc.ProfKey ?? "N/A"

                                    };

              return data.Skip(rowStart).Take(numRows).ToList();

          }

      }

public int FindAllCount()

      {

          using (PRADbDataContext db = new PRADbDataContext())

              {

                  var data = from p in db.persons

                             join c in db.contacts on p.PersKey equals c.PersKey

                             select new Person()

                             {

                                 Id = p.PersKey,

                             };

                  return data.Count();

              }

      }

4 Answers, 1 is accepted

Accepted