Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Query execution is very slow compared to SQL due to DataReader taking a long time to read results #35480

Open
aldrashan opened this issue Jan 15, 2025 · 7 comments
Open

Query execution is very slow compared to SQL due to DataReader taking a long time to read results #35480

aldrashan opened this issue Jan 15, 2025 · 7 comments
Assignees
@roji
Labels
area-perf area-query

Comments

@aldrashan
Copy link

I basically have the same issue (I think) like a lot of the other people where my query is "fast" to execute, but where the conversion to C# objects is taking way longer than expected.
I stumbled upon the following issues which were similar:
#23900
#24194
I believe these issues are similar because the view I'm using is outputting JSON columns, which are potentially very long.

These issues were apparently fixed by swapping .ToListAsync() for .ToList(), but that did not resolve my issue.

I've also bumped into #34939 where the solution was to reference the latest Microsoft.Data.SqlClient (5.2.2), but that also did not resolve my issue.

I've tried to use versions 7.0.20, 8.0.11, 8.0.12 and 9.0.0 of Microsoft.EntityFrameworkCore but the issue persists in all of them.

Image

As you can tell from the screenshot, my issue is based around the .Where(x=>...) clauses.
For some queries, the datareader manages to read everything very fast and for others it's just slow for some reason.
It's always returning the same 11 rows, so I'd guess the length of the strings (all nvarchar) is not the culprit here.

This is the console output I'm getting:

dbug: Microsoft.EntityFrameworkCore.Infrastructure[10401]
      An 'IServiceProvider' was created for internal use by Entity Framework.
warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 7.0.20 initialized 'CRMContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:7.0.20' with options: SensitiveDataLoggingEnabled DetailedErrorsEnabled
dbug: Microsoft.EntityFrameworkCore.Query[10111]
      Compiling query expression:
      'DbSet<VwCombinedLeads>()
          .AsNoTracking()
          .Where(x => x.Closed == (bool?)True || x.Qualified == (bool?)True)
          .Where(x => x.Type == "Lead" && DbSet<TblLeads>()
              .Where(lead => lead.UidLead == x.UidLead)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: lead => new {
                      AccountNum = __customerAccountNum_0,
                      CustomerObjectType = lead.CustomerObjectType,
                      CustomerObjectId = lead.CustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      CustomerObjectType = 0,
                      CustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (lead, cust) => 1)
              .Any() || DbSet<TblLeads>()
              .Where(lead => lead.UidLead == x.UidLead && lead.EndCustomerObjectType.HasValue)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: lead => new {
                      AccountNum = __customerAccountNum_0,
                      EndCustomerObjectType = lead.EndCustomerObjectType.Value,
                      EndCustomerObjectId = lead.EndCustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      EndCustomerObjectType = 0,
                      EndCustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (lead, cust) => 1)
              .Any() || x.Type == "MultiLead" && DbSet<TblMultiLeadCompanyRequesters>()
              .Join(
                  inner: DbSet<TblMultiLeadCompanies>(),
                  outerKeySelector: req => req.UidMultiLeadCompany,
                  innerKeySelector: mlc => mlc.UidMultiLeadCompany,
                  resultSelector: (req, mlc) => new {
                      req = req,
                      mlc = mlc
                   })
              .Where(<>h__TransparentIdentifier0 => (Guid?)<>h__TransparentIdentifier0.mlc.UidMultiLead == x.UidMultiLead)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: <>h__TransparentIdentifier0 => new {
                      AccountNum = __customerAccountNum_0,
                      CustomerObjectType = <>h__TransparentIdentifier0.req.CustomerObjectType,
                      CustomerObjectId = <>h__TransparentIdentifier0.req.CustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      CustomerObjectType = 0,
                      CustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (<>h__TransparentIdentifier0, cust) => 1)
              .Any())
          .OrderBy(x => 1)
          .Skip(__p_1)
          .Take(__p_2)'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<VwCombinedLeads>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: VwCombinedLeads.BusinessTypeAsJson (string), 0], [Property: VwCombinedLeads.BusinessTypeSearchString (string) Required MaxLength(11), 1], [Property: VwCombinedLeads.BusinessUnitAsJson (string), 2], [Property: VwCombinedLeads.BusinessUnitSearchString (string) Required, 3], [Property: VwCombinedLeads.Closed (bool?), 4], [Property: VwCombinedLeads.ClosedById (string) MaxLength(36), 5], [Property: VwCombinedLeads.ClosedByName (string) MaxLength(256), 6], [Property: VwCombinedLeads.ClosedOn (DateTime?), 7], [Property: VwCombinedLeads.ClosureComments (string) MaxLength(500), 8], [Property: VwCombinedLeads.CommercialResponsiblePeopleAsJson (string), 9], [Property: VwCombinedLeads.CommercialResponsiblePeopleSearchString (string), 10], [Property: VwCombinedLeads.ContactAsJson (string), 11], [Property: VwCombinedLeads.ContactSearchString (string), 12], [Property: VwCombinedLeads.CreatedById (string) Required MaxLength(36), 13], [Property: VwCombinedLeads.CreatedByName (string) MaxLength(256), 14], [Property: VwCombinedLeads.CreatedOn (DateTime) Required, 15], [Property: VwCombinedLeads.CustomerAsJson (string), 16], [Property: VwCombinedLeads.CustomerSearchString (string), 17], [Property: VwCombinedLeads.EndCustomerAsJson (string), 18], [Property: VwCombinedLeads.EndCustomerSearchString (string), 19], [Property: VwCombinedLeads.LeadSourcesAsJson (string), 20], [Property: VwCombinedLeads.LeadSourcesSearchString (string), 21], [Property: VwCombinedLeads.ModifiedById (string) Required MaxLength(36), 22], [Property: VwCombinedLeads.ModifiedByName (string) MaxLength(256), 23], [Property: VwCombinedLeads.ModifiedOn (DateTime) Required, 24], [Property: VwCombinedLeads.MultiLeadAsJson (string), 25], [Property: VwCombinedLeads.OwnerAsJson (string), 26], [Property: VwCombinedLeads.OwnerSearchString (string), 27], [Property: VwCombinedLeads.Qualified (bool?), 28], [Property: VwCombinedLeads.QualifiedById (string) MaxLength(36), 29], [Property: VwCombinedLeads.QualifiedByName (string) MaxLength(256), 30], [Property: VwCombinedLeads.QualifiedComments (string) MaxLength(500), 31], [Property: VwCombinedLeads.QualifiedOn (DateTime?), 32], [Property: VwCombinedLeads.QualifiedProjectAsJson (string), 33], [Property: VwCombinedLeads.RegardingObjectType (byte) Required, 34], [Property: VwCombinedLeads.Topic (string) MaxLength(50), 35], [Property: VwCombinedLeads.Type (string) Required MaxLength(9), 36], [Property: VwCombinedLeads.UidLead (Guid) Required, 37], [Property: VwCombinedLeads.UidMultiLead (Guid?), 38] }
              SELECT v.BusinessTypeAsJson, v.BusinessTypeSearchString, v.BusinessUnitAsJson, v.BusinessUnitSearchString, v.Closed, v.ClosedById, v.ClosedByName, v.ClosedOn, v.ClosureComments, v.CommercialResponsiblePeopleAsJson, v.CommercialResponsiblePeopleSearchString, v.ContactAsJson, v.ContactSearchString, v.CreatedById, v.CreatedByName, v.CreatedOn, v.CustomerAsJson, v.CustomerSearchString, v.EndCustomerAsJson, v.EndCustomerSearchString, v.LeadSourcesAsJson, v.LeadSourcesSearchString, v.ModifiedById, v.ModifiedByName, v.ModifiedOn, v.MultiLeadAsJson, v.OwnerAsJson, v.OwnerSearchString, v.Qualified, v.QualifiedById, v.QualifiedByName, v.QualifiedComments, v.QualifiedOn, v.QualifiedProjectAsJson, v.RegardingObjectType, v.Topic, v.Type, v.UidLead, v.UidMultiLead
              FROM vw_CombinedLeads AS v
              WHERE ((v.Closed == CAST(1 AS bit)) || (v.Qualified == CAST(1 AS bit))) && (((v.Type == N'Lead') && (EXISTS (
                  SELECT 1
                  FROM tblLeads AS t
                  INNER JOIN tblCustomerSharedData AS t0 ON ((@__customerAccountNum_0 == t0.AccountNum) && (t.CustomerObjectType == CAST(0 AS tinyint))) && (t.CustomerObjectId == t0.UidRegardingObject)
                  WHERE t.UidLead == v.UidLead) || EXISTS (
                  SELECT 1
                  FROM tblLeads AS t1
                  INNER JOIN tblCustomerSharedData AS t2 ON ((@__customerAccountNum_0 == t2.AccountNum) && (t1.EndCustomerObjectType == CAST(0 AS tinyint))) && (t1.EndCustomerObjectId == t2.UidRegardingObject)
                  WHERE (t1.UidLead == v.UidLead) && NotEqual(t1.EndCustomerObjectType)))) || ((v.Type == N'MultiLead') && EXISTS (
                  SELECT 1
                  FROM tblMultiLeadCompanyRequesters AS t3
                  INNER JOIN tblMultiLeadCompanies AS t4 ON t3.UidMultiLeadCompany == t4.UidMultiLeadCompany
                  INNER JOIN tblCustomerSharedData AS t5 ON ((@__customerAccountNum_0 == t5.AccountNum) && (t3.CustomerObjectType == CAST(0 AS tinyint))) && (t3.CustomerObjectId == t5.UidRegardingObject)
                  WHERE t4.UidMultiLead == v.UidMultiLead)))
              ORDER BY 1 ASC
              OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, VwCombinedLeads>,
          ImportEntityFramework.MSSQL.CRM.CRMContext,
          False,
          True,
          True
      )'
