2

I have a stored procedure that takes about 15 seconds to execute. When hundreds of requests come in, we are seeing unacceptable page load times. Sometimes two minutes. The page loads results based on an ID, so it's not the same results for everyone.

My solution was to use a staging table and only update it if the page hasn't loaded for 5 minutes. I thought this would reduce load on that stored procedure. But now I'm seeing problems with this staging table idea.

If page hasn't been hit for 5 minutes, then DELETE from the staging table based on the ID, then run the stored procedure and INSERT the results into the staging table.

Then UPDATE the staging table with a "total result count", SELECT from the staging table based on ID

As you can see it's doing a DELETE, INSERT, UPDATE, and SELECT. Under load I'm getting tons of deadlocks.

A few questions:

  1. what is best practice for returning expensive result sets to simply display on a web page?
  2. how can the staging table approach be improved and assure no deadlocks?

Code:

CREATE procedure [dbo].[BB02_ListFundraisersForEvent] (
    @DesignEventId int, 
    @Offset int,
    @PageSize int,
    @SearchTerms varchar(100) = null,
    @OrderByField varchar(25) = 'DEFAULT',
    @OrderByDirection varchar(5) = 'ASC'
) 
-- exec BB02_ListFundraisersForEvent 38639, 0, 10, '', '', 'ASC', null
as
    set transaction isolation level read uncommitted

    declare @UpdateIncrement DateTime = DATEADD(MINUTE, -5, GETDATE());
    declare @FundraiserCount int;
    declare @LastUpdated DateTime;
    declare @PAGE_STATUS_CANCELED int;
    declare @TOTAL_TYPE_NON_REJECTED int;
    declare @TOTAL_TYPE_REGISTRATION int;
    declare @PROFILE_APPEAL_WEB_DIR_FAMILY int;
    declare @PROFILE_LEVEL_WEB_DIR_FAMILY int;

    set @TOTAL_TYPE_NON_REJECTED  = 2;
    set @TOTAL_TYPE_REGISTRATION = 3;
    set @PAGE_STATUS_CANCELED = 3
    set @PROFILE_APPEAL_WEB_DIR_FAMILY = 3;
    set @PROFILE_LEVEL_WEB_DIR_FAMILY = 2;

    if @OrderByField not in ('FirstName', 'LastName', 'TotalRaised') set @OrderByField = 'DEFAULT';

        IF isnull(@SearchTerms, '') = ''
        BEGIN 
            select @FundraiserCount = (select count(*) from bb02_olr_getsupporterscache where designeventid = @DesignEventId)
            select @LastUpdated = (select top 1 lastupdated from bb02_olr_getsupporterscache where designeventid = @DesignEventId)

            IF( (@FundraiserCount = 0) OR (@LastUpdated < @UpdateIncrement ) OR (ISNULL(@LastUpdated, '') = '')  )
            BEGIN
                DELETE FROM BB02_OLR_GetSupportersCache 
                WHERE designeventid = @DesignEventId

                INSERT INTO bb02_olr_getsupporterscache (DesignEventId,
                                                        AppealName,
                                                        AppealWebDirectory,
                                                        FirstName,
                                                        ImageChoice,
                                                        LastName,
                                                        PhotoURL,
                                                        ProfileWebDirectory,
                                                        TotalRaisedOffline,
                                                        TotalRaisedOnline,
                                                        TotalContributions,
                                                        DisplayPhoto,
                                                        HasStockImages,
                                                        LastUpdated)
                SELECT
                    DesignEventId,
                    AppealName,
                    AppealWebDirectory,
                    FirstName,
                    ImageChoice,
                    LastName,
                    PhotoURL,
                    ProfileWebDirectory,
                    TotalRaisedOffline,
                    TotalRaisedOnline,
                    TotalContributions,
                    DisplayPhoto,
                    HasStockImages,
                    getdate() as LastUpdated
                FROM (
                    -- fundraising pages
                    SELECT
                        de.DesignEventId,
                        egg.EventGivingGroupName as AppealName,
                        awd.WebDirectoryName as AppealWebDirectory,
                        c.FirstName,
                        egg.ImageChoice,
                        c.LastName,
                        egg.PhotoUrl,
                        cwd.WebDirectoryName as ProfileWebDirectory,
                        eggt.TotalRaisedOffline,
                        eggt.TotalRaisedOnline,
                        eggt.TotalContributions,
                        CAST(egg.DisplayPhoto AS bit) AS DisplayPhoto,
                        CAST(CASE WHEN ISNULL(dei.DesignEventId, 0) != 0 then 1 else 0 end as bit) as HasStockImages
                    FROM
                        BB02_Event e
                    INNER JOIN
                        BB02_EventFundraiserRevenueStream efrs on e.EventId = efrs.EventId
                    INNER JOIN 
                        BB02_EventGivingGroup egg on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId
                    INNER JOIN
                        BB02_EventGivingGroupTotal eggt on egg.EventGivingGroupId = eggt.EventGivingGroupId
                    INNER JOIN
                        BB02_Consumer c on c.ConsumerId = egg.ConsumerId
                    INNER JOIN 
                        BB02_WebDirectory cwd on cwd.WebDirectoryId = c.DefaultWebDirectoryId  and cwd.WebDirectoryFamilyId = @PROFILE_LEVEL_WEB_DIR_FAMILY
                    INNER JOIN 
                        BB02_WebDirectory awd on awd.EventGivingGroupId = egg.EventGivingGroupId and awd.WebDirectoryFamilyId = @PROFILE_APPEAL_WEB_DIR_FAMILY
                        inner join BB02_DesignEvent de on e.EventId = de.EventId and egg.DesignId = de.DesignId
                        left join (select distinct DesignEventId from BB02_DesignEventImage) dei on de.DesignEventId = dei.DesignEventId    
                    where eggt.EventGivingGroupTotalTypeId = 
                        case when de.AddFeesToTotal = 1 then @TOTAL_TYPE_REGISTRATION -- 3 includes registration fees
                        else @TOTAL_TYPE_NON_REJECTED /* 1 = Confirmed, 2 = Not Rejected */
                        end
                    and egg.Status <> @PAGE_STATUS_CANCELED
                    and de.DesignEventId = @DesignEventId
                    and egg.IsDeleted = 0

                    union all 

                    -- registrants without pages
                    select
                        cer.DesignEventId,
                        '' as AppealName,
                        '' as AppealWebDirectory,
                        FirstName,
                        '' as ImageChoice,
                        LastName,
                        '' as PhotoURL,
                        '' as ProfileWebDirectory,
                        0 as TotalRaisedOffline,
                        0 as TotalRaisedOnline,
                        0 as TotalContributions,
                        '' as DisplayPhoto,
                        cast(case when isnull(dei.DesignEventId, 0) != 0 then 1 else 0 end as bit) as HasStockImages

                    from BB02_ConsumerEventRegistration cer 
                        left join (select distinct DesignEventId from BB02_DesignEventImage) dei on cer.DesignEventId = dei.DesignEventId   
                    where cer.DesignEventId = @DesignEventId
                        and cer.ConsumerId not in (
                            select egg.ConsumerId 
                            from BB02_EventGivingGroup egg 
                                inner join BB02_EventFundraiserRevenueStream efrs on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId 
                                inner join BB02_DesignEvent de on efrs.EventId = de.EventId and egg.DesignId = de.DesignId
                            where de.DesignEventId = @DesignEventId
                        )
                ) a

                UPDATE bb02_olr_getsupporterscache
                SET TotalCount = (select count(*) from bb02_olr_getsupporterscache where DesignEventId = @DesignEventId)
                WHERE DesignEventId = @DesignEventId
            END

            SELECT * FROM (
            select
                TotalCount,
                SupporterId,
                AppealName,
                AppealWebDirectory,
                FirstName,
                ImageChoice,
                LastName,
                PhotoURL,
                ProfileWebDirectory,
                TotalRaisedOffline,
                TotalRaisedOnline,
                TotalContributions,
                DisplayPhoto,
                HasStockImages,     
                row_number() over (order by 
                    case when @OrderByField = 'FirstName' and @OrderByDirection = 'ASC' then FirstName end asc,
                    case when @OrderByField = 'FirstName' and @OrderByDirection = 'DESC' then FirstName end desc,
                    case when @OrderByField = 'LastName' and @OrderByDirection = 'ASC' then LastName end asc,
                    case when @OrderByField = 'LastName' and @OrderByDirection = 'DESC' then LastName end desc,
                    case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'ASC' then (TotalRaisedOnline + TotalRaisedOffline) end asc,
                    case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'DESC' then (TotalRaisedOnline + TotalRaisedOffline) end desc,
                    case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'ASC' then AppealName end asc,
                    case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'DESC' then AppealName end desc
                ) as rownumber
            from (
                    select * from bb02_olr_getsupporterscache where designeventid = @DesignEventId
                ) a
            ) q
            where q.rownumber > @Offset
                and q.rownumber <= @Offset + @PageSize
            order by rownumber;
        END
        IF isnull(@SearchTerms, '') != ''
        BEGIN 

            declare getSearchStringSegments cursor for
            select * from dbo.Split(' ', @SearchTerms)

            declare 
                @segmentId int,
                @segment varchar(100),
                @segment1 varchar(100), 
                @segment2 varchar(100), 
                @segment3 varchar(100)

            open getSearchStringSegments
            fetch next from getSearchStringSegments into @segmentId, @segment
                while @@fetch_status = 0 and @segmentId <= 3

                begin
                    print 1;
                    if @segmentId = 1 set @segment1 = @segment;
                    if @segmentId = 2 set @segment2 = @segment;
                    if @segmentId = 3 set @segment3 = @segment;
                    fetch next from getSearchStringSegments into @segmentId, @segment
                end
            close getSearchStringSegments;
            deallocate getSearchStringSegments;

            select @FundraiserCount = (
                -- fundraising pages
                select count(egg.EventGivingGroupId) 
                    from BB02_Event e
                        inner join BB02_EventFundraiserRevenueStream efrs on e.EventId = efrs.EventId
                        inner join BB02_EventGivingGroup egg on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId
                        inner join BB02_EventGivingGroupTotal eggt on egg.EventGivingGroupId = eggt.EventGivingGroupId
                        inner join BB02_Consumer c on c.ConsumerId = egg.ConsumerId
                        inner join BB02_WebDirectory cwd on cwd.WebDirectoryId = c.DefaultWebDirectoryId  and cwd.WebDirectoryFamilyId = @PROFILE_LEVEL_WEB_DIR_FAMILY
                        inner join BB02_WebDirectory awd on awd.EventGivingGroupId = egg.EventGivingGroupId and awd.WebDirectoryFamilyId = @PROFILE_APPEAL_WEB_DIR_FAMILY
                        inner join BB02_DesignEvent de on e.EventId = de.EventId and egg.DesignId = de.DesignId
                    where eggt.EventGivingGroupTotalTypeId = 
                            case when de.AddFeesToTotal = 1 then @TOTAL_TYPE_REGISTRATION -- 3 includes registration fees
                            else @TOTAL_TYPE_NON_REJECTED /* 1 = Confirmed, 2 = Not Rejected */
                            end
                        and egg.Status <> @PAGE_STATUS_CANCELED
                        and de.DesignEventId = @DesignEventId
                        and egg.IsDeleted = 0
                        and (
                                (egg.EventGivingGroupName like '%'+@segment1+'%' or egg.EventGivingGroupName like '%'+@segment2+'%' or egg.EventGivingGroupName like '%'+@segment3+'%')
                                or (egg.Attribution like '%'+@segment1+'%' or egg.Attribution like '%'+@segment2+'%' or egg.Attribution like '%'+@segment3+'%')
                                or (c.FirstName like '%'+@segment1+'%' or c.FirstName like '%'+@segment2+'%' or c.FirstName like '%'+@segment3+'%')
                                or (c.LastName like '%'+@segment1+'%' or c.LastName like '%'+@segment2+'%' or c.LastName like '%'+@segment3+'%')
                            )

            ) + (
                -- registrants without pages
                select count(cer.ConsumerEventRegistrationId)
                    from BB02_ConsumerEventRegistration cer 
                    where cer.DesignEventId = @DesignEventId
                        and cer.ConsumerId not in (
                            select egg.ConsumerId 
                            from BB02_EventGivingGroup egg 
                                inner join BB02_EventFundraiserRevenueStream efrs on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId 
                                inner join BB02_DesignEvent de on efrs.EventId = de.EventId and egg.DesignId = de.DesignId
                            where de.DesignEventId = @DesignEventId
                        )
                        and (
                                (cer.FirstName like '%'+@segment1+'%' or cer.FirstName like '%'+@segment2+'%' or cer.FirstName like '%'+@segment3+'%')
                                or (cer.LastName like '%'+@segment1+'%' or cer.LastName like '%'+@segment2+'%' or cer.LastName like '%'+@segment3+'%')
                            )
                            and cer.IsDeleted <> 1
            )

            select * from (
                select 
                    @FundraiserCount as TotalCount,
                    0 as SupporterId,
                    AppealName,
                    AppealWebDirectory,
                    FirstName,
                    ImageChoice,
                    LastName,
                    PhotoURL,
                    ProfileWebDirectory,
                    TotalRaisedOffline,
                    TotalRaisedOnline,
                    TotalContributions,
                    DisplayPhoto,
                    HasStockImages,     
                    row_number() over (order by 
                        case when @OrderByField = 'FirstName' and @OrderByDirection = 'ASC' then FirstName end asc,
                        case when @OrderByField = 'FirstName' and @OrderByDirection = 'DESC' then FirstName end desc,
                        case when @OrderByField = 'LastName' and @OrderByDirection = 'ASC' then LastName end asc,
                        case when @OrderByField = 'LastName' and @OrderByDirection = 'DESC' then LastName end desc,
                        case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'ASC' then (TotalRaisedOnline + TotalRaisedOffline) end asc,
                        case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'DESC' then (TotalRaisedOnline + TotalRaisedOffline) end desc,
                        case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'ASC' then AppealName end asc,
                        case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'DESC' then AppealName end desc
                    ) as rownumber
                from (
                    -- fundraising pages
                    select
                        egg.EventGivingGroupName as AppealName,
                        awd.WebDirectoryName as AppealWebDirectory,
                        c.FirstName,
                        egg.ImageChoice,
                        c.LastName,
                        egg.PhotoUrl,
                        cwd.WebDirectoryName as ProfileWebDirectory,
                        eggt.TotalRaisedOffline,
                        eggt.TotalRaisedOnline,
                        eggt.TotalContributions,
                        cast(egg.DisplayPhoto as bit) as DisplayPhoto,
                        cast(case when isnull(dei.DesignEventId, 0) != 0 then 1 else 0 end as bit) as HasStockImages,       
                        row_number() over (order by 
                            case when @OrderByField = 'FirstName' and @OrderByDirection = 'ASC' then c.FirstName end asc,
                            case when @OrderByField = 'FirstName' and @OrderByDirection = 'DESC' then c.FirstName end desc,
                            case when @OrderByField = 'LastName' and @OrderByDirection = 'ASC' then c.LastName end asc,
                            case when @OrderByField = 'LastName' and @OrderByDirection = 'DESC' then c.LastName end desc,
                            case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'ASC' then (eggt.TotalRaisedOnline + eggt.TotalRaisedOffline) end asc,
                            case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'DESC' then (eggt.TotalRaisedOnline + eggt.TotalRaisedOffline) end desc,
                            case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'ASC' then egg.EventGivingGroupName end asc,
                            case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'DESC' then egg.EventGivingGroupName end desc
                        ) as rownumber
                    from BB02_Event e
                        inner join BB02_EventFundraiserRevenueStream efrs on e.EventId = efrs.EventId
                        inner join BB02_EventGivingGroup egg on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId
                        inner join BB02_EventGivingGroupTotal eggt on egg.EventGivingGroupId = eggt.EventGivingGroupId
                        inner join BB02_Consumer c on c.ConsumerId = egg.ConsumerId
                        inner join BB02_WebDirectory cwd on cwd.WebDirectoryId = c.DefaultWebDirectoryId  and cwd.WebDirectoryFamilyId = @PROFILE_LEVEL_WEB_DIR_FAMILY
                        inner join BB02_WebDirectory awd on awd.EventGivingGroupId = egg.EventGivingGroupId and awd.WebDirectoryFamilyId = @PROFILE_APPEAL_WEB_DIR_FAMILY
                        inner join BB02_DesignEvent de on e.EventId = de.EventId and egg.DesignId = de.DesignId
                        left join (select distinct DesignEventId from BB02_DesignEventImage) dei on de.DesignEventId = dei.DesignEventId    
                    where eggt.EventGivingGroupTotalTypeId = 
                            case when de.AddFeesToTotal = 1 then @TOTAL_TYPE_REGISTRATION -- 3 includes registration fees
                            else @TOTAL_TYPE_NON_REJECTED /* 1 = Confirmed, 2 = Not Rejected */
                            end
                        and egg.Status <> @PAGE_STATUS_CANCELED
                        and de.DesignEventId = @DesignEventId
                        and egg.IsDeleted = 0
                        and (
                                (egg.EventGivingGroupName like '%'+@segment1+'%' or egg.EventGivingGroupName like '%'+@segment2+'%' or egg.EventGivingGroupName like '%'+@segment3+'%')
                                or (egg.Attribution like '%'+@segment1+'%' or egg.Attribution like '%'+@segment2+'%' or egg.Attribution like '%'+@segment3+'%')
                                or (c.FirstName like '%'+@segment1+'%' or c.FirstName like '%'+@segment2+'%' or c.FirstName like '%'+@segment3+'%')
                                or (c.LastName like '%'+@segment1+'%' or c.LastName like '%'+@segment2+'%' or c.LastName like '%'+@segment3+'%')
                            )

                    union all 

                    -- registrants without pages
                    select 
                        '' as AppealName,
                        '' as AppealWebDirectory,
                        FirstName,
                        '' as ImageChoice,
                        LastName,
                        '' as PhotoURL,
                        '' as ProfileWebDirectory,
                        0 as TotalRaisedOffline,
                        0 as TotalRaisedOnline,
                        0 as TotalContributions,
                        '' as DisplayPhoto,
                        cast(case when isnull(dei.DesignEventId, 0) != 0 then 1 else 0 end as bit) as HasStockImages,
                        row_number() over (order by 
                            case when @OrderByField = 'FirstName' and @OrderByDirection = 'ASC' then cer.FirstName end asc,
                            case when @OrderByField = 'FirstName' and @OrderByDirection = 'DESC' then cer.FirstName end desc,
                            case when @OrderByField = 'LastName' and @OrderByDirection = 'ASC' then cer.LastName end asc,
                            case when @OrderByField = 'LastName' and @OrderByDirection = 'DESC' then cer.LastName end desc,
                            case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'ASC' then (0) end asc,
                            case when @OrderByField = 'TotalRaised' and @OrderByDirection = 'DESC' then (0) end desc,
                            case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'ASC' then '' end asc,
                            case when @OrderByField = 'DEFAULT' and @OrderByDirection = 'DESC' then '' end desc
                        ) as rownumber

                    from BB02_ConsumerEventRegistration cer 
                        left join (select distinct DesignEventId from BB02_DesignEventImage) dei on cer.DesignEventId = dei.DesignEventId   
                    where cer.DesignEventId = @DesignEventId
                        and cer.ConsumerId not in (
                            select egg.ConsumerId 
                            from BB02_EventGivingGroup egg 
                                inner join BB02_EventFundraiserRevenueStream efrs on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId 
                                inner join BB02_DesignEvent de on efrs.EventId = de.EventId and egg.DesignId = de.DesignId
                            where de.DesignEventId = @DesignEventId
                        )
                        and (
                                (cer.FirstName like '%'+@segment1+'%' or cer.FirstName like '%'+@segment2+'%' or cer.FirstName like '%'+@segment3+'%')
                                or (cer.LastName like '%'+@segment1+'%' or cer.LastName like '%'+@segment2+'%' or cer.LastName like '%'+@segment3+'%')
                            )
                ) a
            ) q
            where q.rownumber > @Offset
                and q.rownumber <= @Offset + @PageSize
            order by rownumber;

    END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Induster
  • 733
  • 1
  • 6
  • 15
  • 1
    Can I also make one suggestion. Use SET NOCOUNT ON in your procedure. It will improve performance and it is an easy gain: see https://www.mssqltips.com/sqlservertip/1226/set-nocount-on-improves-sql-server-stored-procedure-performance/ – Fuzzy Feb 10 '16 at 18:19
  • Take a careful look at what order of the items in your FROM clause. Consider putting in subqueries to force the query compiler one direction or another. If one of the INNER JOIN blocks reduces the result set more aggressively than others, then moving that block closer to the "FROM" will yield benefits. – Ryan Goltry Feb 10 '16 at 19:59
  • First of all you have to catch real deadlocks and identify actual deadlock reasons. I don't see any transaction starting inside this proc - is the whole call surrounded with transaction? You should replace all `select *` with necessary column names. First check for `@FundraiserCount` and `@LastUpdated` can be replaced with a single `top`. Note you are missing `ORDER BY` clause in your top1 select. And move `Totalcount` update outside of this transaction as well. Again, until you find out what is actually _deadlocked_ - you can not fix it. – Ivan Starostin Feb 15 '16 at 11:29

