Hi,

Here's my info for versions:
*LLBLGen V3.1 final March 12th, 2012 *SD.LLBLGen.Pro.ORMSupportClasses.NET2.0.dll 11/6/2012 12:04pm 2.1.12.1015 *SQL Server

This SQL is generated from the following VB.NET code using the LLBLGen Adapter.

We are seeing some performance issues, and this query seems like it might be the slow one. I notice that the query is a "DISTINCT" SELECT. From what I've read, DISTINCTs maybe can be slow (for like hundreds of thousands of records)?

Can you see why the LLBLGen code is generating the DISTINCT command?

Background: There are two tables, an employee (EMP_GENERAL) table and a table that contains hashes of the employee's lastname and ssn (employee identifier) called EMP_GENERAL_HASH.

THE EMP_GENERAL_HASH is used for a quick lookup of searching the EMP_GENERAL for partial-strings of the employee's lastname or ssn.

Thanks!

(from SQL Server Profiler)

exec sp_executesql N'SELECT DISTINCT [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[CURRENT_APPOINTMENT_DATE] AS [CurrentAppointmentDate], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[CUSTOM_ID] AS [CustomId], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[DATE_OF_BIRTH] AS [DateOfBirth], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[DOMAIN_ID] AS [DomainId], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[FEGLI_CODE] AS [FegliCode], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[FEHB_CODE] AS [FehbCode], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[FIRST_NAME] AS [FirstName], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[ID] AS [Id], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[LAST_NAME] AS [LastName], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[MIDDLE_INITIAL] AS [MiddleInitial], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[PAY_EFFECTIVE_DATE] AS [PayEffectiveDate], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[PAY_RATE] AS [PayRate], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[PROFILE_LAST_UPDATED_TIMESTAMP] AS [ProfileLastUpdatedTimeStamp], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[SSN] AS [Ssn], [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[TOD_EFFECTIVE_DATE] AS [TodEffectiveDate] FROM (( [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL]  INNER JOIN [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL_HASH]  ON  [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[ID]=[GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL_HASH].[EMPLOYEE_ID]) INNER JOIN [GRB_ASSIST_DATABASE].[GRB_ASSIST].[DOMAIN]  ON  [GRB_ASSIST_DATABASE].[GRB_ASSIST].[DOMAIN].[ID]=[GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL_HASH].[DOMAIN_ID]) WHERE ( ( ( [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL].[DOMAIN_ID] = @p1) AND ( [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL_HASH].[HASH] = @p2 AND [GRB_ASSIST_DATABASE].[GRB_ASSIST].[EMP_GENERAL_HASH].[NUMBER_OF_CHARS_IN_STRING] = @p3)))',N'@p1 int,@p2 varchar(128),@p3 int',@p1=1,@p2='7payoj/nDhVN2kCTmTFEBLZk0iqPFyviUQSYg1bFuNVfrz6Q1ji8GpGwEd2T6AyZNsWkc3WS8cA89LN8wMTC0A==',@p3=3