dbug: Microsoft.EntityFrameworkCore.Database.Command[20103]
      Creating DbCommand for 'ExecuteReader'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20005]
      Creating DbConnection.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20006]
      Created DbConnection. (15ms).
dbug: Microsoft.EntityFrameworkCore.Database.Command[20104]
      Created DbCommand for 'ExecuteReader' (25ms).
dbug: Microsoft.EntityFrameworkCore.Database.Command[20106]
      Initialized DbCommand for 'ExecuteReader' (53ms).
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__customerAccountNum_0='C001796' (Size = 20), @__p_1='0', @__p_2='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE ([v].[Closed] = CAST(1 AS bit) OR [v].[Qualified] = CAST(1 AS bit)) AND (([v].[Type] = N'Lead' AND (EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t]
          INNER JOIN [tblCustomerSharedData] AS [t0] ON @__customerAccountNum_0 = [t0].[AccountNum] AND [t].[CustomerObjectType] = CAST(0 AS tinyint) AND [t].[CustomerObjectId] = [t0].[UidRegardingObject]
          WHERE [t].[UidLead] = [v].[UidLead]) OR EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t1]
          INNER JOIN [tblCustomerSharedData] AS [t2] ON @__customerAccountNum_0 = [t2].[AccountNum] AND [t1].[EndCustomerObjectType] = CAST(0 AS tinyint) AND [t1].[EndCustomerObjectId] = [t2].[UidRegardingObject]
          WHERE [t1].[UidLead] = [v].[UidLead] AND ([t1].[EndCustomerObjectType] IS NOT NULL)))) OR ([v].[Type] = N'MultiLead' AND EXISTS (
          SELECT 1
          FROM [tblMultiLeadCompanyRequesters] AS [t3]
          INNER JOIN [tblMultiLeadCompanies] AS [t4] ON [t3].[UidMultiLeadCompany] = [t4].[UidMultiLeadCompany]
          INNER JOIN [tblCustomerSharedData] AS [t5] ON @__customerAccountNum_0 = [t5].[AccountNum] AND [t3].[CustomerObjectType] = CAST(0 AS tinyint) AND [t3].[CustomerObjectId] = [t5].[UidRegardingObject]
          WHERE [t4].[UidMultiLead] = [v].[UidMultiLead])))
      ORDER BY (SELECT 1)
      OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (221ms) [Parameters=[@__customerAccountNum_0='C001796' (Size = 20), @__p_1='0', @__p_2='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE ([v].[Closed] = CAST(1 AS bit) OR [v].[Qualified] = CAST(1 AS bit)) AND (([v].[Type] = N'Lead' AND (EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t]
          INNER JOIN [tblCustomerSharedData] AS [t0] ON @__customerAccountNum_0 = [t0].[AccountNum] AND [t].[CustomerObjectType] = CAST(0 AS tinyint) AND [t].[CustomerObjectId] = [t0].[UidRegardingObject]
          WHERE [t].[UidLead] = [v].[UidLead]) OR EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t1]
          INNER JOIN [tblCustomerSharedData] AS [t2] ON @__customerAccountNum_0 = [t2].[AccountNum] AND [t1].[EndCustomerObjectType] = CAST(0 AS tinyint) AND [t1].[EndCustomerObjectId] = [t2].[UidRegardingObject]
          WHERE [t1].[UidLead] = [v].[UidLead] AND ([t1].[EndCustomerObjectType] IS NOT NULL)))) OR ([v].[Type] = N'MultiLead' AND EXISTS (
          SELECT 1
          FROM [tblMultiLeadCompanyRequesters] AS [t3]
          INNER JOIN [tblMultiLeadCompanies] AS [t4] ON [t3].[UidMultiLeadCompany] = [t4].[UidMultiLeadCompany]
          INNER JOIN [tblCustomerSharedData] AS [t5] ON @__customerAccountNum_0 = [t5].[AccountNum] AND [t3].[CustomerObjectType] = CAST(0 AS tinyint) AND [t3].[CustomerObjectId] = [t5].[UidRegardingObject]
          WHERE [t4].[UidMultiLead] = [v].[UidMultiLead])))
      ORDER BY (SELECT 1)
      OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
