SQL Server非中心化表性能问题

我在优化一个查询时遇到了困难。表有42列和170万条记录。正如我在主题中提到的，表是一个分散的表。我们有一些int类型的列，其余的都是varchar文本字段。在正常的工作日，这个表每天有10k个插入和大约250k个更新。正如我们的统计数据所示，一个表在一个工作日内回答了550k个选择请求。由于该表具有许多与应用层相关的业务规则，因此存在12种不同的条件。在42列中，我们一直在选择其中的34个。所有的列都是可排序和可搜索的，这就是为什么我们使用动态sql来实现这一点。

ALTER PROCEDURE [dbo].[jo_JobOrder_PerfIndex_Test] 
            @companyID int,--255
            @state int,--null
            @status int,
            @skip int,
            @take int,
            @filterDateType int,
            @technicianID int,
            @filterModelID int,
            @filterCustomerID int,
            @pastTenDate datetime,
            @CategoryID int,
            @TeamID int,
            @CustomSearchParam nvarchar(100),
            @FilterStartDate datetime,
            @FilterEndDate datetime,
            @AssociatedCustomerFilter int,
            @OrderByColumn varchar(50),
            @OrderByDirection nvarchar(50),
            @ForceStrictlyCompany int,
            @deviceType nvarchar(50)
AS
DECLARE @sql nvarchar(max)

SET @Sql=';WITH TempResult AS(Select JobOrder_PerfTable.JobOrderID,ReferenceID,Customer,SerialNo,DeviceName,DeviceTypeName,DeviceBrand,StateID,Staff,JobOrder_PerfTable.GsmNo,StartDate,EndDate,ActualStartDate,ActualEndDate,RelatedFirm,AppointmentDate,ServiceType,CompanyName,Cost,1 as [PassedTime],JobOrder_PerfTable.Address,Description,
Province,District,CurrentFullFilled,DealerTrackCode,CargoDetails,NotifiedFault,State,TagName,JobOrder_PerfTable.SortID,JobOrder_PerfTable.CompanyID,JobOrder_PerfTable.PhoneNumber,JobOrder_PerfTable.Importance,
JobOrder_PerfTable.Status,null as CategoryID,JobOrder_PerfTable.RepeatCount,JobOrder_PerfTable.Attributes,JobOrder_PerfTable.DeliveryShipmentNo,JobOrder_PerfTable.CreatedBy,
0 as [DeviceChange],
0 as [DeviceReturn],
0 as [SNORepeat]
 from JobOrder_PerfTable
 WITH(NOLOCK)
inner join Companies On Companies.CompanyID=JobOrder_PerfTable.CompanyID
WHERE  ((@state is null or JobOrder_PerfTable.StateID=@state) OR @state=-2 AND JobOrder_PerfTable.JobOrderID IN ( select jobOrderID from attendedStaffStatistics where StaffID=@technicianID AND CONVERT(date,AttendedStaffStatistics.InsertDate, 105)=CONVERT(date, getdate(), 105) ))
            AND ((@status = -1 AND JobOrder_PerfTable.Status IN(0,1,10,11,4)) OR (@status=1 AND JobOrder_PerfTable.Status IN(1,4,10,11)) OR
             (@status=2 AND JobOrder_PerfTable.Status IN (0))) AND (@CategoryID is null or JobOrder_PerfTable.CategoryID=@CategoryID)
             AND (@AssociatedCustomerFilter is null or Joborder_PerfTable.CustomerID IN (Select CustomerID from Customers where RelatedFirmID IN (select CustomerID from StaffAssignedToCustomer where StaffID=@AssociatedCustomerFilter) UNION select CustomerID from StaffAssignedToCustomer where StaffID=@AssociatedCustomerFilter))
             AND ((@ForceStrictlyCompany>0 AND JobOrder_PerfTable.CompanyID=@ForceStrictlyCompany) OR (@ForceStrictlyCompany is null AND (JobOrder_PerfTable.CompanyID=@companyID OR Companies.SubCompanyOf=@companyID)))
                 '+ ( CASE WHEN @CustomSearchParam='' THEN '' ELSE (Select dbo.[perf_likeBuilder](@CustomSearchParam)) END)+'
             AND (@technicianID is null or JobOrder_PerfTable.JobOrderID IN (Select AttendedStaff.JobOrderID from AttendedStaff Where AttendedStaff.StaffID=@technicianID))
             AND (@pastTenDate is null or  JobOrder_PerfTable.StartDate<@pastTenDate)
             AND (@filterModelID is null or JobOrder_PerfTable.DeviceModelID=@filterModelID)
             AND (@filterCustomerID is null or JobOrder_PerfTable.CustomerID=@filterCustomerID)
             AND (@deviceType is null or JobOrder_PerfTable.DeviceTypeName=@deviceType)
             AND ((@filterDateType=1 AND (@FilterStartDate is null or convert(datetime, JobOrder_PerfTable.StartDate, 20) between @FilterStartDate and @FilterEndDate)) OR (@filterDateType=2 AND (@FilterStartDate is null or convert(datetime, JobOrder_PerfTable.EndDate, 20) between @FilterStartDate and @FilterEndDate)))
             ), TotalCount AS (Select COUNT(*) as TotalCount from TempResult) 
             
             Select * from TempResult, TotalCount 
          order by '+@OrderByColumn+' '+@OrderByDirection+'

            OFFSET     @skip ROWS      
