Slow loading data and problems with paging

[plusHR]

Hi,

I know the answer is going to be with my query, however we have 2,500 records being returned using RIA. I have adjusted the query as the database has a large amount of columns and we only need a small subset of the data to be shown in the grid. I have adjusted the loading page size to 500 in the data source, however when running an SQL trace the query is returning all records and the paging is being done on the client side. Therefore the binding is very slow. Could you look at my query and advise why this is not paging or how to modify this;

var query = from person in this.ObjectContext.data_PERSON
                        join roles in this.ObjectContext.data_ROLE on person.rowguid equals roles.personguid into tempRole
                        select new
                        {
                            person.firstname, person.lastname, person.emailprimary, person.rowguid, person.companyguid,
                            tempRole = (from role in tempRole
                                        select new
                                        {
                                            role.rowguid, role.linemanager, role.personaljobtitle,
                                            role.orglevel1, role.orglevel2, role.orglevel3, role.orglevel4, role.orglevel5,
                                        }).FirstOrDefault()
                        };

            //company
            var companysecurity = (from c in this.ObjectContext.system_SECURITY_ADMIN_COMPANY
                                   where c.securityguid == u.AdminSecurityGuid
                                   select c.companyguid).Distinct().ToList();

            if (companysecurity.Count != 0)
                query = from q in query where companysecurity.Contains(q.companyguid) select q;

            //orglevel 1
            var orglevel1security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL1
                                     where o.securityguid == u.AdminSecurityGuid
                                     select o.orglevel1guid).Distinct().ToList();

            if (orglevel1security.Count != 0)
                query = from q in query where orglevel1security.Contains(q.tempRole.orglevel1) select q;

            //orglevel 2
            var orglevel2security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL2
                                     where o.securityguid == u.AdminSecurityGuid
                                     select o.orglevel2guid).Distinct().ToList();

            if (orglevel2security.Count != 0)
                query = from q in query where orglevel2security.Contains(q.tempRole.orglevel2) select q;

            //orglevel 3
            var orglevel3security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL3
                                     where o.securityguid == u.AdminSecurityGuid
                                     select o.orglevel3guid).Distinct().ToList();

            if (orglevel3security.Count != 0)
                query = from q in query where orglevel3security.Contains(q.tempRole.orglevel3) select q;

            //orglevel 4
            var orglevel4security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL4
                                     where o.securityguid == u.AdminSecurityGuid
                                     select o.orglevel4guid).Distinct().ToList();

            if (orglevel4security.Count != 0)
                query = from q in query where orglevel4security.Contains(q.tempRole.orglevel4) select q;


            return query.ToList().AsQueryable().Select(
                p => new data_PERSON
                {
                    rowguid = p.rowguid,
                    emailprimary = p.emailprimary,
                    firstname = p.firstname,
                    lastname = p.lastname,
                    data_CURRENT_ROLE = (new data_ROLE
                    {
                        rowguid = p.tempRole.rowguid,
                        linemanager = p.tempRole.linemanager,
                        personaljobtitle = p.tempRole.personaljobtitle,
                    })
                }).OrderBy(o=>o.firstname);
        }

[Vlad]

Hi,

 If you want to use paging for your own constructed queries you need Skip() and Take() methods on the client. Otherwise you can use the codeless approach with DomainDataSource/RadDomainDataSource. 

Kind regards,
Vlad
the Telerik team

[plusHR]

HI, many thanks for the quick response. The dataset is using the domaindatasource however I have changed this to the RadDomainDataSource. It does load faster, however when I do an SQL trace it is still retrieving all of the records when you move from one page to the next.