dbug: Microsoft.EntityFrameworkCore.Database.Command[20301]
      Closing data reader to 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader for 'OURDATABASE' on server 'OURSERVER' is being disposed after spending 4284ms reading results.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'OURDATABASE' on server 'OURSERVER' (4ms).
ToListAsync:5377,4998 ms
dbug: Microsoft.EntityFrameworkCore.Query[10111]
      Compiling query expression:
      'DbSet<VwCombinedLeads>()
          .AsNoTracking()
          .Where(x => x.Closed == (bool?)True || x.Qualified == (bool?)True)
          .Where(x => x.Type == "Lead" && DbSet<TblLeads>()
              .Where(lead => lead.UidLead == x.UidLead)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: lead => new {
                      AccountNum = __customerAccountNum_0,
                      CustomerObjectType = lead.CustomerObjectType,
                      CustomerObjectId = lead.CustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      CustomerObjectType = 0,
                      CustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (lead, cust) => 1)
              .Any() || DbSet<TblLeads>()
              .Where(lead => lead.UidLead == x.UidLead && lead.EndCustomerObjectType.HasValue)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: lead => new {
                      AccountNum = __customerAccountNum_0,
                      EndCustomerObjectType = lead.EndCustomerObjectType.Value,
                      EndCustomerObjectId = lead.EndCustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      EndCustomerObjectType = 0,
                      EndCustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (lead, cust) => 1)
              .Any() || x.Type == "MultiLead" && DbSet<TblMultiLeadCompanyRequesters>()
              .Join(
                  inner: DbSet<TblMultiLeadCompanies>(),
                  outerKeySelector: req => req.UidMultiLeadCompany,
                  innerKeySelector: mlc => mlc.UidMultiLeadCompany,
                  resultSelector: (req, mlc) => new {
                      req = req,
                      mlc = mlc
                   })
              .Where(<>h__TransparentIdentifier0 => (Guid?)<>h__TransparentIdentifier0.mlc.UidMultiLead == x.UidMultiLead)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: <>h__TransparentIdentifier0 => new {
                      AccountNum = __customerAccountNum_0,
                      CustomerObjectType = <>h__TransparentIdentifier0.req.CustomerObjectType,
                      CustomerObjectId = <>h__TransparentIdentifier0.req.CustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      CustomerObjectType = 0,
                      CustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (<>h__TransparentIdentifier0, cust) => 1)
              .Any())
          .Where(x => __problematicIds_1.Contains(x.UidLead))
          .OrderBy(x => 1)
          .Skip(__p_2)
          .Take(__p_3)'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<VwCombinedLeads>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: VwCombinedLeads.BusinessTypeAsJson (string), 0], [Property: VwCombinedLeads.BusinessTypeSearchString (string) Required MaxLength(11), 1], [Property: VwCombinedLeads.BusinessUnitAsJson (string), 2], [Property: VwCombinedLeads.BusinessUnitSearchString (string) Required, 3], [Property: VwCombinedLeads.Closed (bool?), 4], [Property: VwCombinedLeads.ClosedById (string) MaxLength(36), 5], [Property: VwCombinedLeads.ClosedByName (string) MaxLength(256), 6], [Property: VwCombinedLeads.ClosedOn (DateTime?), 7], [Property: VwCombinedLeads.ClosureComments (string) MaxLength(500), 8], [Property: VwCombinedLeads.CommercialResponsiblePeopleAsJson (string), 9], [Property: VwCombinedLeads.CommercialResponsiblePeopleSearchString (string), 10], [Property: VwCombinedLeads.ContactAsJson (string), 11], [Property: VwCombinedLeads.ContactSearchString (string), 12], [Property: VwCombinedLeads.CreatedById (string) Required MaxLength(36), 13], [Property: VwCombinedLeads.CreatedByName (string) MaxLength(256), 14], [Property: VwCombinedLeads.CreatedOn (DateTime) Required, 15], [Property: VwCombinedLeads.CustomerAsJson (string), 16], [Property: VwCombinedLeads.CustomerSearchString (string), 17], [Property: VwCombinedLeads.EndCustomerAsJson (string), 18], [Property: VwCombinedLeads.EndCustomerSearchString (string), 19], [Property: VwCombinedLeads.LeadSourcesAsJson (string), 20], [Property: VwCombinedLeads.LeadSourcesSearchString (string), 21], [Property: VwCombinedLeads.ModifiedById (string) Required MaxLength(36), 22], [Property: VwCombinedLeads.ModifiedByName (string) MaxLength(256), 23], [Property: VwCombinedLeads.ModifiedOn (DateTime) Required, 24], [Property: VwCombinedLeads.MultiLeadAsJson (string), 25], [Property: VwCombinedLeads.OwnerAsJson (string), 26], [Property: VwCombinedLeads.OwnerSearchString (string), 27], [Property: VwCombinedLeads.Qualified (bool?), 28], [Property: VwCombinedLeads.QualifiedById (string) MaxLength(36), 29], [Property: VwCombinedLeads.QualifiedByName (string) MaxLength(256), 30], [Property: VwCombinedLeads.QualifiedComments (string) MaxLength(500), 31], [Property: VwCombinedLeads.QualifiedOn (DateTime?), 32], [Property: VwCombinedLeads.QualifiedProjectAsJson (string), 33], [Property: VwCombinedLeads.RegardingObjectType (byte) Required, 34], [Property: VwCombinedLeads.Topic (string) MaxLength(50), 35], [Property: VwCombinedLeads.Type (string) Required MaxLength(9), 36], [Property: VwCombinedLeads.UidLead (Guid) Required, 37], [Property: VwCombinedLeads.UidMultiLead (Guid?), 38] }
              SELECT v.BusinessTypeAsJson, v.BusinessTypeSearchString, v.BusinessUnitAsJson, v.BusinessUnitSearchString, v.Closed, v.ClosedById, v.ClosedByName, v.ClosedOn, v.ClosureComments, v.CommercialResponsiblePeopleAsJson, v.CommercialResponsiblePeopleSearchString, v.ContactAsJson, v.ContactSearchString, v.CreatedById, v.CreatedByName, v.CreatedOn, v.CustomerAsJson, v.CustomerSearchString, v.EndCustomerAsJson, v.EndCustomerSearchString, v.LeadSourcesAsJson, v.LeadSourcesSearchString, v.ModifiedById, v.ModifiedByName, v.ModifiedOn, v.MultiLeadAsJson, v.OwnerAsJson, v.OwnerSearchString, v.Qualified, v.QualifiedById, v.QualifiedByName, v.QualifiedComments, v.QualifiedOn, v.QualifiedProjectAsJson, v.RegardingObjectType, v.Topic, v.Type, v.UidLead, v.UidMultiLead
              FROM vw_CombinedLeads AS v
              WHERE (((v.Closed == CAST(1 AS bit)) || (v.Qualified == CAST(1 AS bit))) && (((v.Type == N'Lead') && (EXISTS (
                  SELECT 1
                  FROM tblLeads AS t
                  INNER JOIN tblCustomerSharedData AS t0 ON ((@__customerAccountNum_0 == t0.AccountNum) && (t.CustomerObjectType == CAST(0 AS tinyint))) && (t.CustomerObjectId == t0.UidRegardingObject)
                  WHERE t.UidLead == v.UidLead) || EXISTS (
                  SELECT 1
                  FROM tblLeads AS t1
                  INNER JOIN tblCustomerSharedData AS t2 ON ((@__customerAccountNum_0 == t2.AccountNum) && (t1.EndCustomerObjectType == CAST(0 AS tinyint))) && (t1.EndCustomerObjectId == t2.UidRegardingObject)
                  WHERE (t1.UidLead == v.UidLead) && NotEqual(t1.EndCustomerObjectType)))) || ((v.Type == N'MultiLead') && EXISTS (
                  SELECT 1
                  FROM tblMultiLeadCompanyRequesters AS t3
                  INNER JOIN tblMultiLeadCompanies AS t4 ON t3.UidMultiLeadCompany == t4.UidMultiLeadCompany
                  INNER JOIN tblCustomerSharedData AS t5 ON ((@__customerAccountNum_0 == t5.AccountNum) && (t3.CustomerObjectType == CAST(0 AS tinyint))) && (t3.CustomerObjectId == t5.UidRegardingObject)
                  WHERE t4.UidMultiLead == v.UidMultiLead)))) && v.UidLead IN (@__problematicIds_1)
              ORDER BY 1 ASC
              OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, VwCombinedLeads>,
          ImportEntityFramework.MSSQL.CRM.CRMContext,
          False,
          True,
          True
      )'