FETCH NEXT @take ROWS ONLY 
OPTION (RECOMPILE); ';

EXECUTE sp_executesql  @sql, N'@companyID int,
            @state int,
            @status int,
            @skip int,@take int,
            @filterDateType int,
            @technicianID int,
            @filterModelID int,
            @filterCustomerID int,
            @pastTenDate datetime,
            @CategoryID int,
            @TeamID int,
            @CustomSearchParam nvarchar(100),
            @FilterStartDate datetime,
            @FilterEndDate datetime,
            @AssociatedCustomerFilter int,
            @ForceStrictlyCompany int,
            @deviceType nvarchar(50)',
            @companyID=@companyID,
            @state=@state,
            @status=@status,
            @skip=@skip,
            @take=@take,
            @filterDateType=@filterDateType,
            @technicianID=@technicianID,
            @filterModelID=@filterModelID,
            @filterCustomerID=@filterCustomerID,
            @pastTenDate=@pastTenDate,
            @CategoryID=@CategoryID,            
            @TeamID=@TeamID,
            @CustomSearchParam=@CustomSearchParam,
            @FilterStartDate=@FilterStartDate,
            @FilterEndDate=@FilterEndDate,
            @AssociatedCustomerFilter=@AssociatedCustomerFilter,
            @ForceStrictlyCompany=@ForceStrictlyCompany,
            @deviceType=@deviceType

"我已经尝试过的"

在where子句中索引了大多数列
放弃动态填充并将查询转换为常规查询（无更改）
认为可能是执行计划问题和/或参数嗅探，使用OPTION修饰查询（RECOMPILE）
由于表的更新量非常大，因此尝试使用WITH（nolock）查询它

"我已经知道的"

选择所有行是不好的。
索引所有列或索引太多不好（更新查询）
当索引没有覆盖select中的所有列时，会发生键查找，但我不能向索引添加35列。
键查找不一定是坏事。
并行可能会导致查询速度变慢，因为其他表的索引不正确或存在其他问题。（在本例中，其他表有5k行）

当这个查询工作时最多需要3秒钟，并且需要大量的cpu来运行，当其中10个同时工作时，整个系统的响应速度很慢。
有什么建议吗？

我已经重新格式化了你的SQL，使它可读。
你在where子句中有错误的括号数目，我假设我丢失了AND，所以，目前它甚至没有运行，我不能相信在复制查询到这里时没有发生其他错误。
where子句的第一个条件也是多余的，因为company_id已经因为连接 predicate 而相等。
不要使用(@param IS NULL or table.column = @param)，它会把优化器搞得一团糟。相反，在构建动态查询时检查@param。如果它为null，不要在where子句中嵌入任何内容。
OR条件通常也不利于性能。
你有Orders.Status=@status OR Orders.State=@state，它需要两个不同的索引来优化，但是优化器必须根据基数来预测哪一个最好，它不能同时优化两个条件。
通常的折衷方案是将两个查询UNION在一起，但这取决于您的数据。我也不知道您是否使用过两个过滤器，或者是否至少有一个总是null。
同样，没有一个索引可以满足所有可能的排列，索引并不神奇，它们就像书后面的索引，它们是排序的引用，如果你在(a, b)上有一个索引，然后搜索b=1，这个索引基本上是无用的。
所以，如果你想快速查询，你必须定制你的索引，以适应可能的搜索模式。
这确实会减慢更新速度，但这是代价。索引和优化不是魔术。如果你想加快读取速度，你就必须减慢写入速度。

SET @Sql = '
  WITH
    TempResult AS
  (
    SELECT
      <42Rows>
    FROM
      orders
    INNER JOIN
      companies
        ON companies.companyID = orders.companyID
    WHERE
         (Orders.CompanyId=Companies.CompanyID OR Orders.CompanyId=Companies.SubCompanyOf)
    (
      (@status is null or Orders.Status=@status)
      OR @state is null
      OR Orders.State=@state)
    )
    AND '+@whereColumn +' '+@searchTerm+'
    ORDER BY 
      '+@OrderByColumn+' '+@OrderByDirection
        
  EXECUTE sp_executeSql @sql ...(parameters so on)

总之，请更正sql查询的错误，并给予用例和如何设置参数和不设置参数的说明。
还有，读一读这个; https://www.sommarskog.se/dyn-search.html
这里面有很多细节，但是对于您的场景，这是您需要学习理解和处理的。

SQL Server非中心化表性能问题

1条答案

相关问题

热门标签

最新问答