Is there a way I can better structure this LINQ query?

Question

I'm working on a new WPF application. It needs to display the results of a query in a datagrid when it launches. In testing I've found that under certain circumstances the time it takes for the query to run can be 2 minutes - unacceptably long. However, under other circumstances it can be much faster. The difference depends in part upon where the application is running and I suspect other factors contribute to how fast it can run. Being closer to the server (on-prem) then it is only a matter of a few seconds, although it will run faster at other offices even further away from the server. Most of us are still working from home. At my home I have incredibly good Internet speeds (for my state), but I still have abysmal performance on that query.

But I've no idea at this point how fast that query will run for the users, who are as far away from the server as I am. Here's the LINQ query to retrieve the data. Can I restructure the LINQ better for better performance?

using (var ctx = new AppEntities())
{
    People = ctx.People.AsNoTracking().Where(p => p.Inactive == false)
        .Include(p => p.Agency)
        .Include(p => p.PersonnelCertifications.Select(pc => pc.CertificationType))
        .Where(p => p.PersonnelCertifications.Any(pc => pc.CertificationType.CertType == "Operator"))
        .OrderBy(p => p.LastName)
        .ThenBy(p => p.FirstName)
        .ToList();
}

Here's the primary table Person:

CREATE TABLE [app].[Person](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [AgencyID] [bigint] NOT NULL,
    [LastName] [nvarchar](25) NOT NULL,
    [FirstName] [nvarchar](20) NOT NULL,
    --other columns removed for brevity
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And table Agency:

CREATE TABLE [app].[Agency](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [AgencyCode] [nvarchar](5) NOT NULL,
    [AgencyName] [nvarchar](50) NULL,
    --other columns removed for brevity
 CONSTRAINT [PK_Agency] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY],
 CONSTRAINT [AK_AgencyCode] UNIQUE NONCLUSTERED 
(
    [AgencyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And table PersonnelCertification:

CREATE TABLE [app].[PersonnelCertification](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [CertificationTypeID] [bigint] NOT NULL,
    [PersonID] [bigint] NOT NULL,
    [AgencyID] [bigint] NOT NULL,
    --other columns removed for brevity
 CONSTRAINT [PK_PersonnelCertification] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

And finally table CertificationType:

CREATE TABLE [app].[CertificationType](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [CertType] [nvarchar](30) NOT NULL,
    --other columns removed for brevity
 CONSTRAINT [PK_CertificationType] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY],
 CONSTRAINT [CertType] UNIQUE NONCLUSTERED 
(
    [CertType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

If you have determined that: a) This is the place which takes the most time; and b) the time taken is mostly affected by the network speed and/or latency; -- then I don't see how optimizing the query would help anything. — Vilx-, Apr 23 '21 at 16:26
Are you using Entity Framework? Or are all your objects already in memory from some other db function? — ps2goat, Apr 23 '21 at 16:39
If using EF, Adding foreign keys or indexes may help, but when you start linking this many tables together it may be better to just make a new entity in code and a view in the db. Check the query output by EF to see what it's doing. More complicated joins/queries in ORMs tend to produce garbage queries that could be simplified if put in a view. We won't really know how to make it better without seeing the generated output. — ps2goat, Apr 23 '21 at 16:41
you can try left join https://stackoverflow.com/questions/17142151/linq-to-sql-multiple-tables-left-outer-join — Power Mouse, Apr 23 '21 at 16:44
also, you can move filter CertificationType == "Operator" inside to limit records you linking, and select where p.PersonnelCertifications != null — Power Mouse, Apr 23 '21 at 16:57
ps2goat I'm using EF 6. Vilx the resulting SQL generated by EF is too long to fit in SO's comment field. ps2goat I've developed a SQL script which I can put into a new SQL View. I'm hoping for something else, such as putting at least the most complex SQL statements into a library class, then put that into a web service running on the server. It would be faster but using web services is counter to accepted practice where I work. :( — Rod, Apr 23 '21 at 19:51
@Rod - you should update your question with information that is asked for. There should be enough room there ;) — ps2goat, Apr 23 '21 at 23:20

Is there a way I can better structure this LINQ query?

0 Answers0