dbug: Microsoft.EntityFrameworkCore.Database.Command[20103]
      Creating DbCommand for 'ExecuteReader'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20104]
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: Microsoft.EntityFrameworkCore.Database.Command[20106]
      Initialized DbCommand for 'ExecuteReader' (0ms).
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__customerAccountNum_0='C001796' (Size = 20), @__p_2='0', @__p_3='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE ([v].[Closed] = CAST(1 AS bit) OR [v].[Qualified] = CAST(1 AS bit)) AND (([v].[Type] = N'Lead' AND (EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t]
          INNER JOIN [tblCustomerSharedData] AS [t0] ON @__customerAccountNum_0 = [t0].[AccountNum] AND [t].[CustomerObjectType] = CAST(0 AS tinyint) AND [t].[CustomerObjectId] = [t0].[UidRegardingObject]
          WHERE [t].[UidLead] = [v].[UidLead]) OR EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t1]
          INNER JOIN [tblCustomerSharedData] AS [t2] ON @__customerAccountNum_0 = [t2].[AccountNum] AND [t1].[EndCustomerObjectType] = CAST(0 AS tinyint) AND [t1].[EndCustomerObjectId] = [t2].[UidRegardingObject]
          WHERE [t1].[UidLead] = [v].[UidLead] AND ([t1].[EndCustomerObjectType] IS NOT NULL)))) OR ([v].[Type] = N'MultiLead' AND EXISTS (
          SELECT 1
          FROM [tblMultiLeadCompanyRequesters] AS [t3]
          INNER JOIN [tblMultiLeadCompanies] AS [t4] ON [t3].[UidMultiLeadCompany] = [t4].[UidMultiLeadCompany]
          INNER JOIN [tblCustomerSharedData] AS [t5] ON @__customerAccountNum_0 = [t5].[AccountNum] AND [t3].[CustomerObjectType] = CAST(0 AS tinyint) AND [t3].[CustomerObjectId] = [t5].[UidRegardingObject]
          WHERE [t4].[UidMultiLead] = [v].[UidMultiLead]))) AND [v].[UidLead] IN ('51c4700f-8db7-e911-a976-000d3a391e8e', '37995dae-5bea-ed11-a056-005056b68f3f', '0b6b2a12-c29d-ee11-a061-005056b68f3f', '353c92c4-95af-ee11-a062-005056b68f3f', '1cc7c6e0-5ef2-ee11-a065-005056b68f3f', '91c75eb8-9a66-ef11-a06a-005056b68f3f', 'e2c81ecf-16d9-e911-a812-000d3a4a11b4', '74a26ac1-9b76-eb11-a812-0022488069ba', '6d4d4374-86de-ed11-a055-005056b68f3f', 'c2f40dac-a4a5-ea11-a812-000d3a2ab5a1', '8c27811e-33b5-e911-a976-000d3a391e8e')
      ORDER BY (SELECT 1)
      OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[@__customerAccountNum_0='C001796' (Size = 20), @__p_2='0', @__p_3='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE ([v].[Closed] = CAST(1 AS bit) OR [v].[Qualified] = CAST(1 AS bit)) AND (([v].[Type] = N'Lead' AND (EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t]
          INNER JOIN [tblCustomerSharedData] AS [t0] ON @__customerAccountNum_0 = [t0].[AccountNum] AND [t].[CustomerObjectType] = CAST(0 AS tinyint) AND [t].[CustomerObjectId] = [t0].[UidRegardingObject]
          WHERE [t].[UidLead] = [v].[UidLead]) OR EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t1]
          INNER JOIN [tblCustomerSharedData] AS [t2] ON @__customerAccountNum_0 = [t2].[AccountNum] AND [t1].[EndCustomerObjectType] = CAST(0 AS tinyint) AND [t1].[EndCustomerObjectId] = [t2].[UidRegardingObject]
          WHERE [t1].[UidLead] = [v].[UidLead] AND ([t1].[EndCustomerObjectType] IS NOT NULL)))) OR ([v].[Type] = N'MultiLead' AND EXISTS (
          SELECT 1
          FROM [tblMultiLeadCompanyRequesters] AS [t3]
          INNER JOIN [tblMultiLeadCompanies] AS [t4] ON [t3].[UidMultiLeadCompany] = [t4].[UidMultiLeadCompany]
          INNER JOIN [tblCustomerSharedData] AS [t5] ON @__customerAccountNum_0 = [t5].[AccountNum] AND [t3].[CustomerObjectType] = CAST(0 AS tinyint) AND [t3].[CustomerObjectId] = [t5].[UidRegardingObject]
          WHERE [t4].[UidMultiLead] = [v].[UidMultiLead]))) AND [v].[UidLead] IN ('51c4700f-8db7-e911-a976-000d3a391e8e', '37995dae-5bea-ed11-a056-005056b68f3f', '0b6b2a12-c29d-ee11-a061-005056b68f3f', '353c92c4-95af-ee11-a062-005056b68f3f', '1cc7c6e0-5ef2-ee11-a065-005056b68f3f', '91c75eb8-9a66-ef11-a06a-005056b68f3f', 'e2c81ecf-16d9-e911-a812-000d3a4a11b4', '74a26ac1-9b76-eb11-a812-0022488069ba', '6d4d4374-86de-ed11-a055-005056b68f3f', 'c2f40dac-a4a5-ea11-a812-000d3a2ab5a1', '8c27811e-33b5-e911-a976-000d3a391e8e')
      ORDER BY (SELECT 1)
      OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY
dbug: Microsoft.EntityFrameworkCore.Database.Command[20301]
      Closing data reader to 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader for 'OURDATABASE' on server 'OURSERVER' is being disposed after spending 3ms reading results.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'OURDATABASE' on server 'OURSERVER'.
Double where clause ToListAsync via ids:53,3699 ms
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'OURDATABASE' on server 'OURSERVER' (0ms).
dbug: Microsoft.EntityFrameworkCore.Query[10111]
      Compiling query expression:
      'DbSet<VwCombinedLeads>()
          .Where(x => __problematicIds_0.Contains(x.UidLead))
          .OrderBy(x => 1)
          .Skip(__p_1)
          .Take(__p_2)'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<VwCombinedLeads>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: VwCombinedLeads.BusinessTypeAsJson (string), 0], [Property: VwCombinedLeads.BusinessTypeSearchString (string) Required MaxLength(11), 1], [Property: VwCombinedLeads.BusinessUnitAsJson (string), 2], [Property: VwCombinedLeads.BusinessUnitSearchString (string) Required, 3], [Property: VwCombinedLeads.Closed (bool?), 4], [Property: VwCombinedLeads.ClosedById (string) MaxLength(36), 5], [Property: VwCombinedLeads.ClosedByName (string) MaxLength(256), 6], [Property: VwCombinedLeads.ClosedOn (DateTime?), 7], [Property: VwCombinedLeads.ClosureComments (string) MaxLength(500), 8], [Property: VwCombinedLeads.CommercialResponsiblePeopleAsJson (string), 9], [Property: VwCombinedLeads.CommercialResponsiblePeopleSearchString (string), 10], [Property: VwCombinedLeads.ContactAsJson (string), 11], [Property: VwCombinedLeads.ContactSearchString (string), 12], [Property: VwCombinedLeads.CreatedById (string) Required MaxLength(36), 13], [Property: VwCombinedLeads.CreatedByName (string) MaxLength(256), 14], [Property: VwCombinedLeads.CreatedOn (DateTime) Required, 15], [Property: VwCombinedLeads.CustomerAsJson (string), 16], [Property: VwCombinedLeads.CustomerSearchString (string), 17], [Property: VwCombinedLeads.EndCustomerAsJson (string), 18], [Property: VwCombinedLeads.EndCustomerSearchString (string), 19], [Property: VwCombinedLeads.LeadSourcesAsJson (string), 20], [Property: VwCombinedLeads.LeadSourcesSearchString (string), 21], [Property: VwCombinedLeads.ModifiedById (string) Required MaxLength(36), 22], [Property: VwCombinedLeads.ModifiedByName (string) MaxLength(256), 23], [Property: VwCombinedLeads.ModifiedOn (DateTime) Required, 24], [Property: VwCombinedLeads.MultiLeadAsJson (string), 25], [Property: VwCombinedLeads.OwnerAsJson (string), 26], [Property: VwCombinedLeads.OwnerSearchString (string), 27], [Property: VwCombinedLeads.Qualified (bool?), 28], [Property: VwCombinedLeads.QualifiedById (string) MaxLength(36), 29], [Property: VwCombinedLeads.QualifiedByName (string) MaxLength(256), 30], [Property: VwCombinedLeads.QualifiedComments (string) MaxLength(500), 31], [Property: VwCombinedLeads.QualifiedOn (DateTime?), 32], [Property: VwCombinedLeads.QualifiedProjectAsJson (string), 33], [Property: VwCombinedLeads.RegardingObjectType (byte) Required, 34], [Property: VwCombinedLeads.Topic (string) MaxLength(50), 35], [Property: VwCombinedLeads.Type (string) Required MaxLength(9), 36], [Property: VwCombinedLeads.UidLead (Guid) Required, 37], [Property: VwCombinedLeads.UidMultiLead (Guid?), 38] }
              SELECT v.BusinessTypeAsJson, v.BusinessTypeSearchString, v.BusinessUnitAsJson, v.BusinessUnitSearchString, v.Closed, v.ClosedById, v.ClosedByName, v.ClosedOn, v.ClosureComments, v.CommercialResponsiblePeopleAsJson, v.CommercialResponsiblePeopleSearchString, v.ContactAsJson, v.ContactSearchString, v.CreatedById, v.CreatedByName, v.CreatedOn, v.CustomerAsJson, v.CustomerSearchString, v.EndCustomerAsJson, v.EndCustomerSearchString, v.LeadSourcesAsJson, v.LeadSourcesSearchString, v.ModifiedById, v.ModifiedByName, v.ModifiedOn, v.MultiLeadAsJson, v.OwnerAsJson, v.OwnerSearchString, v.Qualified, v.QualifiedById, v.QualifiedByName, v.QualifiedComments, v.QualifiedOn, v.QualifiedProjectAsJson, v.RegardingObjectType, v.Topic, v.Type, v.UidLead, v.UidMultiLead
              FROM vw_CombinedLeads AS v
              WHERE v.UidLead IN (@__problematicIds_0)
              ORDER BY 1 ASC
              OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, VwCombinedLeads>,
          ImportEntityFramework.MSSQL.CRM.CRMContext,
          False,
          True,
          True
      )'