SELECT
1 AS [C1],
[Extent1].[firstname] AS [firstname],
[Extent1].[lastname] AS [lastname],
[Extent1].[emailprimary] AS [emailprimary],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[companyguid] AS [companyguid],
[Limit1].[C1] AS [C2],
[Limit1].[rowguid] AS [rowguid1],
[Limit1].[linemanager] AS [linemanager],
[Limit1].[personaljobtitle] AS [personaljobtitle],
[Limit1].[orglevel1] AS [orglevel1],
[Limit1].[orglevel2] AS [orglevel2],
[Limit1].[orglevel3] AS [orglevel3],
[Limit1].[orglevel4] AS [orglevel4],
[Limit1].[orglevel5] AS [orglevel5]
FROM  [dbo].[data_PERSON] AS [Extent1]
OUTER APPLY  (SELECT TOP (1)
    1 AS [C1],
    [Extent2].[rowguid] AS [rowguid],
    [Extent2].[orglevel1] AS [orglevel1],
    [Extent2].[orglevel2] AS [orglevel2],
    [Extent2].[orglevel3] AS [orglevel3],
    [Extent2].[orglevel4] AS [orglevel4],
    [Extent2].[orglevel5] AS [orglevel5],
    [Extent2].[personaljobtitle] AS [personaljobtitle],
    [Extent2].[linemanager] AS [linemanager]
    FROM [dbo].[data_ROLE] AS [Extent2]
    WHERE [Extent1].[rowguid] = [Extent2].[personguid] ) AS [Limit1]

public IQueryable<data_PERSON> PerformanceAdmin_GetEmployees()
{

    MembershipServiceUser u = new MembershipServiceUser(Membership.GetUser());

    var query = from person in this.ObjectContext.data_PERSON
                join roles in this.ObjectContext.data_ROLE on person.rowguid equals roles.personguid into tempRole
                select new
                {
                    person.firstname, person.lastname, person.emailprimary, person.rowguid, person.companyguid,
                    tempRole = (from role in tempRole
                                select new
                                {
                                    role.rowguid, role.linemanager, role.personaljobtitle,
                                    role.orglevel1, role.orglevel2, role.orglevel3, role.orglevel4, role.orglevel5,
                                }).FirstOrDefault()
                };

    //company
    var companysecurity = (from c in this.ObjectContext.system_SECURITY_ADMIN_COMPANY
                           where c.securityguid == u.AdminSecurityGuid
                           select c.companyguid).Distinct().ToList();

    if (companysecurity.Count != 0)
        query = from q in query where companysecurity.Contains(q.companyguid) select q;

    //orglevel 1
    var orglevel1security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL1
                             where o.securityguid == u.AdminSecurityGuid
                             select o.orglevel1guid).Distinct().ToList();

    if (orglevel1security.Count != 0)
        query = from q in query where orglevel1security.Contains(q.tempRole.orglevel1) select q;

    //orglevel 2
    var orglevel2security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL2
                             where o.securityguid == u.AdminSecurityGuid
                             select o.orglevel2guid).Distinct().ToList();

    if (orglevel2security.Count != 0)
        query = from q in query where orglevel2security.Contains(q.tempRole.orglevel2) select q;

    //orglevel 3
    var orglevel3security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL3
                             where o.securityguid == u.AdminSecurityGuid
                             select o.orglevel3guid).Distinct().ToList();

    if (orglevel3security.Count != 0)
        query = from q in query where orglevel3security.Contains(q.tempRole.orglevel3) select q;

    //orglevel 4
    var orglevel4security = (from o in this.ObjectContext.system_SECURITY_ADMIN_ORGLEVEL4
                             where o.securityguid == u.AdminSecurityGuid
                             select o.orglevel4guid).Distinct().ToList();

    if (orglevel4security.Count != 0)
        query = from q in query where orglevel4security.Contains(q.tempRole.orglevel4) select q;


    return query.ToList().AsQueryable().Select(
        p => new data_PERSON
        {
            rowguid = p.rowguid,
            emailprimary = p.emailprimary,
            firstname = p.firstname,
            lastname = p.lastname,
            data_CURRENT_ROLE = (new data_ROLE
            {
                rowguid = p.tempRole.rowguid,
                linemanager = p.tempRole.linemanager,
                personaljobtitle = p.tempRole.personaljobtitle,
            })
        }).OrderBy(o=>o.firstname);
}

So I would of thought that the data being returned from the server should also be restricted to the page sizes?

[plusHR]

If I change the query to do 

return this.ObjectContext.data_PERSON.OrderBy(o=>o.firstname);

Then the data is collection and 'skipped' correctly so the problem is with my query, and I am guessing this is due to the ToList(), however I dont want to bring back all of the columns in the data_PERSON table, so this is why its a complex query, so how can I get this query to listen to the skip/take command or make changes to the query so that the correct restrictions are applied?