如何优化C# ASP.NET搜索页的查询与代码以提升加载速度

当前问题：数据量达5000条时，搜索加载耗时长达2分钟，现有逻辑是通过两个存储过程实现——第一个根据参数获取候选人详情，第二个通过返回的候选人ID统计，再用Foreach匹配ID。以下是第一个存储过程的原代码：

declare @FirstName varchar(500)='',
@LastName varchar(500)='',
@Phone varchar(500)='',
@Email varchar(500)='',
@Specialty varchar(500)='',
@AdditionalLicensedStates varchar(500)='AL',
@DesiredLocation varchar(50)='',
@CompactState varchar(50)=''   
SET NOCOUNT ON;    
declare @query nvarchar(max)=' '
set @query=@query+
'SELECT       
      
T.CandidateId,''''Ranking,T.[Status], t.FirstName,t.LastName, t.Phone, t.[Email],t.ID AS TroveId      
,t.Desired,t.Licensed,t.Specialty,t.CallBack,t.LastContact    
,t.LastContactNote,0 MatchingJobs  ,t.AvailableDate ,T.DepartmentId   
,0 DocumentId,''''Division,''''EMRSystem,''''Application,T.Compactstate  
FROM (        
select T.CandidateId,T.Status, C.FirstName,C.LastName, C.Phone, C.Email,T.ID,T.DepartmentId,j.DesiredStates as Desired,
j.Licensed,j.Specialty,T.Compactstate,j.AvailableDate,cb.CallbackDate CallBack,convert(date,n.DateCreated) LastContact,
n.Notes LastContactNote from T1 T(NOLOCK)
INNER JOIN Candidate C(NOLOCK) on T.CandidateId=  C.Id and T.DepartmentId=1 AND T.Profile=''0''       
INNER JOIN T2 P(NOLOCK) on T.CandidateId=P.TroveCandidateId      
LEFT join  T3(Nolock) j on j.CandidateId=T.CandidateId  
Inner join T4 js on js.jackieId=j.Id
LEFT  JOIN dbo.T5(Nolock) N ON N.ObjectID = T.CandidateId and n.IsLatest=1 and n.Category=''3'' and n.NoteType=''NormalNote''      
LEFT JOIN  T6(Nolock) cb on cb.CandidateId=t.CandidateId and cb.IsLatest=1 and cb.CallBackType=''Trove'' 
where      
('''+@FirstName+''' ='''' or C.FirstName like ''%'+@FirstName+'%'') AND      
('''+@LastName+''' ='''' or C.LastName like ''%'+@LastName+'%'') AND      
('''+@Phone+''' =''''  or C.Phone ='''+@Phone+''') AND      
('''+@Email+''' =''''  or C.Email like ''%'+@Email+'%'')  AND     
('''+@Specialty+''' =''''  or cast(Js.specialtyId as varchar(10)) = '''+@Specialty+''')  AND
('''+@AdditionalLicensedStates+''' =''''  or J.Licensed like ''%'+@AdditionalLicensedStates+'%'') AND
('''+@DesiredLocation+''' =''''  or J.DesiredStates like ''%'+@DesiredLocation+'%'') AND
('''+@CompactState+''' =''''  or t.CompactState like ''%'+@CompactState+'%'')
     
)         as t  '    
--  print @query
exec (@query)

优化建议 #

• 替换动态SQL为参数化查询：原代码用字符串拼接生成SQL，存在SQL注入风险，且SQL Server无法缓存执行计划。改用sp_executesql传递参数，可复用执行计划，大幅提升重复查询性能。
• 添加针对性索引：根据查询中的过滤条件和连接字段创建索引，示例如下：
  • CREATE NONCLUSTERED INDEX IX_Candidate_Name_Phone_Email ON Candidate(FirstName, LastName, Phone, Email) INCLUDE(Id)
  • CREATE NONCLUSTERED INDEX IX_T1_CandidateId_DepartmentId_Profile ON T1(CandidateId, DepartmentId, Profile) INCLUDE(Status, ID, Compactstate)
  • CREATE NONCLUSTERED INDEX IX_T2_TroveCandidateId ON T2(TroveCandidateId)
  • CREATE NONCLUSTERED INDEX IX_T3_CandidateId ON T3(CandidateId) INCLUDE(DesiredStates, Licensed, Specialty, AvailableDate, Id)
  • CREATE NONCLUSTERED INDEX IX_T4_JackieId_SpecialtyId ON T4(jackieId, specialtyId)
  • CREATE NONCLUSTERED INDEX IX_T5_ObjectID_IsLatest_Category_NoteType ON T5(ObjectID, IsLatest, Category, NoteType) INCLUDE(DateCreated, Notes)
  • CREATE NONCLUSTERED INDEX IX_T6_CandidateId_IsLatest_CallBackType ON T6(CandidateId, IsLatest, CallBackType) INCLUDE(CallbackDate)
• 优化模糊查询：LIKE '%xxx%'会触发全表扫描，无法使用索引。如果业务允许，改为前缀匹配'xxx%'；若必须支持中间匹配，考虑创建全文索引优化模糊搜索性能。
• 合并存储过程逻辑：无需分两次查询再用Foreach匹配ID，直接在获取候选人详情的查询中集成统计逻辑，减少数据库往返次数和内存消耗。
• 谨慎使用NOLOCK：NOLOCK会读取未提交的脏数据，确认业务场景允许脏读再保留，否则去掉NOLOCK提示，避免数据不一致问题。

修改后的参数化存储过程示例 #

CREATE PROCEDURE GetCandidateDetails
    @FirstName varchar(500) = '',
    @LastName varchar(500) = '',
    @Phone varchar(500) = '',
    @Email varchar(500) = '',
    @Specialty varchar(500) = '',
    @AdditionalLicensedStates varchar(500) = 'AL',
    @DesiredLocation varchar(50) = '',
    @CompactState varchar(50) = ''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = N'
    SELECT       
        T.CandidateId, '''' AS Ranking, T.[Status], t.FirstName, t.LastName, t.Phone, t.[Email], t.ID AS TroveId      
        , t.Desired, t.Licensed, t.Specialty, t.CallBack, t.LastContact    
        , t.LastContactNote, 0 AS MatchingJobs, t.AvailableDate, T.DepartmentId   
        , 0 AS DocumentId, '''' AS Division, '''' AS EMRSystem, '''' AS Application, T.Compactstate  
    FROM (        
        SELECT 
            T.CandidateId, T.Status, C.FirstName, C.LastName, C.Phone, C.Email, T.ID, T.DepartmentId, 
            j.DesiredStates AS Desired, j.Licensed, j.Specialty, T.Compactstate, j.AvailableDate,
            cb.CallbackDate AS CallBack, CONVERT(DATE, n.DateCreated) AS LastContact, n.Notes AS LastContactNote 
        FROM T1 T(NOLOCK)
        INNER JOIN Candidate C(NOLOCK) ON T.CandidateId = C.Id AND T.DepartmentId = 1 AND T.Profile = ''0''       
        INNER JOIN T2 P(NOLOCK) ON T.CandidateId = P.TroveCandidateId      
        LEFT JOIN T3(Nolock) j ON j.CandidateId = T.CandidateId  
        INNER JOIN T4 js ON js.jackieId = j.Id
        LEFT JOIN dbo.T5(Nolock) N ON N.ObjectID = T.CandidateId AND n.IsLatest = 1 AND n.Category = ''3'' AND n.NoteType = ''NormalNote''      
        LEFT JOIN T6(Nolock) cb ON cb.CandidateId = t.CandidateId AND cb.IsLatest = 1 AND cb.CallBackType = ''Trove'' 
        WHERE      
            (@FirstName = '''' OR C.FirstName LIKE ''%'' + @FirstName + ''%'') AND      
            (@LastName = '''' OR C.LastName LIKE ''%'' + @LastName + ''%'') AND      
            (@Phone = '''' OR C.Phone = @Phone) AND      
            (@Email = '''' OR C.Email LIKE ''%'' + @Email + ''%'')  AND     
            (@Specialty = '''' OR CAST(Js.specialtyId AS VARCHAR(10)) = @Specialty)  AND
            (@AdditionalLicensedStates = '''' OR J.Licensed LIKE ''%'' + @AdditionalLicensedStates + ''%'') AND
            (@DesiredLocation = '''' OR J.DesiredStates LIKE ''%'' + @DesiredLocation + ''%'') AND
            (@CompactState = '''' OR t.CompactState LIKE ''%'' + @CompactState + ''%'')
    ) AS t';

    EXEC sp_executesql @SQL, 
        N'@FirstName varchar(500), @LastName varchar(500), @Phone varchar(500), @Email varchar(500),
          @Specialty varchar(500), @AdditionalLicensedStates varchar(500), @DesiredLocation varchar(50), @CompactState varchar(50)',
        @FirstName = @FirstName,
        @LastName = @LastName,
        @Phone = @Phone,
        @Email = @Email,
        @Specialty = @Specialty,
        @AdditionalLicensedStates = @AdditionalLicensedStates,
        @DesiredLocation = @DesiredLocation,
        @CompactState = @CompactState;
END
GO

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