dbug: Microsoft.EntityFrameworkCore.Database.Command[20103]
      Creating DbCommand for 'ExecuteReader'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20104]
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: Microsoft.EntityFrameworkCore.Database.Command[20106]
      Initialized DbCommand for 'ExecuteReader' (0ms).
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__p_1='0', @__p_2='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE [v].[UidLead] IN ('51c4700f-8db7-e911-a976-000d3a391e8e', '37995dae-5bea-ed11-a056-005056b68f3f', '0b6b2a12-c29d-ee11-a061-005056b68f3f', '353c92c4-95af-ee11-a062-005056b68f3f', '1cc7c6e0-5ef2-ee11-a065-005056b68f3f', '91c75eb8-9a66-ef11-a06a-005056b68f3f', 'e2c81ecf-16d9-e911-a812-000d3a4a11b4', '74a26ac1-9b76-eb11-a812-0022488069ba', '6d4d4374-86de-ed11-a055-005056b68f3f', 'c2f40dac-a4a5-ea11-a812-000d3a2ab5a1', '8c27811e-33b5-e911-a976-000d3a391e8e')
      ORDER BY (SELECT 1)
      OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[@__p_1='0', @__p_2='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE [v].[UidLead] IN ('51c4700f-8db7-e911-a976-000d3a391e8e', '37995dae-5bea-ed11-a056-005056b68f3f', '0b6b2a12-c29d-ee11-a061-005056b68f3f', '353c92c4-95af-ee11-a062-005056b68f3f', '1cc7c6e0-5ef2-ee11-a065-005056b68f3f', '91c75eb8-9a66-ef11-a06a-005056b68f3f', 'e2c81ecf-16d9-e911-a812-000d3a4a11b4', '74a26ac1-9b76-eb11-a812-0022488069ba', '6d4d4374-86de-ed11-a055-005056b68f3f', 'c2f40dac-a4a5-ea11-a812-000d3a2ab5a1', '8c27811e-33b5-e911-a976-000d3a391e8e')
      ORDER BY (SELECT 1)
      OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
dbug: Microsoft.EntityFrameworkCore.Database.Command[20301]
      Closing data reader to 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader for 'OURDATABASE' on server 'OURSERVER' is being disposed after spending 1ms reading results.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'OURDATABASE' on server 'OURSERVER' (0ms).
No where clauses ToListAsync via ids:16,6934 ms
dbug: Microsoft.EntityFrameworkCore.Query[10111]
      Compiling query expression:
      'DbSet<VwCombinedLeads>()
          .AsNoTracking()
          .Where(x => x.Closed == (bool?)True || x.Qualified == (bool?)True)
          .Where(x => x.Type == "Lead" && DbSet<TblLeads>()
              .Where(lead => lead.UidLead == x.UidLead)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: lead => new {
                      AccountNum = __customerAccountNum_0,
                      CustomerObjectType = lead.CustomerObjectType,
                      CustomerObjectId = lead.CustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      CustomerObjectType = 0,
                      CustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (lead, cust) => 1)
              .Any() || DbSet<TblLeads>()
              .Where(lead => lead.UidLead == x.UidLead && lead.EndCustomerObjectType.HasValue)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: lead => new {
                      AccountNum = __customerAccountNum_0,
                      EndCustomerObjectType = lead.EndCustomerObjectType.Value,
                      EndCustomerObjectId = lead.EndCustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      EndCustomerObjectType = 0,
                      EndCustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (lead, cust) => 1)
              .Any() || x.Type == "MultiLead" && DbSet<TblMultiLeadCompanyRequesters>()
              .Join(
                  inner: DbSet<TblMultiLeadCompanies>(),
                  outerKeySelector: req => req.UidMultiLeadCompany,
                  innerKeySelector: mlc => mlc.UidMultiLeadCompany,
                  resultSelector: (req, mlc) => new {
                      req = req,
                      mlc = mlc
                   })
              .Where(<>h__TransparentIdentifier0 => (Guid?)<>h__TransparentIdentifier0.mlc.UidMultiLead == x.UidMultiLead)
              .Join(
                  inner: DbSet<TblCustomerSharedData>(),
                  outerKeySelector: <>h__TransparentIdentifier0 => new {
                      AccountNum = __customerAccountNum_0,
                      CustomerObjectType = <>h__TransparentIdentifier0.req.CustomerObjectType,
                      CustomerObjectId = <>h__TransparentIdentifier0.req.CustomerObjectId
                   },
                  innerKeySelector: cust => new {
                      AccountNum = cust.AccountNum,
                      CustomerObjectType = 0,
                      CustomerObjectId = cust.UidRegardingObject
                   },
                  resultSelector: (<>h__TransparentIdentifier0, cust) => 1)
              .Any())
          .Where(x => True)
          .OrderBy(x => 1)
          .Skip(__p_1)
          .Take(__p_2)'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      Generated query execution expression:
      'queryContext => new SingleQueryingEnumerable<VwCombinedLeads>(
          (RelationalQueryContext)queryContext,
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: VwCombinedLeads.BusinessTypeAsJson (string), 0], [Property: VwCombinedLeads.BusinessTypeSearchString (string) Required MaxLength(11), 1], [Property: VwCombinedLeads.BusinessUnitAsJson (string), 2], [Property: VwCombinedLeads.BusinessUnitSearchString (string) Required, 3], [Property: VwCombinedLeads.Closed (bool?), 4], [Property: VwCombinedLeads.ClosedById (string) MaxLength(36), 5], [Property: VwCombinedLeads.ClosedByName (string) MaxLength(256), 6], [Property: VwCombinedLeads.ClosedOn (DateTime?), 7], [Property: VwCombinedLeads.ClosureComments (string) MaxLength(500), 8], [Property: VwCombinedLeads.CommercialResponsiblePeopleAsJson (string), 9], [Property: VwCombinedLeads.CommercialResponsiblePeopleSearchString (string), 10], [Property: VwCombinedLeads.ContactAsJson (string), 11], [Property: VwCombinedLeads.ContactSearchString (string), 12], [Property: VwCombinedLeads.CreatedById (string) Required MaxLength(36), 13], [Property: VwCombinedLeads.CreatedByName (string) MaxLength(256), 14], [Property: VwCombinedLeads.CreatedOn (DateTime) Required, 15], [Property: VwCombinedLeads.CustomerAsJson (string), 16], [Property: VwCombinedLeads.CustomerSearchString (string), 17], [Property: VwCombinedLeads.EndCustomerAsJson (string), 18], [Property: VwCombinedLeads.EndCustomerSearchString (string), 19], [Property: VwCombinedLeads.LeadSourcesAsJson (string), 20], [Property: VwCombinedLeads.LeadSourcesSearchString (string), 21], [Property: VwCombinedLeads.ModifiedById (string) Required MaxLength(36), 22], [Property: VwCombinedLeads.ModifiedByName (string) MaxLength(256), 23], [Property: VwCombinedLeads.ModifiedOn (DateTime) Required, 24], [Property: VwCombinedLeads.MultiLeadAsJson (string), 25], [Property: VwCombinedLeads.OwnerAsJson (string), 26], [Property: VwCombinedLeads.OwnerSearchString (string), 27], [Property: VwCombinedLeads.Qualified (bool?), 28], [Property: VwCombinedLeads.QualifiedById (string) MaxLength(36), 29], [Property: VwCombinedLeads.QualifiedByName (string) MaxLength(256), 30], [Property: VwCombinedLeads.QualifiedComments (string) MaxLength(500), 31], [Property: VwCombinedLeads.QualifiedOn (DateTime?), 32], [Property: VwCombinedLeads.QualifiedProjectAsJson (string), 33], [Property: VwCombinedLeads.RegardingObjectType (byte) Required, 34], [Property: VwCombinedLeads.Topic (string) MaxLength(50), 35], [Property: VwCombinedLeads.Type (string) Required MaxLength(9), 36], [Property: VwCombinedLeads.UidLead (Guid) Required, 37], [Property: VwCombinedLeads.UidMultiLead (Guid?), 38] }
              SELECT v.BusinessTypeAsJson, v.BusinessTypeSearchString, v.BusinessUnitAsJson, v.BusinessUnitSearchString, v.Closed, v.ClosedById, v.ClosedByName, v.ClosedOn, v.ClosureComments, v.CommercialResponsiblePeopleAsJson, v.CommercialResponsiblePeopleSearchString, v.ContactAsJson, v.ContactSearchString, v.CreatedById, v.CreatedByName, v.CreatedOn, v.CustomerAsJson, v.CustomerSearchString, v.EndCustomerAsJson, v.EndCustomerSearchString, v.LeadSourcesAsJson, v.LeadSourcesSearchString, v.ModifiedById, v.ModifiedByName, v.ModifiedOn, v.MultiLeadAsJson, v.OwnerAsJson, v.OwnerSearchString, v.Qualified, v.QualifiedById, v.QualifiedByName, v.QualifiedComments, v.QualifiedOn, v.QualifiedProjectAsJson, v.RegardingObjectType, v.Topic, v.Type, v.UidLead, v.UidMultiLead
              FROM vw_CombinedLeads AS v
              WHERE ((v.Closed == CAST(1 AS bit)) || (v.Qualified == CAST(1 AS bit))) && (((v.Type == N'Lead') && (EXISTS (
                  SELECT 1
                  FROM tblLeads AS t
                  INNER JOIN tblCustomerSharedData AS t0 ON ((@__customerAccountNum_0 == t0.AccountNum) && (t.CustomerObjectType == CAST(0 AS tinyint))) && (t.CustomerObjectId == t0.UidRegardingObject)
                  WHERE t.UidLead == v.UidLead) || EXISTS (
                  SELECT 1
                  FROM tblLeads AS t1
                  INNER JOIN tblCustomerSharedData AS t2 ON ((@__customerAccountNum_0 == t2.AccountNum) && (t1.EndCustomerObjectType == CAST(0 AS tinyint))) && (t1.EndCustomerObjectId == t2.UidRegardingObject)
                  WHERE (t1.UidLead == v.UidLead) && NotEqual(t1.EndCustomerObjectType)))) || ((v.Type == N'MultiLead') && EXISTS (
                  SELECT 1
                  FROM tblMultiLeadCompanyRequesters AS t3
                  INNER JOIN tblMultiLeadCompanies AS t4 ON t3.UidMultiLeadCompany == t4.UidMultiLeadCompany
                  INNER JOIN tblCustomerSharedData AS t5 ON ((@__customerAccountNum_0 == t5.AccountNum) && (t3.CustomerObjectType == CAST(0 AS tinyint))) && (t3.CustomerObjectId == t5.UidRegardingObject)
                  WHERE t4.UidMultiLead == v.UidMultiLead)))
              ORDER BY 1 ASC
              OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, VwCombinedLeads>,
          ImportEntityFramework.MSSQL.CRM.CRMContext,
          False,
          True,
          True
      )'
