Count() method giving error Timeout expired - The timeout period elapsed prior to completion of the operation or the server is not responding. : The Official Microsoft ASP.NET Forums

Reply

w3hunter

0 Points

5 Posts

Count() method giving error Timeout expired - The timeout period elapsed prior to completion of t...

Oct 19, 2009 08:38 PM|LINK

Hi,

I am facing some problem in LINQ.

There are three tables, let's say 'Profile', 'Test1' & 'Test2'. (There are other tables, too).

Profile - Id, Username, Email, Password

Test1 - Id, Title, Description

Test2 - ProfileId, Test1Id, Percentage

Test2.ProfileId & Test2.Test1Id are definned as a composite primary key and foreign key reference to relevant tables.

Test2 table has more than 9000000(9 million) records.

Now I want to implement paging, so I am using .count() method of IQuerable<> object, but it is giving me following error.

System.Data.SqlClient.SqlException

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

var list = (from opp in _db.EmploymentOpportunities
                        join company in _db.Companies on opp.OfficeId equals company.Id     // Join using OfficeId
                        join cities in _db.Cities on company.CityId equals cities.Id
                        join regions in _db.Regions on company.RegionId equals regions.Id
                        where opp.Company.ActivePartner == true && DateTime.Now < opp.DeadLine
                        select new HalfAnonymous { EmploymentOpportunity = opp, City = cities, Company = company, Region = regions, Percentage = 0 });


            HttpContext.Current.Trace.Warn("After fetching all data & Before JoinMatchTable : " + DateTime.Now.ToString());
            // Join Matches table
            var joinMatchTable = new Func<bool, IQueryable<HalfAnonymous>>((desiredJobs) =>
            {
                var profileId = base.GetMembershipId();


                /*return list.GroupJoin(
                    _db.Matches,
                        x => new { ProfileId = profileId, EmploymentOpportunityId = x.EmploymentOpportunity.Id },
                        y => new { ProfileId = y.ProfileId, EmploymentOpportunityId = y.EmploymentOpportunityId },
                        (x, y) => new { TempResult = x, Matches = y.DefaultIfEmpty() })
                    .SelectMany(
                        x => x.Matches,
                        (x, match) => new HalfAnonymous
                        {
                            EmploymentOpportunity = x.TempResult.EmploymentOpportunity,
                            Company = x.TempResult.Company,
                            City = x.TempResult.City,
                            Region = x.TempResult.Region,
                            Percentage = (match != null) ? match.MatchPercent : (byte)0,
                            //Percentage = (match != null && match.FitMe) ? match.MatchPercent : (byte)0,
                        });*/






                if (model.DesiredJobs)
                {
                    //case when user selects 'Desired Jobs' in "Jobs That Fit Me" page..
                    return list.GroupJoin(
                _db.Matches,
                    x => new { ProfileId = profileId, EmploymentOpportunityId = x.EmploymentOpportunity.Id },
                    y => new { ProfileId = y.ProfileId, EmploymentOpportunityId = y.EmploymentOpportunityId },
                    (x, y) => new { TempResult = x, Matches = y.Where(m => m.FitMe == true) })
                .SelectMany(
                    x => x.Matches,
                    (x, match) => new HalfAnonymous
                    {
                        EmploymentOpportunity = x.TempResult.EmploymentOpportunity,
                        Company = x.TempResult.Company,
                        Percentage = (match != null) ? match.MatchPercent : (byte)0
                        //Percentage = (match != null && match.FitMe) ? match.MatchPercent : (byte)0,
                    });
                }
                else
                {
                    //case for all the matches..
                    return list.GroupJoin(
                _db.Matches,
                    x => new { ProfileId = profileId, EmploymentOpportunityId = x.EmploymentOpportunity.Id },
                    y => new { ProfileId = y.ProfileId, EmploymentOpportunityId = y.EmploymentOpportunityId },
                    (x, y) => new { TempResult = x, Matches = y.DefaultIfEmpty() })
                .SelectMany(
                    x => x.Matches,
                    (x, match) => new HalfAnonymous
                    {
                        EmploymentOpportunity = x.TempResult.EmploymentOpportunity,
                        Company = x.TempResult.Company,
                        Percentage = (match != null) ? match.MatchPercent : (byte)0
                        //Percentage = (match != null && match.FitMe) ? match.MatchPercent : (byte)0,
                    });
                }




            });




            if (includePercentageForCount)
                list = joinMatchTable(model.DesiredJobs);




            list = SetConditions(model, list);


            var result = GetCount(model, list);














/*GetCount Function*/
JobOppsSearchResultModel GetCount(JobOppsSearchConditionModel model, IQueryable<HalfAnonymous> list)
        {
            var result = new JobOppsSearchResultModel();
            result.TotalRecords = list.Count();
            result.TotalPages = (result.TotalRecords == 0) ? 0 : (result.TotalRecords / model.DisplayRows) + 1;
            result.Page = (result.TotalPages == 0) ? 0 : model.CurrentPage;
            return result;
        }