VB.NET CODE:

      Dim r As New System.Text.RegularExpressions.Regex("^([1-9]|0[1-9]|1[012])[- /.]([1-9]|0[1-9]|[12][0-9]|3[01])[- /.]([0-9]{2}|[0-9]{4})$")
        Dim r2 As New System.Text.RegularExpressions.Regex("^([0-9]{3}-[0-9]{2}-[0-9]{4}|[0-9]{9})$")
        Dim matchDate As System.Text.RegularExpressions.Match = r.Match(search)
        Dim matchSsn As System.Text.RegularExpressions.Match = r2.Match(search)

        Using adapter As SD.LLBLGen.Pro.ORMSupportClasses.IDataAccessAdapter = Grb.Framework.Business.Lower.FactoryAdapter.FactoryAdapter.GetDataAccessAdapter(m_adapterConnection)
          Dim filter As New SD.LLBLGen.Pro.ORMSupportClasses.RelationPredicateBucket()
          filter.Relations.Add(Grb.Framework.Business.Lower.EntityClasses.EmpGeneralHashEntity.Relations.EmpGeneralEntityUsingEmployeeId)
          filter.Relations.Add(Grb.Framework.Business.Lower.EntityClasses.EmpGeneralHashEntity.Relations.DomainEntityUsingDomainId)
          Dim predicateExpression As New SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression()
          predicateExpression.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.DomainId = domainId)

          Dim generalPredicateExpression As New SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression()
          If (String.Equals(search, StringConstant.General.Asterisk, StringComparison.InvariantCulture) = False) Then
            Dim employeeGeneralHash As Grb.PlugIn.Assist.Business.Common.EmployeeGeneralHash
            employeeGeneralHash = Grb.PlugIn.Assist.Business.Common.EmployeeHashGenerator.GenerateHash(search.ToLower(Globalization.CultureInfo.InvariantCulture), search.Length)
            generalPredicateExpression.AddWithAnd(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralHashFields.Hash = employeeGeneralHash.Hash)
            generalPredicateExpression.AddWithAnd(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralHashFields.NumberOfCharsInString = employeeGeneralHash.NumberOfCharsInString)

            'check if the search is date
            If (matchDate.Success) Then
              Dim dateValue As Date = Convert.ToDateTime(search, System.Globalization.CultureInfo.InvariantCulture)
              Dim dateHash As Grb.PlugIn.Assist.Business.Common.EmployeeGeneralHash
              dateHash = Grb.PlugIn.Assist.Business.Common.EmployeeHashGenerator.GenerateHash(dateValue.ToString(StringConstant.General.DefaultDateFormat, System.Globalization.CultureInfo.InvariantCulture), 10)
              Dim datePredicateExpression As SD.LLBLGen.Pro.ORMSupportClasses.IPredicateExpression = New SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression()
              datePredicateExpression.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralHashFields.Hash = dateHash.Hash)
              datePredicateExpression.AddWithAnd(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralHashFields.NumberOfCharsInString = dateHash.NumberOfCharsInString)
              generalPredicateExpression.AddWithOr(datePredicateExpression)

              'check if the search is SSN
            ElseIf (matchSsn.Success) Then
              Dim ssn As String = search
              Dim ssnHash As Grb.PlugIn.Assist.Business.Common.EmployeeGeneralHash
              If search.Length = 11 Then
                'Return theSSN without the dashes           
                ssn = search.Replace("-", "")
              End If
              ssnHash = Grb.PlugIn.Assist.Business.Common.EmployeeHashGenerator.GenerateHash(ssn, ssn.Length)
              Dim ssnPredicateExpression As SD.LLBLGen.Pro.ORMSupportClasses.IPredicateExpression = New SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression()
              ssnPredicateExpression.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralHashFields.Hash = ssnHash.Hash)
              ssnPredicateExpression.AddWithAnd(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralHashFields.NumberOfCharsInString = ssnHash.NumberOfCharsInString)
              generalPredicateExpression.AddWithOr(ssnPredicateExpression)

            End If

          End If

          filter.PredicateExpression.Add(predicateExpression).AddWithAnd( generalPredicateExpression)

          Dim includedFieldList As New SD.LLBLGen.Pro.ORMSupportClasses.IncludeFieldsList
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.Id)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.PayRate)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.FegliCode)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.FehbCode)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.CurrentAppointmentDate)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.TodEffectiveDate)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.PayEffectiveDate)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.LastName)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.FirstName)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.MiddleInitial)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.Ssn)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.CustomId)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.DateOfBirth)
          includedFieldList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.ProfileLastUpdatedTimeStamp)

          adapter.FetchEntityCollection(empGeneralEntityCollection, filter, 0, Nothing, Nothing, includedFieldList)
        End Using

Database tables:

