SQL查询性能调优求助：长耗时查询优化方案咨询

Alright, let's dig into optimizing this slow SQL query—22 minutes to return just over 1k results is way too sluggish, so we can attack this from several key angles to cut down that runtime drastically.

1. Shrink Your Dataset Early (Critical for Large Tables) #

Right now, some filtering logic is embedded in join conditions, but we can make it explicit for the query optimizer to prioritize trimming data before doing heavy joins. Let's use a CTE to first grab only the SampleIds that match your account and date criteria—this will drastically reduce the number of rows we need to join against your huge tables (SampleTestFractionsView and ValidatedResults).

WITH FilteredSamples AS (
    SELECT sv.SampleId
    FROM dbo.ClientSamples cs
    INNER JOIN dbo.SamplesView sv 
        ON cs.SampleId = sv.SampleId
    WHERE cs.AccountCode IN ('A00052498', 'A00091603', 'AFR000790', 'AFR025580', 'AFR033702', 'AFR034669', 'AFR065301')
      AND sv.ReceptionDate >= @p__linq__2 
      AND sv.ReceptionDate <= @p__linq__1
)
SELECT DISTINCT 
    1 AS [C1],
    ptv.DisplayText + ' (' + vr.ParameterCode + ')' AS [C2],
    vr.ParameterCode,
    ptv.ParameterId
FROM FilteredSamples fs
INNER JOIN dbo.SampleTestFractionsView stfv ON fs.SampleId = stfv.SampleId
INNER JOIN dbo.ValidatedResults vr ON stfv.SampleTestFractionId = vr.SampleTestFractionId
INNER JOIN dbo.ParameterTranslationsView ptv 
    ON vr.ParameterId = ptv.ParameterId
    AND ptv.LanguageId = @p__linq__0
WHERE 
    ptv.DisplayText + ' (' + vr.ParameterCode + ')' IS NOT NULL
    AND LEN(ptv.DisplayText + ' (' + vr.ParameterCode + ')') > 0
    AND ptv.DisplayText + ' (' + vr.ParameterCode + ')' <> ' '

2. Eliminate Redundant Calculations #

Your query repeats the string concatenation [Extent5].[DisplayText] + ' (' + [Extent4].[ParameterCode] + ')' four times—this forces the database to recalculate the same value over and over, wasting CPU cycles. Let's compute it once in a CTE, then reference the result:

WITH FilteredSamples AS (
    SELECT sv.SampleId
    FROM dbo.ClientSamples cs
    INNER JOIN dbo.SamplesView sv 
        ON cs.SampleId = sv.SampleId
    WHERE cs.AccountCode IN ('A00052498', 'A00091603', 'AFR000790', 'AFR025580', 'AFR033702', 'AFR034669', 'AFR065301')
      AND sv.ReceptionDate >= @p__linq__2 
      AND sv.ReceptionDate <= @p__linq__1
),
JoinedData AS (
    SELECT 
        ptv.DisplayText + ' (' + vr.ParameterCode + ')' AS DisplayCode,
        vr.ParameterCode,
        ptv.ParameterId
    FROM FilteredSamples fs
    INNER JOIN dbo.SampleTestFractionsView stfv ON fs.SampleId = stfv.SampleId
    INNER JOIN dbo.ValidatedResults vr ON stfv.SampleTestFractionId = vr.SampleTestFractionId
    INNER JOIN dbo.ParameterTranslationsView ptv 
        ON vr.ParameterId = ptv.ParameterId
        AND ptv.LanguageId = @p__linq__0
)
SELECT DISTINCT 
    1 AS [C1],
    DisplayCode AS [C2],
    ParameterCode,
    ParameterId
FROM JoinedData
WHERE 
    DisplayCode IS NOT NULL
    AND LEN(DisplayCode) > 0
    AND DisplayCode <> ' '

Also, note that CAST(LEN(...) AS int) is redundant—LEN() already returns an integer, so we can simplify that condition to LEN(DisplayCode) > 0.

3. Add Targeted Indexes (The Big Win for Large Tables) #

Your two largest tables (SampleTestFractionsView with 14.8M rows, ValidatedResults with 74.6M rows) are the bottleneck here. Let's create indexes that let the database quickly find and retrieve only the data it needs:

• For ValidatedResults: We join on SampleTestFractionId and need ParameterId/ParameterCode—create a covering index to avoid key lookups:

  CREATE NONCLUSTERED INDEX IX_ValidatedResults_SampleTestFractionId_Include 
  ON dbo.ValidatedResults(SampleTestFractionId)
  INCLUDE (ParameterId, ParameterCode);
  

• For SampleTestFractionsView: If this view is built on a base table (e.g., SampleTestFractions), add an index on SampleId that includes SampleTestFractionId:

  CREATE NONCLUSTERED INDEX IX_SampleTestFractions_SampleId_Include 
  ON dbo.SampleTestFractions(SampleId)
  INCLUDE (SampleTestFractionId);
  

• For ClientSamples: Speed up the account filter with a composite index:

  CREATE NONCLUSTERED INDEX IX_ClientSamples_AccountCode_SampleId 
  ON dbo.ClientSamples(AccountCode)
  INCLUDE (SampleId);
  

• For SamplesView: If the base table has a ReceptionDate column, add an index on SampleId + ReceptionDate to speed up the date filter:

  CREATE NONCLUSTERED INDEX IX_Samples_SampleId_ReceptionDate 
  ON dbo.Samples(SampleId, ReceptionDate);
  

4. Reassess the Need for DISTINCT #

You're using DISTINCT to remove duplicates, but first ask: why are duplicates happening? If it's because joins are creating redundant rows (e.g., one sample mapping to multiple test fractions/results), DISTINCT is necessary—but sometimes GROUP BY is more efficient for the optimizer. Try swapping DISTINCT for a GROUP BY on your selected columns:

SELECT 
    1 AS [C1],
    DisplayCode AS [C2],
    ParameterCode,
    ParameterId
FROM JoinedData
WHERE 
    DisplayCode IS NOT NULL
    AND LEN(DisplayCode) > 0
    AND DisplayCode <> ' '
GROUP BY DisplayCode, ParameterCode, ParameterId;

5. Audit Your Views #

Views can hide inefficient logic or prevent the optimizer from generating optimal plans. Check:

• Do your views (SamplesView, SampleTestFractionsView) include unnecessary joins or filters?
• Can you replace the views with direct joins to their base tables? This gives the optimizer more flexibility to rearrange joins and filters.
• If the views are simple (no aggregates/unions), consider creating indexed views to precompute and store their results.

6. Update Statistics #

Outdated statistics can lead the query optimizer to make bad decisions (like choosing table scans over index seeks). Refresh stats for all involved tables:

UPDATE STATISTICS dbo.ClientSamples;
UPDATE STATISTICS dbo.Samples;
UPDATE STATISTICS dbo.SampleTestFractions;
UPDATE STATISTICS dbo.ValidatedResults;
UPDATE STATISTICS dbo.ParameterTranslations;

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