SQL Server获取iCal重复日程查询过慢问题求助

Alright, let's tackle this slow query problem head-on. The biggest bottleneck here is two separate calls to your custom C# DLL dbo.ExpandRecurrence—this function parses iCal RRULEs to generate all recurring events, and combining it with repeated date formatting and string manipulation is dragging performance way down.

Here's your original query for reference:

select 1,case when B.startdate is not null then B.startdate else case when charindex('Z',dtstart) > 0 then dbo.VCalendar_DateFormat(dtstart) else LEFT(dtstart,4) + '-' + substring(dtstart,5,2) + '-' + substring(dtstart,7,2)end end as startTime, C.ID as idressource,ltrim(rtrim((select STUFF(( select ' ' + CHAR(10) + Summary + ' ' + Description from tbIcal_data AA left join tbPatient D on (replacE(replacE(replacE(Summary,' ',''),'-',''),'_','') Collate SQL_Latin1_General_CP1253_CI_AI like '%' +replace(replace(replace(D.Prenom_SansAccent + D.Nom_SansAccent,' ',''),'-',''),'_','') + '%' ) or ( replacE(replacE(Summary,' ',''),'-','') like '%'+ ltrim(rtrim(REPLACE(REPLACE(isnull(D.TelephoneDomicile,'')+isnull(D.TelephoneTravail,'')+isnull(D.TelephonePortable,''),' ',''),'-',''))) + '%' and ltrim(rtrim(REPLACE(REPLACE(isnull(D.TelephoneDomicile,'')+isnull(D.TelephoneDomicile,'')+isnull(D.TelephonePortable,''),' ',''),'-',''))) <> '') outer apply dbo.ExpandRecurrence(RRule,null,0,GETDATE(),dateadd(year,2,getdate()),case when charindex('Z',AA.dtstart) > 0 then dbo.VCalendar_DateFormat(AA.dtstart) else LEFT(AA.dtstart,4) + '-' + substring(AA.dtstart,5,2) + '-' + substring(AA.dtstart,7,2)end,case when charindex('Z',AA.dtEnd) > 0 then dbo.VCalendar_DateFormat(AA.dtEnd) else LEFT(AA.dtEnd,4) + '-' + substring(AA.dtEnd,5,2) + '-' + substring(AA.dtEnd,7,2)end ) BB where ((case when bb.startdate is not null then bb.startdate else case when charindex('Z',AA.dtstart) > 0 then dbo.VCalendar_DateFormat(AA.dtstart) else LEFT(AA.dtstart,4) + '-' + substring(AA.dtstart,5,2) + '-' + substring(AA.dtstart,7,2)end end >= GETDATE() and datediff(day,case when bb.startdate is not null then bb.startdate else case when charindex('Z',AA.dtstart) > 0 then dbo.VCalendar_DateFormat(AA.dtstart) else LEFT(AA.dtstart,4) + '-' + substring(AA.dtstart,5,2) + '-' + substring(AA.dtstart,7,2)end end,case when bb.Enddate is not null then bb.Enddate else case when charindex('Z',AA.dtEnd) > 0 then dbo.VCalendar_DateFormat(AA.dtEnd) else LEFT(dtEnd,4) + '-' + substring(AA.dtEnd,5,2) + '-' + substring(AA.dtEnd,7,2)end end ) = 1) or (datediff(day,case when bb.startdate is not null then bb.startdate else case when charindex('Z',AA.dtstart) > 0 then dbo.VCalendar_DateFormat(AA.dtstart) else LEFT(AA.dtstart,4) + '-' + substring(AA.dtstart,5,2) + '-' + substring(AA.dtstart,7,2)end end,case when bb.Enddate is not null then bb.Enddate else case when charindex('Z',dtEnd) > 0 then dbo.VCalendar_DateFormat(dtEnd) else LEFT(dtEnd,4) + '-' + substring(dtEnd,5,2) + '-' + substring(dtEnd,7,2)end end ) = 0 and bb.StartDate is not null)) and AA.ressource = A.ressource and D.ID is null and case when bb.startdate is not null then bb.startdate else case when charindex('Z',AA.dtstart) > 0 then dbo.VCalendar_DateFormat(AA.dtstart) else LEFT(AA.dtstart,4) + '-' + substring(AA.dtstart,5,2) + '-' + substring(AA.dtstart,7,2)end end = case when B.startdate is not null then B.startdate else case when charindex('Z',A.dtstart) > 0 then dbo.VCalendar_DateFormat(A.dtstart) else LEFT(A.dtstart,4) + '-' + substring(A.dtstart,5,2) + '-' + substring(A.dtstart,7,2)end end FOR XML PATH('')),1,1,'')AS DayMessage))) AS DayMessage from tbIcal_data A left join tbPatient D on (replacE(replacE(replacE(Summary,' ',''),'-',''),'_','') Collate SQL_Latin1_General_CP1253_CI_AI like '%' +replace(replace(replace(D.Prenom_SansAccent + D.Nom_SansAccent,' ',''),'-',''),'_','') + '%' ) or ( replacE(replacE(Summary,' ',''),'-','') like '%'+ ltrim(rtrim(REPLACE(REPLACE(isnull(D.TelephoneDomicile,'')+isnull(D.TelephoneTravail,'')+isnull(D.TelephonePortable,''),' ',''),'-',''))) + '%' and ltrim(rtrim(REPLACE(REPLACE(isnull(D.TelephoneDomicile,'')+isnull(D.TelephoneDomicile,'')+isnull(D.TelephonePortable,''),' ',''),'-',''))) <> '') outer apply dbo.ExpandRecurrence(RRule,null,0,GETDATE(),dateadd(year,2,getdate()),case when charindex('Z',A.dtstart) > 0 then dbo.VCalendar_DateFormat(A.dtstart) else LEFT(A.dtstart,4) + '-' + substring(A.dtstart,5,2) + '-' + substring(A.dtstart,7,2)end,case when charindex('Z',A.dtEnd) > 0 then dbo.VCalendar_DateFormat(A.dtEnd) else LEFT(A.dtEnd,4) + '-' + substring(A.dtEnd,5,2) + '-' + substring(A.dtEnd,7,2)end ) B join tbRessource C on (C.nom = A.ressource) where ((case when B.startdate is not null then B.startdate else case when charindex('Z',A.dtstart) > 0 then dbo.VCalendar_DateFormat(A.dtstart) else LEFT(A.dtstart,4) + '-' + substring(A.dtstart,5,2) + '-' + substring(A.dtstart,7,2)end end >= GETDATE() and datediff(day,case when B.startdate is not null then B.startdate else case when charindex('Z',A.dtstart) > 0 then dbo.VCalendar_DateFormat(A.dtstart) else LEFT(A.dtstart,4) + '-' + substring(A.dtstart,5,2) + '-' + substring(A.dtstart,7,2)end end,case when B.Enddate is not null then B.Enddate else case when charindex('Z',A.dtEnd) > 0 then dbo.VCalendar_DateFormat(A.dtEnd) else LEFT(dtEnd,4) + '-' + substring(A.dtEnd,5,2) + '-' + substring(A.dtEnd,7,2)end end ) = 1) or (datediff(day,case when B.startdate is not null then B.startdate else case when charindex('Z',A.dtstart) > 0 then dbo.VCalendar_DateFormat(A.dtstart) else LEFT(A.dtstart,4) + '-' + substring(A.dtstart,5,2) + '-' + substring(A.dtstart,7,2)end end,case when B.Enddate is not null then B.Enddate else case when charindex('Z',dtEnd) > 0 then dbo.VCalendar_DateFormat(dtEnd) else LEFT(dtEnd,4) + '-' + substring(dtEnd,5,2) + '-' + substring(dtEnd,7,2)end end ) = 0 and B.StartDate is not null)) and ressource <> 'Gestion' and D.ID is null and A.Summary is not null group by C.ID,A.ressource,case when B.startdate is not null then B.startdate else case when charindex('Z',A.dtstart) > 0 then dbo.VCalendar_DateFormat(A.dtstart) else LEFT(A.dtstart,4) + '-' + substring(A.dtstart,5,2) + '-' + substring(A.dtstart,7,2)end end