Please help me out..

Thanks in advance..

Moved by XIII on Oct 19, 2009 08:56 PM

Reply

kwanann

Contributor

3816 Points

750 Posts

MVP

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

Oct 19, 2009 10:43 PM|LINK

hmm can you run sql profiler and see what is the sql that's being executed?

chances are due to the large number of records and lack of indexes, your query is taking too long to run. you will need to optimize either your query or tables to speed up the execution speed

MVP ASP.NET C#
View my blog @ http://jefferytay.wordpress.com

Reply

w3hunter

0 Points

5 Posts

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

Oct 20, 2009 05:27 PM|LINK

var selectedMatches = (from opp in _db.EmploymentOpportunities
                                       from prof in _db.Profiles
                                       from mat in _db.Matches
                                       where mat.EmploymentOpportunityId == opp.Id && mat.ProfileId == profId && mat.MatchPercent >= model.MatchPercentage
                                       select mat).Distinct();
                                      



                if (model.DesiredJobs)
                {
                    list = (from opp in _db.EmploymentOpportunities
                            join company in _db.Companies on opp.OfficeId equals company.Id     // Join using OfficeId
                            join cities in _db.Cities on company.CityId equals cities.Id
                            join regions in _db.Regions on company.RegionId equals regions.Id
                            join mat in selectedMatches on opp.Id equals mat.EmploymentOpportunityId
                            join prof in _db.Profiles on mat.ProfileId equals prof.Id
                            where opp.Company.ActivePartner == true && DateTime.Now < opp.DeadLine && mat.MatchPercent > model.MatchPercentage && prof.Id == profId && mat.FitMe == true
                            select new HalfAnonymous { EmploymentOpportunity = opp, City = cities, Company = company, Region = regions, Percentage = mat.MatchPercent });
                }
                else
                {
                    list = (from opp in _db.EmploymentOpportunities
                            join company in _db.Companies on opp.OfficeId equals company.Id     // Join using OfficeId
                            join cities in _db.Cities on company.CityId equals cities.Id
                            join regions in _db.Regions on company.RegionId equals regions.Id
                            join mat in selectedMatches on opp.Id equals mat.EmploymentOpportunityId
                            join prof in _db.Profiles on mat.ProfileId equals prof.Id
                            where opp.Company.ActivePartner == true && DateTime.Now < opp.DeadLine && mat.MatchPercent > model.MatchPercentage && prof.Id == profId
                            select new HalfAnonymous { EmploymentOpportunity = opp, City = cities, Company = company, Region = regions, Percentage = mat.MatchPercent });
                }
                

            }
            else
            {
                list = (from opp in _db.EmploymentOpportunities
                        join company in _db.Companies on opp.OfficeId equals company.Id     // Join using OfficeId
                        join cities in _db.Cities on company.CityId equals cities.Id
                        join regions in _db.Regions on company.RegionId equals regions.Id
                        where opp.Company.ActivePartner == true && DateTime.Now < opp.DeadLine
                        select new HalfAnonymous { EmploymentOpportunity = opp, City = cities, Company = company, Region = regions, Percentage = 0 });
            }

SELECT COUNT(*) AS [value]
FROM [dbo].[EmploymentOpportunities] AS [t0]
INNER JOIN [dbo].[Companys] AS [t1] ON [t0].[OfficeId] = [t1].[Id]
INNER JOIN [dbo].[Cities] AS [t2] ON [t1].[CityId] = [t2].[Id]
INNER JOIN [dbo].[Regions] AS [t3] ON [t1].[RegionId] = [t3].[Id]
INNER JOIN (
    SELECT DISTINCT [t6].[EmploymentOpportunityId], [t6].[ProfileId], [t6].[MatchPercent], [t6].[SkillsPercent], [t6].[FutureSkillsPercent], [t6].[CompetencyPercent], [t6].[ExperiencePercent], [t6].[FitMe]
    FROM [dbo].[EmploymentOpportunities] AS [t4], [dbo].[Profiles] AS [t5], [dbo].[Matches] AS [t6]
    WHERE ([t6].[EmploymentOpportunityId] = [t4].[Id]) AND ([t6].[ProfileId] = @p0) AND ((CONVERT(Int,[t6].[MatchPercent])) >= @p1)
    ) AS [t7] ON [t0].[Id] = [t7].[EmploymentOpportunityId]