1 Answers1

0

Depending on how long it takes to get the actual data, the idea of a caching the data might be worthwhile indeed; the question is in how far it wouldn't be easier to do so in the webserver layer rather than in the database layer.

That said, since you already have gotten this part, let's have a look at the stored procedure.

First of some general remarks:

  • I really, really don't like READ UNCOMMITTED. I understand you added this to avoid locking, but the idea of dirty reads really makes me nervous. You risk returning halved or false information to the caller like this. Personally I rather wait a bit longer for good info than getting bad info.
  • Do not use SELECT COUNT(*) ... to figure out if there are any records. The server needs to scan all the relevant records in order to get the number but all you're really interested in is whether there is one. Use WHERE EXISTS() for this.
  • Even better, since you do a SELECT TOP 1 afterwards anyway, use the outcome of that. If it found a value, then there must be a record!
  • ISNULL(@LastUpdated, '') = '' is a bit of a fishy construction when using a datetime field/variable. Rather use @LastUpdated IS NULL for this check
  • You do the DELETE followed by what is probably a heavy INSERT afterwards. I guess this takes a while and this is where the deadlocks come from. Proper locking should help out here, but I don't know how it would cooperate with the READ UNCOMMITTED from above.
  • After the INSERT you go over the freshly inserted data again to figure out how many rows there are. Since the only relevant rows here are those we just inserted we can safely assume that @@ROWCOUNT already holds the answer. Reading that system-variable takes zero effort. Doing a SELECT COUNT(*) ... is much, much more taxing on the system
  • Personally I'd prefer to split the INSERT into 2 parts and use a temp-table to hold the intermediate results. I'm not sure it would make much of a difference performance wise (in theory it's MORE work for the server) but I think it would cause less surprises, especially if you put some statistics updates in between. Only testing can really tell.
  • The side-effect of having the temp-table would be that you can simply return the results from said table and don't need to go back to the cache table, avoiding interactions with the other processes.
  • I'd also suggest to rather use dynamic sql because of the flexibility offered for the sorting. The casing structure inside the `RowNumber()' surely works but I doubt it's overly efficient.

Anyway, as for locking. The reason you get dead-locks is because you're doing a lot of operations on the same records and potentially from different connections simultaneously. As a result one connection might be doing the INSERT while another one tries to do the DELETE or UPDATE etc etc.. That can't be good. To avoid this I'd go with locking but even there I might run into issues, so instead I'd try to use an application lock that 'pauses' any connection while another one is still in the process of refreshing the data in the cache-table. More-so, we can 'limit' this locking to the @EventId that's being processed.

Doing some quick & dirty changes to the code I come up with below. There probably will be some syntax errors and maybe even some logic errors so you'll probably need to tweak/fix as needed. But I hope it will point you in the right direction.

CREATE procedure [dbo].[BB02_ListFundraisersForEvent] (
    @DesignEventId int, 
    @Offset int,
    @PageSize int,
    @SearchTerms varchar(100) = null,
    @OrderByField varchar(25) = 'DEFAULT',
    @OrderByDirection varchar(5) = 'ASC'
) 
-- exec BB02_ListFundraisersForEvent 38639, 0, 10, '', '', 'ASC', null
as
    -- set transaction isolation level read uncommitted

    declare @UpdateIncrement DateTime = DATEADD(MINUTE, -5, GETDATE());
    declare @FundraiserCount int;
    declare @LastUpdated DateTime;
    declare @PAGE_STATUS_CANCELED int;
    declare @TOTAL_TYPE_NON_REJECTED int;
    declare @TOTAL_TYPE_REGISTRATION int;
    declare @PROFILE_APPEAL_WEB_DIR_FAMILY int;
    declare @PROFILE_LEVEL_WEB_DIR_FAMILY int;
    declare @TotalCount int
    declare @cache_was_updated bit
    declare @sql nvarchar(max)
    declare @rc int
    declare @LockName sysname

    set @TOTAL_TYPE_NON_REJECTED  = 2;
    set @TOTAL_TYPE_REGISTRATION = 3;
    set @PAGE_STATUS_CANCELED = 3
    set @PROFILE_APPEAL_WEB_DIR_FAMILY = 3;
    set @PROFILE_LEVEL_WEB_DIR_FAMILY = 2;

    if @OrderByField not in ('FirstName', 'LastName', 'TotalRaised') set @OrderByField = 'DEFAULT';
    IF @OrderByDirection not in ('ASC', 'DESC') set @OrderByDirection = 'ASC';

    SET @cache_was_updated = 0

    IF isnull(@SearchTerms, '') = ''
        BEGIN 

            select @LastUpdated = NULL
            select TOP 1 @LastUpdated = lastupdated from bb02_olr_getsupporterscache where designeventid = @DesignEventId


            IF( (@LastUpdated IS NULL) -- no value found means no data present for given @DesignEventId
                 OR (@LastUpdated < @UpdateIncrement ) ) -- or value found, but too far in the past)
                BEGIN
                    -- prepare new batch
                    set @LockName = 'CacheUpdate' + Convert(nvarchar(100), @DesignEventId)


                    -- get exclusive applock on this @DesignEventId
                    -- => we should be the only ones that can update this!

                    EXEC @rc = sp_getapplock @Resource = @LockName,
                                             @LockMode = 'Exlusive',
                                             @LockTimeout = 0, -- if another process already has the lock, we will skip the update
                                             @lockOwner = 'Session'

                    IF @rc > 0
                        BEGIN
                            -- lock obtained
                            -- => let's do the cache update

                            SET @TotalCount = 0

                            -- fundraising pages
                            SELECT
                                    egg.EventGivingGroupName as AppealName,
                                    awd.WebDirectoryName as AppealWebDirectory,
                                    c.FirstName,
                                    egg.ImageChoice,
                                    c.LastName,
                                    egg.PhotoUrl,
                                    cwd.WebDirectoryName as ProfileWebDirectory,
                                    eggt.TotalRaisedOffline,
                                    eggt.TotalRaisedOnline,
                                    eggt.TotalContributions,
                                    CAST(egg.DisplayPhoto AS bit) AS DisplayPhoto,
                                    CAST(CASE WHEN ISNULL(dei.DesignEventId, 0) != 0 then 1 else 0 end as bit) as HasStockImages
                                INTO #staging
                                FROM
                                    BB02_Event e
                                INNER JOIN
                                    BB02_EventFundraiserRevenueStream efrs on e.EventId = efrs.EventId
                                INNER JOIN 
                                    BB02_EventGivingGroup egg on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId
                                INNER JOIN
                                    BB02_EventGivingGroupTotal eggt on egg.EventGivingGroupId = eggt.EventGivingGroupId
                                INNER JOIN
                                    BB02_Consumer c on c.ConsumerId = egg.ConsumerId
                                INNER JOIN 
                                    BB02_WebDirectory cwd on cwd.WebDirectoryId = c.DefaultWebDirectoryId  and cwd.WebDirectoryFamilyId = @PROFILE_LEVEL_WEB_DIR_FAMILY
                                INNER JOIN 
                                    BB02_WebDirectory awd on awd.EventGivingGroupId = egg.EventGivingGroupId and awd.WebDirectoryFamilyId = @PROFILE_APPEAL_WEB_DIR_FAMILY
                                    inner join BB02_DesignEvent de on e.EventId = de.EventId and egg.DesignId = de.DesignId
                                    left join (select distinct DesignEventId from BB02_DesignEventImage) dei on de.DesignEventId = dei.DesignEventId    
                                where eggt.EventGivingGroupTotalTypeId = 
                                    case when de.AddFeesToTotal = 1 then @TOTAL_TYPE_REGISTRATION -- 3 includes registration fees
                                    else @TOTAL_TYPE_NON_REJECTED /* 1 = Confirmed, 2 = Not Rejected */
                                    end
                                and egg.Status <> @PAGE_STATUS_CANCELED
                                and de.DesignEventId = @DesignEventId
                                and egg.IsDeleted = 0


                                SET @TotalCount = @TotalCount + @@ROWCOUNT

                                -- registrants without pages

                            INSERT #staging (                       AppealName,
                                                                    AppealWebDirectory,
                                                                    FirstName,
                                                                    ImageChoice,
                                                                    LastName,
                                                                    PhotoURL,
                                                                    ProfileWebDirectory,
                                                                    TotalRaisedOffline,
                                                                    TotalRaisedOnline,
                                                                    TotalContributions,
                                                                    DisplayPhoto,
                                                                    HasStockImages,
                                                                    LastUpdated)
                                select
                                    '' as AppealName,
                                    '' as AppealWebDirectory,
                                    FirstName,
                                    '' as ImageChoice,
                                    LastName,
                                    '' as PhotoURL,
                                    '' as ProfileWebDirectory,
                                    0 as TotalRaisedOffline,
                                    0 as TotalRaisedOnline,
                                    0 as TotalContributions,
                                    '' as DisplayPhoto,
                                    cast(case when isnull(dei.DesignEventId, 0) != 0 then 1 else 0 end as bit) as HasStockImages

                                from BB02_ConsumerEventRegistration cer 
                                    left join (select distinct DesignEventId from BB02_DesignEventImage) dei on cer.DesignEventId = dei.DesignEventId   
                                where cer.DesignEventId = @DesignEventId
                                    and cer.ConsumerId not in (
                                        select egg.ConsumerId 
                                        from BB02_EventGivingGroup egg 
                                            inner join BB02_EventFundraiserRevenueStream efrs on efrs.EventFundraiserRevenueStreamId = egg.EventFundraiserRevenueStreamId 
                                            inner join BB02_DesignEvent de on efrs.EventId = de.EventId and egg.DesignId = de.DesignId
                                        where de.DesignEventId = @DesignEventId
                                    )

                                SET @TotalCount = @TotalCount + @@ROWCOUNT


                            -- out with the old, in with the new
                            BEGIN TRANSACTION

                                DELETE FROM BB02_OLR_GetSupportersCache WITH (XLOCK, ROWLOCK, HOLDLOCK)
                                 WHERE designeventid = @DesignEventId

                                INSERT INTO bb02_olr_getsupporterscache (DesignEventId,
                                                                        AppealName,
                                                                        AppealWebDirectory,
                                                                        FirstName,
                                                                        ImageChoice,
                                                                        LastName,
                                                                        PhotoURL,
                                                                        ProfileWebDirectory,
                                                                        TotalRaisedOffline,
                                                                        TotalRaisedOnline,
                                                                        TotalContributions,
                                                                        DisplayPhoto,
                                                                        HasStockImages,
                                                                        LastUpdated,
                                                                        TotalCount)

                                SELECT  DesignEventId = @DesignEventId,
                                        AppealName,
                                        AppealWebDirectory,
                                        FirstName,
                                        ImageChoice,
                                        LastName,
                                        PhotoURL,
                                        ProfileWebDirectory,
                                        TotalRaisedOffline,
                                        TotalRaisedOnline,
                                        TotalContributions,
                                        DisplayPhoto,
                                        HasStockImages,
                                        LastUpdated = CURRENT_TIMESTAMP,
                                        TotalCount = @TotalCount

                            COMMIT TRANSAcTION

                            -- don't drop #staging table yet, we'll re-use it for output again right away
                            SELECT @cache_was_updated = 1

                        END
                    ELSE
                        BEGIN
                            -- no lock was obtained, simply wait for the lock to be freed as that will 
                            -- be the moment the new data comes available

                            EXEC @rc = sp_getapplock @Resource = @LockName,
                                                     @LockMode = 'Exclusive',
                                                     @LockTimeout = 600000, -- 10 minutes should be enough, end-user will be pretty annyoyed anyway =)
                                                     @lockOwner = 'Session'


                        END


                    -- free the lock agian, we assume reading locks are handled properly 
                    EXEC sp_releaseapplock @Resource = @LockName


                END -- cache update required or not?

            -- fetch results
            SET @sql = Convert(nvarchar(max), N'')
                     + N'

            SELECT * FROM (
            select
                TotalCount' + (CASE WHEN @cache_was_updated  = 1 THEN ' = @TotalCount' ELSE N'' END) + ',
                SupporterId,
                AppealName,
                AppealWebDirectory,
                FirstName,
                ImageChoice,
                LastName,
                PhotoURL,
                ProfileWebDirectory,
                TotalRaisedOffline,
                TotalRaisedOnline,
                TotalContributions,
                DisplayPhoto,
                HasStockImages,     
                row_number() over (order by ' + 
                    case when @OrderByField IN ('FirstName', 'LastName') then @OrderByField 
                         when @OrderByField = 'TotalRaised'              then '(TotalRaisedOnline + TotalRaisedOffline)' 
                                                                         else 'AppealName'  end
                + ' ' + @OrderByDirection + ') as rownumber
            from ' + (CASE WHEN @cache_was_updated = 1 THEN '#staging' ELSE 'bb02_olr_getsupporterscache where designeventid = @DesignEventId' END) + '

            ) q
            where q.rownumber > @Offset
              and q.rownumber <= @Offset + @PageSize
            order by rownumber;'

            exec sp_executesql @stmt = @sql,
                               @params = N'@TotalCount int, @DesignEventId int, @Offset int, @PageSize int',
                               @TotalCount = @TotalCount,
                               @DesignEventId = @DesignEventId,
                               @Offset = @Offset,
                               @PageSize = @PageSize


            DROP TABLE #staging

        END


        -- other part not looked at (yet) as I guess the trouble comes from the refresh, and that's only in the first part as far as I can tell ...

    END
deroby
  • 5,902
  • 2
  • 19
  • 33