Actionable Optimization Steps #

1. Eliminate Repeated Calls to dbo.ExpandRecurrence #

This is the single biggest performance win. Your query calls the DLL once in the main query and again in the subquery for STUFF—each call is expensive, especially since it generates all recurring events upfront. Instead, precompute all expanded events once using a CTE or temporary table, then reuse that data everywhere.

2. Simplify Date Formatting Logic #

You're repeating the same date formatting logic dozens of times. Move this into a precomputed field in your CTE/temp table, or wrap it in a lightweight scalar function (CTE is better for performance). Also, check if dbo.VCalendar_DateFormat can be replaced with built-in SQL functions like TRY_CONVERT to cut down on overhead.

3. Fix Non-SARGable String Matching #

Your JOIN conditions on tbPatient use tons of REPLACE calls directly on fields, which prevents SQL from using indexes. Fix this by:

• Adding computed columns to tbIcal_data for the standardized Summary (no spaces, dashes, underscores) and indexing it
• Adding computed columns to tbPatient for standardized full name and combined phone numbers (no spaces/dashes) and indexing those
• Using these precomputed columns in your JOIN/WHERE clauses instead of modifying fields on the fly

4. Replace STUFF + FOR XML PATH with STRING_AGG (SQL Server 2017+) #

If you're on SQL Server 2017 or newer, STRING_AGG is far more efficient for concatenating strings than the old STUFF trick. It's also easier to read and maintain.

5. Add Targeted Indexes #

Make sure you have indexes on:

• tbIcal_data.ressource, tbIcal_data.dtstart, tbIcal_data.dtEnd
• tbRessource.nom (since you're joining on this field)
• The computed standardized columns in tbIcal_data and tbPatient mentioned earlier

6. Narrow the Recurrence Date Range #

Your DLL is generating events for the next 2 years—do you really need all that data right now? If you only need upcoming appointments (e.g., next 30/90 days), shrink the range in DATEADD(year,2,GETDATE()) to reduce the number of events generated.

Optimized Query Example #

Here's a rewritten version using CTEs to precompute expanded events and simplify logic:

WITH ExpandedEvents AS (
    SELECT 
        A.ressource,
        A.Summary,
        A.Description,
        -- Precompute formatted dates once
        CASE WHEN CHARINDEX('Z', A.dtstart) > 0 THEN dbo.VCalendar_DateFormat(A.dtstart)
             ELSE LEFT(A.dtstart,4) + '-' + SUBSTRING(A.dtstart,5,2) + '-' + SUBSTRING(A.dtstart,7,2)
        END AS FormattedStartDate,
        CASE WHEN CHARINDEX('Z', A.dtEnd) > 0 THEN dbo.VCalendar_DateFormat(A.dtEnd)
             ELSE LEFT(A.dtEnd,4) + '-' + SUBSTRING(A.dtEnd,5,2) + '-' + SUBSTRING(A.dtEnd,7,2)
        END AS FormattedEndDate,
        BB.startdate,
        BB.Enddate
    FROM tbIcal_data A
    -- Call ExpandRecurrence ONLY ONCE
    OUTER APPLY dbo.ExpandRecurrence(
        A.RRule,
        NULL,
        0,
        GETDATE(),
        DATEADD(year,2,GETDATE()), -- Adjust this range if possible!
        CASE WHEN CHARINDEX('Z', A.dtstart) > 0 THEN dbo.VCalendar_DateFormat(A.dtstart)
             ELSE LEFT(A.dtstart,4) + '-' + SUBSTRING(A.dtstart,5,2) + '-' + SUBSTRING(A.dtstart,7,2)
        END,
        CASE WHEN CHARINDEX('Z', A.dtEnd) > 0 THEN dbo.VCalendar_DateFormat(A.dtEnd)
             ELSE LEFT(A.dtEnd,4) + '-' + SUBSTRING(A.dtEnd,5,2) + '-' + SUBSTRING(A.dtEnd,7,2)
        END
    ) BB
    WHERE A.ressource <> 'Gestion'
      AND A.Summary IS NOT NULL
),
FilteredEvents AS (
    SELECT 
        ressource,
        Summary,
        Description,
        -- Use COALESCE to simplify start/end date logic
        COALESCE(startdate, FormattedStartDate) AS FinalStartDate,
        COALESCE(Enddate, FormattedEndDate) AS FinalEndDate
    FROM ExpandedEvents
    WHERE 
        COALESCE(startdate, FormattedStartDate) >= GETDATE()
        AND (
            DATEDIFF(day, COALESCE(startdate, FormattedStartDate), COALESCE(Enddate, FormattedEndDate)) = 1
            OR (
                DATEDIFF(day, COALESCE(startdate, FormattedStartDate), COALESCE(Enddate, FormattedEndDate)) = 0
                AND startdate IS NOT NULL
            )
        )
)
SELECT 
    1,
    FE.FinalStartDate AS startTime,
    C.ID AS idressource,
    -- Use STRING_AGG for cleaner, faster concatenation
    STRING_AGG(FE2.Summary + ' ' + FE2.Description, CHAR(10)) AS DayMessage
FROM FilteredEvents FE
JOIN tbRessource C ON C.nom = FE.ressource
-- Reuse filtered events for same-day concatenation
LEFT JOIN FilteredEvents FE2
    ON FE2.ressource = FE.ressource
    AND FE2.FinalStartDate = FE.FinalStartDate
-- Exclude patient-matching records
LEFT JOIN tbPatient D
    ON (
        REPLACE(REPLACE(REPLACE(FE.Summary,' ',''),'-',''),'_','') COLLATE SQL_Latin1_General_CP1253_CI_AI
        LIKE '%' + REPLACE(REPLACE(REPLACE(D.Prenom_SansAccent + D.Nom_SansAccent,' ',''),'-',''),'_','') + '%'
    ) OR (
        REPLACE(REPLACE(FE.Summary,' ',''),'-','')
        LIKE '%' + LTRIM(RTRIM(REPLACE(REPLACE(ISNULL(D.TelephoneDomicile,'')+ISNULL(D.TelephoneTravail,'')+ISNULL(D.TelephonePortable,''),' ',''),'-',''))) + '%'
        AND LTRIM(RTRIM(REPLACE(REPLACE(ISNULL(D.TelephoneDomicile,'')+ISNULL(D.TelephoneDomicile,'')+ISNULL(D.TelephonePortable,''),' ',''),'-',''))) <> ''
    )
WHERE D.ID IS NULL
GROUP BY C.ID, FE.ressource, FE.FinalStartDate

内容的提问来源于stack exchange，提问作者alexandre