INNER JOIN [dbo].[Profiles] AS [t8] ON [t7].[ProfileId] = [t8].[Id]
INNER JOIN [dbo].[Companys] AS [t9] ON [t9].[Id] = [t0].[CompanyId]
WHERE ([t0].[Status] = @p2) AND ([t7].[MatchPercent] >= @p3) AND ([t9].[ActivePartner] = 1) AND (@p4 < [t0].[DeadLine]) AND ((CONVERT(Int,[t7].[MatchPercent])) > @p5) AND ([t8].[Id] = @p6)

"Matches" table has composite (nonclustered) primary key of EmpOppId & ProfileId which are also defined as foreign key.

Is there any other index i can set to speed up the execution ?

Edited by w3hunter on Oct 20, 2009 05:28 PM
Edited by w3hunter on Oct 20, 2009 05:30 PM
Edited by w3hunter on Oct 20, 2009 05:32 PM
Edited by w3hunter on Oct 20, 2009 05:33 PM
Edited by w3hunter on Oct 20, 2009 05:35 PM

Reply

kwanann

Contributor

3816 Points

750 Posts

MVP

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

Oct 21, 2009 07:35 AM|LINK

w3hunter

SELECT DISTINCT [t6].[EmploymentOpportunityId], [t6].[ProfileId], [t6].[MatchPercent], [t6].[SkillsPercent], [t6].[FutureSkillsPercent], [t6].[CompetencyPercent], [t6].[ExperiencePercent], [t6].[FitMe]
    FROM [dbo].[EmploymentOpportunities] AS [t4], [dbo].[Profiles] AS [t5], [dbo].[Matches] AS [t6]
    WHERE ([t6].[EmploymentOpportunityId] = [t4].[Id]) AND ([t6].[ProfileId] = @p0) AND ((CONVERT(Int,[t6].[MatchPercent])) >= @p1)

anyway of optimizing this further? e.g using inner joins?

w3hunter

INNER JOIN [dbo].[Companys] AS [t1] ON [t0].[OfficeId] = [t1].[Id]
INNER JOIN [dbo].[Cities] AS [t2] ON [t1].[CityId] = [t2].[Id]
INNER JOIN [dbo].[Regions] AS [t3] ON [t1].[RegionId] = [t3].[Id]

these are not used inside your joins and conditions, can take them out?

w3hunter

INNER JOIN (
    SELECT DISTINCT [t6].[EmploymentOpportunityId], [t6].[ProfileId], [t6].[MatchPercent], [t6].[SkillsPercent], [t6].[FutureSkillsPercent], [t6].[CompetencyPercent], [t6].[ExperiencePercent], [t6].[FitMe]
    FROM [dbo].[EmploymentOpportunities] AS [t4], [dbo].[Profiles] AS [t5], [dbo].[Matches] AS [t6]
    WHERE ([t6].[EmploymentOpportunityId] = [t4].[Id]) AND ([t6].[ProfileId] = @p0) AND ((CONVERT(Int,[t6].[MatchPercent])) >= @p1)
    ) AS [t7] ON [t0].[Id] = [t7].[EmploymentOpportunityId]
INNER JOIN [dbo].[Profiles] AS [t8] ON [t7].[ProfileId] = [t8].[Id]
INNER JOIN [dbo].[Companys] AS [t9] ON [t9].[Id] = [t0].[CompanyId]
WHERE ([t0].[Status] = @p2) AND ([t7].[MatchPercent] >= @p3) AND ([t9].[ActivePartner] = 1) AND (@p4 < [t0].[DeadLine]) AND ((CONVERT(Int,[t7].[MatchPercent])) > @p5)

since t7 is obtained from t6, possible to specify just one matchpercent condition instead of 2 separate ones? after all both are > conditions

MVP ASP.NET C#
View my blog @ http://jefferytay.wordpress.com

Reply

w3hunter

0 Points

5 Posts

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

Oct 22, 2009 06:40 PM|LINK

Hi,

Thanks for your reply..

I have tried what you have suggested. I have changed code as follows...

IQueryable<EmploymentOpportunity> lstEmpOpp = _db.EmploymentOpportunities;


           if (includePercentageForCount)
           {
               var profId = this.GetMembershipId();
               if (model.DesiredJobs) {
                   lstEmpOpp = (from matc in _db.Matches
                                join empOpp in _db.EmploymentOpportunities on matc.EmploymentOpportunityId equals empOpp.Id
                                join prof in _db.Profiles on matc.ProfileId equals prof.Id
                                join comp in _db.Companies on empOpp.OfficeId equals comp.Id
                                where matc.FitMe == true && matc.MatchPercent > model.MatchPercentage && prof.Id == profId && empOpp.Status == 1 && DateTime.Now < empOpp.DeadLine && comp.ActivePartner == true
                                select empOpp);
               } else {
                   lstEmpOpp = (from matc in _db.Matches
                                join empOpp in _db.EmploymentOpportunities on matc.EmploymentOpportunityId equals empOpp.Id
                                join prof in _db.Profiles on matc.ProfileId equals prof.Id
                                join comp in _db.Companies on empOpp.OfficeId equals comp.Id
                                where matc.MatchPercent > model.MatchPercentage && prof.Id == profId && empOpp.Status == 1 && DateTime.Now < empOpp.DeadLine && comp.ActivePartner == true
                                select empOpp);
               }


               



           }
           else
           {
               lstEmpOpp = (from empOpp in _db.EmploymentOpportunities
                            join comp in _db.Companies on empOpp.OfficeId equals comp.Id
                            where empOpp.Status == 1 && comp.ActivePartner == true && DateTime.Now < empOpp.DeadLine
                            select empOpp);
           }


            var result = GetCount(model, lstEmpOpp);

