Ask a Dev

Any time we filter in SQL on a VARCHAR column, it's best to send the parameter as an ANSI string. That way it can use the index more effectively. Dapper assumes NVARCHAR, and not all fields are NVARCHAR in Disco.

That looks like this:

            var parameters = new
            {
                CommissionBeginDate = commissionBeginDate.Date,
                CommissionEndDate = commissionEndDate.Date,
                // BonusGroup = bonusGroup, // REPLACE with the line below
                BonusGroup = new DbString { IsAnsi = true, Length = 100, Value = bonusGroup };
            };
            var query = @"Select AssociateID,Sum(Amount) Amount
	                        From CRM_CommissionHistory 
	                        Where [Group] = @BonusGroup
	                        And ComPeriodID in (Select recordnumber From CRM_CommissionPeriods 
                                                    Where Begindate >= @CommissionBeginDate 
                                                    And EndDate <= @CommissionEndDate 
                                                    And PeriodName='ClientWeekly')
	                        Group By AssociateID
                        ";
            return dbHelper.Query<AssociateTeamRoyaltyBonus>(query, parameters).ToList();

This will aid in the performance of the query. We mention it only because there are a lot expensive queries that go against the CRM_CommissionAssociateValues, CommissionPeriod, and Stats tables, that will benefit greatly from this addition.