dbug: Microsoft.EntityFrameworkCore.Database.Command[20103]
      Creating DbCommand for 'ExecuteReader'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20104]
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: Microsoft.EntityFrameworkCore.Database.Command[20106]
      Initialized DbCommand for 'ExecuteReader' (0ms).
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__customerAccountNum_0='C001796' (Size = 20), @__p_1='0', @__p_2='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE ([v].[Closed] = CAST(1 AS bit) OR [v].[Qualified] = CAST(1 AS bit)) AND (([v].[Type] = N'Lead' AND (EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t]
          INNER JOIN [tblCustomerSharedData] AS [t0] ON @__customerAccountNum_0 = [t0].[AccountNum] AND [t].[CustomerObjectType] = CAST(0 AS tinyint) AND [t].[CustomerObjectId] = [t0].[UidRegardingObject]
          WHERE [t].[UidLead] = [v].[UidLead]) OR EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t1]
          INNER JOIN [tblCustomerSharedData] AS [t2] ON @__customerAccountNum_0 = [t2].[AccountNum] AND [t1].[EndCustomerObjectType] = CAST(0 AS tinyint) AND [t1].[EndCustomerObjectId] = [t2].[UidRegardingObject]
          WHERE [t1].[UidLead] = [v].[UidLead] AND ([t1].[EndCustomerObjectType] IS NOT NULL)))) OR ([v].[Type] = N'MultiLead' AND EXISTS (
          SELECT 1
          FROM [tblMultiLeadCompanyRequesters] AS [t3]
          INNER JOIN [tblMultiLeadCompanies] AS [t4] ON [t3].[UidMultiLeadCompany] = [t4].[UidMultiLeadCompany]
          INNER JOIN [tblCustomerSharedData] AS [t5] ON @__customerAccountNum_0 = [t5].[AccountNum] AND [t3].[CustomerObjectType] = CAST(0 AS tinyint) AND [t3].[CustomerObjectId] = [t5].[UidRegardingObject]
          WHERE [t4].[UidMultiLead] = [v].[UidMultiLead])))
      ORDER BY (SELECT 1)
      OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (205ms) [Parameters=[@__customerAccountNum_0='C001796' (Size = 20), @__p_1='0', @__p_2='100'], CommandType='Text', CommandTimeout='30']
      SELECT [v].[BusinessTypeAsJson], [v].[BusinessTypeSearchString], [v].[BusinessUnitAsJson], [v].[BusinessUnitSearchString], [v].[Closed], [v].[ClosedById], [v].[ClosedByName], [v].[ClosedOn], [v].[ClosureComments], [v].[CommercialResponsiblePeopleAsJson], [v].[CommercialResponsiblePeopleSearchString], [v].[ContactAsJson], [v].[ContactSearchString], [v].[CreatedById], [v].[CreatedByName], [v].[CreatedOn], [v].[CustomerAsJson], [v].[CustomerSearchString], [v].[EndCustomerAsJson], [v].[EndCustomerSearchString], [v].[LeadSourcesAsJson], [v].[LeadSourcesSearchString], [v].[ModifiedById], [v].[ModifiedByName], [v].[ModifiedOn], [v].[MultiLeadAsJson], [v].[OwnerAsJson], [v].[OwnerSearchString], [v].[Qualified], [v].[QualifiedById], [v].[QualifiedByName], [v].[QualifiedComments], [v].[QualifiedOn], [v].[QualifiedProjectAsJson], [v].[RegardingObjectType], [v].[Topic], [v].[Type], [v].[UidLead], [v].[UidMultiLead]
      FROM [vw_CombinedLeads] AS [v]
      WHERE ([v].[Closed] = CAST(1 AS bit) OR [v].[Qualified] = CAST(1 AS bit)) AND (([v].[Type] = N'Lead' AND (EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t]
          INNER JOIN [tblCustomerSharedData] AS [t0] ON @__customerAccountNum_0 = [t0].[AccountNum] AND [t].[CustomerObjectType] = CAST(0 AS tinyint) AND [t].[CustomerObjectId] = [t0].[UidRegardingObject]
          WHERE [t].[UidLead] = [v].[UidLead]) OR EXISTS (
          SELECT 1
          FROM [tblLeads] AS [t1]
          INNER JOIN [tblCustomerSharedData] AS [t2] ON @__customerAccountNum_0 = [t2].[AccountNum] AND [t1].[EndCustomerObjectType] = CAST(0 AS tinyint) AND [t1].[EndCustomerObjectId] = [t2].[UidRegardingObject]
          WHERE [t1].[UidLead] = [v].[UidLead] AND ([t1].[EndCustomerObjectType] IS NOT NULL)))) OR ([v].[Type] = N'MultiLead' AND EXISTS (
          SELECT 1
          FROM [tblMultiLeadCompanyRequesters] AS [t3]
          INNER JOIN [tblMultiLeadCompanies] AS [t4] ON [t3].[UidMultiLeadCompany] = [t4].[UidMultiLeadCompany]
          INNER JOIN [tblCustomerSharedData] AS [t5] ON @__customerAccountNum_0 = [t5].[AccountNum] AND [t3].[CustomerObjectType] = CAST(0 AS tinyint) AND [t3].[CustomerObjectId] = [t5].[UidRegardingObject]
          WHERE [t4].[UidMultiLead] = [v].[UidMultiLead])))
      ORDER BY (SELECT 1)
      OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