CREATE TABLE [GRB_ASSIST].[EMP_GENERAL](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DOMAIN_ID] [int] NOT NULL,
    [SSN] [varchar](64) NULL,
    [LAST_NAME] [varchar](192) NOT NULL,
    [FIRST_NAME] [varchar](192) NOT NULL,
    [MIDDLE_INITIAL] [varchar](64) NULL,
    [CUSTOM_ID] [varchar](128) NULL,
    [DATE_OF_BIRTH] [datetime] NOT NULL,
    [SPOUSE_DATE_OF_BIRTH] [datetime] NULL,
    [NUMBER_OF_CHILDREN] [int] NULL,
    [CURRENT_APPOINTMENT_DATE] [datetime] NULL,
...
 CONSTRAINT [EMP_GENERAL_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]




------------------------------



CREATE TABLE [GRB_ASSIST].[EMP_GENERAL_HASH](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DOMAIN_ID] [int] NOT NULL,
    [EMPLOYEE_ID] [int] NOT NULL,
    [HASH] [varchar](128) NOT NULL,
    [NUMBER_OF_CHARS_IN_STRING] [int] NOT NULL,
    [CREATED_TIMESTAMP] [datetime] NULL,
    [LAST_UPDATED_TIMESTAMP] [datetime] NULL,
 CONSTRAINT [EMP_GENERAL_HASH_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [emp_general_hash_unique_chk] UNIQUE NONCLUSTERED 
(
    [EMPLOYEE_ID] ASC,
    [HASH] ASC,
    [NUMBER_OF_CHARS_IN_STRING] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [GRB_ASSIST].[EMP_GENERAL_HASH]  WITH CHECK ADD  CONSTRAINT [emp_gen_hash_domain_id_fk] FOREIGN KEY([DOMAIN_ID])
REFERENCES [GRB_ASSIST].[DOMAIN] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [GRB_ASSIST].[EMP_GENERAL_HASH] CHECK CONSTRAINT [emp_gen_hash_domain_id_fk]
GO

ALTER TABLE [GRB_ASSIST].[EMP_GENERAL_HASH]  WITH CHECK ADD  CONSTRAINT [emp_gen_hash_emp_id_fk] FOREIGN KEY([EMPLOYEE_ID])
REFERENCES [GRB_ASSIST].[EMP_GENERAL] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [GRB_ASSIST].[EMP_GENERAL_HASH] CHECK CONSTRAINT [emp_gen_hash_emp_id_fk]
GO

Hi Andy,

Are you sure that this is the slow query? How slow? DISTINCT is emitted to guarantee uniqueness. Since you are fetching and EntityCollection (no duplicates allowed) and you are adding relations.

Use a profiler, without it, you don't know what is exactly slow, and why it's slow. If the SQL query is slow, use the sql profiler to find why it takes that much time, check execution plans and see whether you need to add an index somewhere perhaps. DISTINCT isn't slowing queries down noticeably.

Thanks for the suggestion.

I ran through the profiler and it appears to be doing a full table scan for table EMP_GENERAL_HASH table. Based on the query, it looks like we're missing individual indexes on the EMP_GENERAL_HASH table for each of the EMPLOYEE_ID, HASH and NUMBER_OF_CHARS_IN_STRING columns.

We have a three-column index on EMPLOYEE_ID, HASH, NUMBER_OF_CHARS_IN_STRING...but I suspect it needs to be on the individual columns.

Please try the individual indexes one by one, and see if the issue gets solved.

greenstone wrote:

Thanks for the suggestion.

I ran through the profiler and it appears to be doing a full table scan for table EMP_GENERAL_HASH table. Based on the query, it looks like we're missing individual indexes on the EMP_GENERAL_HASH table for each of the EMPLOYEE_ID, HASH and NUMBER_OF_CHARS_IN_STRING columns.

We have a three-column index on EMPLOYEE_ID, HASH, NUMBER_OF_CHARS_IN_STRING...but I suspect it needs to be on the individual columns.

This is often a thing that's overlooked indeed. The FK as a whole gets an index, but queries which filter on a subset of the columns in the FK will not use that index. So do as Walaa, suggested, add an index for the columns individually, as your where clause filters on DOMAIN_ID, not EMPLOYEE_ID, so the index you have on the full FK isn't used in this case.

It might be, an index on these 3 fields as a whole is enough, experiment.

Thanks! Adding the indexes to the individual columns fixed the issue.