bobrufenacht
commented
11 months ago exec sp_executesql N'SELECT [Limit2].[Id] AS [Id], [Limit2].[ParentLocationId] AS [ParentLocationId], [Limit2].[Name] AS [Name], [Limit2].[IsActive] AS [IsActive], [Limit2].[LocationTypeValueId] AS [LocationTypeValueId], [Limit2].[GeoPoint] AS [GeoPoint], [Limit2].[GeoFence] AS [GeoFence], [Limit2].[Street1] AS [Street1], [Limit2].[Street2] AS [Street2], [Limit2].[City] AS [City], [Limit2].[County] AS [County], [Limit2].[State] AS [State], [Limit2].[Country] AS [Country], [Limit2].[PostalCode] AS [PostalCode], [Limit2].[Barcode] AS [Barcode], [Limit2].[AssessorParcelId] AS [AssessorParcelId], [Limit2].[StandardizeAttemptedDateTime] AS [StandardizeAttemptedDateTime], [Limit2].[StandardizeAttemptedServiceType] AS [StandardizeAttemptedServiceType], [Limit2].[StandardizeAttemptedResult] AS [StandardizeAttemptedResult], [Limit2].[StandardizedDateTime] AS [StandardizedDateTime], [Limit2].[GeocodeAttemptedDateTime] AS [GeocodeAttemptedDateTime], [Limit2].[GeocodeAttemptedServiceType] AS [GeocodeAttemptedServiceType], [Limit2].[GeocodeAttemptedResult] AS [GeocodeAttemptedResult], [Limit2].[GeocodedDateTime] AS [GeocodedDateTime], [Limit2].[IsGeoPointLocked] AS [IsGeoPointLocked], [Limit2].[PrinterDeviceId] AS [PrinterDeviceId], [Limit2].[ImageId] AS [ImageId], [Limit2].[SoftRoomThreshold] AS [SoftRoomThreshold], [Limit2].[FirmRoomThreshold] AS [FirmRoomThreshold], [Limit2].[CreatedDateTime] AS [CreatedDateTime], [Limit2].[ModifiedDateTime] AS [ModifiedDateTime], [Limit2].[CreatedByPersonAliasId] AS [CreatedByPersonAliasId], [Limit2].[ModifiedByPersonAliasId] AS [ModifiedByPersonAliasId], [Limit2].[Guid] AS [Guid], [Limit2].[ForeignId] AS [ForeignId], [Limit2].[ForeignGuid] AS [ForeignGuid], [Limit2].[ForeignKey] AS [ForeignKey] FROM ( SELECT TOP (1) [Extent4].[Id] AS [Id], [Extent4].[ParentLocationId] AS [ParentLocationId], [Extent4].[Name] AS [Name], [Extent4].[IsActive] AS [IsActive], [Extent4].[LocationTypeValueId] AS [LocationTypeValueId], [Extent4].[GeoPoint] AS [GeoPoint], [Extent4].[GeoFence] AS [GeoFence], [Extent4].[Street1] AS [Street1], [Extent4].[Street2] AS [Street2], [Extent4].[City] AS [City], [Extent4].[County] AS [County], [Extent4].[State] AS [State], [Extent4].[Country] AS [Country], [Extent4].[PostalCode] AS [PostalCode], [Extent4].[Barcode] AS [Barcode], [Extent4].[AssessorParcelId] AS [AssessorParcelId], [Extent4].[StandardizeAttemptedDateTime] AS [StandardizeAttemptedDateTime], [Extent4].[StandardizeAttemptedServiceType] AS [StandardizeAttemptedServiceType], [Extent4].[StandardizeAttemptedResult] AS [StandardizeAttemptedResult], [Extent4].[StandardizedDateTime] AS [StandardizedDateTime], [Extent4].[GeocodeAttemptedDateTime] AS [GeocodeAttemptedDateTime], [Extent4].[GeocodeAttemptedServiceType] AS [GeocodeAttemptedServiceType], [Extent4].[GeocodeAttemptedResult] AS [GeocodeAttemptedResult], [Extent4].[GeocodedDateTime] AS [GeocodedDateTime], [Extent4].[IsGeoPointLocked] AS [IsGeoPointLocked], [Extent4].[PrinterDeviceId] AS [PrinterDeviceId], [Extent4].[ImageId] AS [ImageId], [Extent4].[SoftRoomThreshold] AS [SoftRoomThreshold], [Extent4].[FirmRoomThreshold] AS [FirmRoomThreshold], [Extent4].[CreatedDateTime] AS [CreatedDateTime], [Extent4].[ModifiedDateTime] AS [ModifiedDateTime], [Extent4].[CreatedByPersonAliasId] AS [CreatedByPersonAliasId], [Extent4].[ModifiedByPersonAliasId] AS [ModifiedByPersonAliasId], [Extent4].[Guid] AS [Guid], [Extent4].[ForeignId] AS [ForeignId], [Extent4].[ForeignGuid] AS [ForeignGuid], [Extent4].[ForeignKey] AS [ForeignKey] FROM ( SELECT @plinq0 AS [plinq0], @plinq1 AS [plinq1], [Distinct1].[GroupId] AS [GroupId] FROM ( SELECT DISTINCT [Extent1].[GroupId] AS [GroupId] FROM [dbo].[GroupLocation] AS [Extent1] WHERE ([Extent1].[IsMailingLocation] = 1) AND ([Extent1].[GroupLocationTypeValueId] IS NOT NULL) AND (([Extent1].[GroupLocationTypeValueId] = @plinq0) OR (([Extent1].[GroupLocationTypeValueId] IS NULL) AND (@plinq0 IS NULL)) OR ([Extent1].[GroupLocationTypeValueId] = @plinq1) OR (([Extent1].[GroupLocationTypeValueId] IS NULL) AND (@plinq1 IS NULL))) ) AS [Distinct1] ) AS [Project2] CROSS APPLY ( SELECT TOP (1) [Project3].[GroupId] AS [GroupId], [Project3].[LocationId] AS [LocationId] FROM ( SELECT [Extent3].[ModifiedDateTime] AS [ModifiedDateTime], [Extent2].[GroupId] AS [GroupId], [Extent2].[LocationId] AS [LocationId] FROM [dbo].[GroupLocation] AS [Extent2] INNER JOIN [dbo].[Location] AS [Extent3] ON [Extent2].[LocationId] = [Extent3].[Id] WHERE ([Extent2].[IsMailingLocation] = 1) AND ([Extent2].[GroupLocationTypeValueId] IS NOT NULL) AND (([Extent2].[GroupLocationTypeValueId] = @plinq0) OR (([Extent2].[GroupLocationTypeValueId] IS NULL) AND (@plinq0 IS NULL)) OR ([Extent2].[GroupLocationTypeValueId] = @plinq1) OR (([Extent2].[GroupLocationTypeValueId] IS NULL) AND (@plinq1 IS NULL))) AND ([Project2].[GroupId] = [Extent2].[GroupId]) ) AS [Project3] ORDER BY [Project3].[GroupId] ASC, [Project3].[ModifiedDateTime] DESC ) AS [Limit1] LEFT OUTER JOIN [dbo].[Location] AS [Extent4] ON [Limit1].[LocationId] = [Extent4].[Id] WHERE [Limit1].[GroupId] = @plinq2 ) AS [Limit2]' ,N'@plinq0 int,@plinq1 int,@plinq2 int',@plinq0=19,@plinq1=20,@plinq2=76
--Note param 0 = HomeLocationId, 1 = WorkLocationId and 2 = FamilyId
jasonhendee
Description
On behalf of North Point Ministries
North Point is seeing a significant server/SQL performance issue when sending out emailed statements that link back to the member portal to display the giving statement (individually). They are emailing approximately 10,000 statements at one time and maxing out a P15 SQL DB for an extended period of time as people click n the link to see their statement. (Note they scale up to P15 when sending and have cut their 40k+ emails into 10K chunks for emailing already.)
Actual Behavior
Emailing giving statements and having congregants click on a link to see the statements max's out the DB for an extended period of time.
Expected Behavior
While I would expect there to be a noticeable load, I would not expect it to max out the DB at 100% for more than an hour.
Steps to Reproduce
I believe I can point out the issue and as such I think I can point to a potential way to reproduce the problem. In FinancialStatementGeneratorHelper.cs at line 138, the FinancialStatementGeneratorHelper.GetGroupLocationQuery is called. Then on line 142 it is used and then joined based on GroupId. Based on traces and performance analysis done by NPM, I believe that a portion of that query is returning 264K Group Locations (for NPM) prior to being joined to the one GroupId that is actually needed. The associated linq query is slower performing than the ones to get Financial Transaction.
As such, I believe the issue could be reproduced by repeated calls to the API to fetch one statement if the system has lots of Mailing Addresses for lots of families.
I will add a sample of the Linq query which Azure/SQL identified as the one of longest duration happening when the issue was happening.
Also, a simple fix for the issue would be to add a line after line 138 which added a .Where clause to the groupLocationsQry prior to executing the "join" to limit the first query to only what is needed in the second query.
Issue Confirmation
Rock Version
14.2
Client Culture Setting
en-US