dbug: Microsoft.EntityFrameworkCore.Database.Command[20301]
      Closing data reader to 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader for 'OURDATABASE' on server 'OURSERVER' is being disposed after spending 4321ms reading results.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'OURDATABASE' on server 'OURSERVER'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'OURDATABASE' on server 'OURSERVER' (0ms).
Double where clause ToListAsync via 1 == 1:4539,5481 ms
@aldrashan
Copy link
Author

I've tried using a SqlCommand directly to check the slow queries and apparently the DbDataReader's ReadAsync takes a long time for some rows.

ReadAsync took: 1,396 ms
Row 0: 7,5434 ms
ReadAsync took: 0,1762 ms
Row 1: 0,0977 ms
ReadAsync took: 0,0056 ms
Row 2: 0,0466 ms
ReadAsync took: 0,0143 ms
Row 3: 0,1104 ms
ReadAsync took: 0,0044 ms
Row 4: 205,609 ms
ReadAsync took: 0,0123 ms
Row 5: 0,0708 ms
ReadAsync took: 0,0057 ms
Row 6: 718,424 ms
ReadAsync took: 0,0132 ms
Row 7: 0,3351 ms
ReadAsync took: 0,0089 ms
Row 8: 0,0525 ms
ReadAsync took: 0,006 ms
Row 9: 3334,0361 ms
ReadAsync took: 0,0133 ms
Row 10: 0,0479 ms
QueryTime: 4552,8938 ms

@Seabizkit
Copy link

Ur issue I believe is how you have written your linq... And therefor your sql...

My suggestion is write the raw sql which gives you the performance you want ie it has nothing to do with ef.. Then convert that sql into the linq equalivent.

I have seen this so many times...

First start with the sql and then it will help you think about modeling the linq

Take the raw sql that linq gives you and you will get the same bad performance from sql running directly , ruling out everything else aka its the sql which is generated by you.

@aldrashan
Copy link
Author

aldrashan commented Jan 16, 2025 •
edited
Loading

@Seabizkit The issue in that case would be that EF is reporting incorrect query times (or I'm interpretting the log incorrectly).
It's reporting a time of ~200ms to execute the query and taking ~5 seconds to actually read it to C# objects. For 2 out of 4 queries shown there the log is correct.

@roji
Copy link
Member

roji commented Jan 19, 2025

I've tried using a SqlCommand directly to check the slow queries and apparently the DbDataReader's ReadAsync takes a long time for some rows.

This isn't something that has anything to do with EF - it would be something to investigate at the SqlClient level. SqlClient unfortunately indeed has various known issues around async .

I'm also confused, because afterwards you write: "It's reporting a time of ~200ms to execute the query and taking ~5 seconds to actually read it to C# objects.". So I'm still not sure what exactly performance problem you're describing here.

If you want us to investigate anything, you'll have to submit a minimal, runnable repro (a partial screenshot as above does not count).

@Seabizkit
Copy link

U are mistaking compiling the query 200 ms for it executing it.

Like I said, take the raw sql and execute directly with out ef you will find it will take just as long to run +-

Your problem is ur linq and therefor the sql...

@aldrashan
Copy link
Author

I've tried using a SqlCommand directly to check the slow queries and apparently the DbDataReader's ReadAsync takes a long time for some rows.

This isn't something that has anything to do with EF - it would be something to investigate at the SqlClient level. SqlClient unfortunately indeed has various known issues around async .

I'm also confused, because afterwards you write: "It's reporting a time of ~200ms to execute the query and taking ~5 seconds to actually read it to C# objects.". So I'm still not sure what exactly performance problem you're describing here.

If you want us to investigate anything, you'll have to submit a minimal, runnable repro (a partial screenshot as above does not count).

I guess I'm confused about what times EF is actually logging/reporting.
More specifically the Executed DbCommand (7ms).
This is the time it takes to simply do var reader = await command.ExecuteReaderAsync(cancellationToken) then and not the time it takes to read the sql results (i.e. while (await reader.ReadAsync(cancellationToken)) { ... } Console.WriteLine("It took x ms");)?

@roji
Copy link
Member

roji commented Jan 21, 2025

Executed DbCommand reports the time that took to execute await command.ExecuteReaderAsync().

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@roji roji

Labels
area-perf area-query
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@roji @Seabizkit @AndriySvyryd @aldrashan