SQL查询优化与索引创建咨询：两张表关联查询提速

Let's break down how to speed up this query and get it running way faster than 11 seconds. The main issues right now are non-sargable expressions (functions applied to columns in joins/filters) and missing targeted indexes that the query optimizer can leverage.

First: Rewrite the Query to Fix Date Conversion Issues #

Your original query uses FORMAT() to convert the DateTime column to a string, then uses that string for date comparisons in the join. This is a big performance killer—SQL Server can't use any indexes on DateTime when you wrap it in a function, so it has to scan the entire table every time.

Here's the optimized version, with no unnecessary string conversions:

SELECT TOP 100 
    sto.DWH_ID,
    -- Keep FORMAT only for final output (presentation, not filtering/joining)
    FORMAT(sto.[DateTime], 'dd-MM-yyyy HH:mm') AS date_time,
    sto.TimeDiff,
    DATEADD(second, sto.TimeDiff, sto.[DateTime]) AS [End Date],
    sto.SPS_Bereich,
    sto.txtName
FROM [Stoerdaten].[sta].[Stoerungen] sto
JOIN [IgnitionServer].[dbo].[scheduled_events_ISTProduction] cal
    -- Simplified time overlap check (raw datetime comparisons only)
    ON sto.[DateTime] <= cal.end_date
    AND DATEADD(second, sto.TimeDiff, sto.[DateTime]) >= cal.start_date
WHERE 
    sto.Classname = 'Alarm' 
    AND sto.TimeDiff > 60
    AND cal.typ = 1
ORDER BY sto.DWH_ID DESC

Key fixes here: #

• Removed the unnecessary subquery—we can do everything in a single join
• Got rid of FORMAT() in the join conditions: we compare raw datetime values directly, which is index-friendly
• Simplified the overlap logic: instead of checking both the alarm's start and end against the event's full range, we just verify the two time periods overlap (this is logically identical, but much easier for the optimizer to handle)

Second: Create Targeted Indexes #

Your only existing index is on DWH_ID, which helps with the final ORDER BY, but does nothing for filtering or joining. Let's add two indexes tailored exactly to this query's needs.

Index for [sta].[Stoerungen] #

This is a covering index—it includes every column the query needs, so SQL Server can answer the entire query directly from the index without touching the base table:

CREATE NONCLUSTERED INDEX IX_Stoerungen_Alarm_TimeDiff
ON [Stoerdaten].[sta].[Stoerungen] (Classname, TimeDiff, [DateTime])
INCLUDE (DWH_ID, SPS_Bereich, txtName);

• Leading columns: Classname (we filter for ='Alarm' first) then TimeDiff (we need >60), followed by DateTime (used in the join). This lets SQL Server quickly narrow down to the exact rows we care about.
• Included columns: All the other columns we select, so no expensive "key lookups" are needed to pull extra data from the base table.

Index for [dbo].[scheduled_events_ISTProduction] #

This index focuses on the filter and join conditions for the calendar table:

CREATE NONCLUSTERED INDEX IX_scheduled_events_Type1_Dates
ON [IgnitionServer].[dbo].[scheduled_events_ISTProduction] (typ, start_date, end_date);

• Leading columns: typ (we filter for =1 first), then start_date and end_date (used in the overlap check). This lets SQL Server quickly find all type 1 events and efficiently compare their time ranges to the alarms.

Third: Verify with the Execution Plan #

After making these changes, always check the actual execution plan in SSMS (hit Ctrl+M before running the query) to confirm:

• No full table scans are happening (look for "Index Seek" instead of "Table Scan")
• The new indexes are being used
• There are no expensive operations like key lookups or sorts

Why This Works #

• Sargable queries: By avoiding functions on columns used in filters/joins, SQL Server can use indexes to locate rows in milliseconds instead of scanning millions of rows.
• Covering indexes: These eliminate the need to go back to the base table, cutting down on I/O (the biggest cause of slow queries).
• Simplified logic: The overlap condition is easier for the query optimizer to optimize, leading to a more efficient execution plan.

内容的提问来源于stack exchange，提问作者Tarun. P