which results in following query.

SELECT COUNT(*) AS [value]
FROM [dbo].[Matches] AS [t0]
INNER JOIN [dbo].[EmploymentOpportunities] AS [t1] ON [t0].[EmploymentOpportunityId] = [t1].[Id]
INNER JOIN [dbo].[Profiles] AS [t2] ON [t0].[ProfileId] = [t2].[Id]
INNER JOIN [dbo].[Companys] AS [t3] ON [t1].[OfficeId] = [t3].[Id]
WHERE ((CONVERT(Int,[t0].[MatchPercent])) > @p0) AND ([t2].[Id] = @p1) AND ([t1].[Status] = @p2) AND (@p3 < [t1].[DeadLine]) AND ([t3].[ActivePartner] = 1)

But still having same problem.. it is giving me same error.

Is there any index which I can define ?

Reply

w3hunter

0 Points

5 Posts

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

Oct 22, 2009 08:15 PM|LINK

Hurray.. my problem is solved...

Thanks for all your help..

w3hunter

<div style="position: absolute; left: -10000px; top: 591px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">SELECT COUNT(*)</div> <div style="position: absolute; left: -10000px; top: 591px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">FROM [dbo].[Matches] AS [t0]</div> <div style="position: absolute; left: -10000px; top: 591px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">INNER JOIN [dbo].[EmploymentOpportunities] AS [t1] ON [t0].[EmploymentOpportunityId] = [t1].[Id]</div> <div style="position: absolute; left: -10000px; top: 591px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">INNER JOIN [dbo].[Profiles] AS [t2] ON [t0].[ProfileId] = [t2].[Id]</div> <div style="position: absolute; left: -10000px; top: 591px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">INNER JOIN [dbo].[Companys] AS [t3] ON [t1].[OfficeId] = [t3].[Id]</div> <div style="position: absolute; left: -10000px; top: 591px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">WHERE ((CONVERT(Int,[t0].[MatchPercent])) > 127) AND ([t2].[Id] = '2e9f002f-d1e1-46a8-a610-740e3b1192a2') AND ([t1].[Status] = 1) AND ('2009-10-22' < [t1].[DeadLine]) AND ([t3].[ActivePartner] = 1)</div> <div></div>

SELECT COUNT(*)

FROM [dbo].[Matches] AS [t0]

INNER JOIN [dbo].[EmploymentOpportunities] AS [t1] ON [t0].[EmploymentOpportunityId] = [t1].[Id]

INNER JOIN [dbo].[Profiles] AS [t2] ON [t0].[ProfileId] = [t2].[Id]

INNER JOIN [dbo].[Companys] AS [t3] ON [t1].[OfficeId] = [t3].[Id]

WHERE ((CONVERT(Int,[t0].[MatchPercent])) > 127) AND ([t2].[Id] = '2e9f002f-d1e1-46a8-a610-740e3b1192a2') AND ([t1].[Status] = 1) AND ('2009-10-22' < [t1].[DeadLine]) AND ([t3].[ActivePartner] = 1)

I ran this query and checked Execution Plan. It suggested me to define NonCluster Index on the Matches table..

I did the same and my problem is resolved.

Marked as answer by w3hunter on Oct 22, 2009 08:16 PM

Reply

kwanann

Contributor

3816 Points

750 Posts

MVP

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

Oct 22, 2009 10:03 PM|LINK

that's great :)

do take note of such cases in future, never join more tables than those absolutely neccesary

also sql server mgmt studio 2008 is your best friend for such queries, it can tell you what index you can add to optimize your queries :)

MVP ASP.NET C#
View my blog @ http://jefferytay.wordpress.com

Count() method giving error Timeout expired - The timeout period elapsed prior to completion of the operation or the server is not responding. RSS

6 replies

w3hunter

Count() method giving error Timeout expired - The timeout period elapsed prior to completion of t...

kwanann

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

w3hunter

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

kwanann

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

w3hunter

w3hunter

w3hunter

w3hunter

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

w3hunter

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

w3hunter

kwanann

Re: Count() method giving error Timeout expired - The timeout period elapsed prior to completion ...

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft