sql - 需要建议。以下更改导致我的存储过程执行 5 分钟

Question

我有以下看法：

ALTER VIEW [dbo].[vAccount]
AS
with cte_accounts_data AS 
(
SELECT
         null as CompanyVendorAccountId
        ,CAST(0 as bit) as IsVendorAccount
        ,null as VendorAccountReference
        ,null as VendorCompanyId
        ,a.[AccountId]
        ,a.[CompanyId]
        ,a.[CompanyAccountTypeId]
        ,ag.[CompanyAccountGroupId]
        ,ag.[Name] as CompanyAccountGroupName
        ,a.[RegionId]
        ,a.[Name]
        ,CONCAT(c.ShortName + ': ', a.[Name], ' [' + a.[Code] +']') AS DisplayName
        ,a.[Code]
        ,a.[Address]
        ,a.[Email]
        ,a.[IncludeEscalationEmail]
        ,a.[GPSLat]
        ,a.[GPSLong]
        ,a.[Telephone]
        ,a.[VATNumber]
        ,a.[AutoReceive]
        ,a.[AutoIssue]
        ,a.[IsBillableToAccount]
        ,a.[BillingStart]
        ,a.[IsEquipmentDepot]
        ,a.[IsShiftAttendanceEnabled]
        ,a.[ShiftMinHoursForLunchDeduction]
        ,a.[NightShiftStart]
        ,a.[NightShiftEnd]
        ,a.[ShiftStartDayOfMonth]
        ,a.[OperatingHoursStart]
        ,a.[OperatingHoursEnd]
        ,a.[LoadBays]
        ,a.[LoadInterval]
        ,a.[ArrivalInterval]
        ,a.[OverrideStockTakeCloseBalanceTime]
        --,a.[RFEquipment]
        ,a.[temp_IgnoreVendorIssueViaSAP]
        ,a.[Archived]
        ,a.[CreatedDate]
        ,a.[CreatedBy_PersonId]
        ,a.[UpdatedDate]
        ,a.[UpdatedBy_PersonId]
        ,cat.Name as CompanyAccountTypeName
        ,at.Name as AccountTypeName
        ,at.AccountTypeId
        ,at.EnumAccountType
        ,r.Name as Region
        ,c.Name as Company
        ,CONCAT(c.Code, ': ', a.Name, ' ',a.Code, ' ', c.Name, ' ', r.Name, ' ', at.Name, ' ', r.Code, ' ') as ViewSearchColumn
    FROM
        [Account] a
    JOIN Company c on (a.CompanyId = c.CompanyId)
    JOIN CompanyAccountType cat on (a.CompanyAccountTypeId = cat.CompanyAccountTypeId)
    JOIN AccountType at on (cat.AccountTypeId = at.AccountTypeId)
    LEFT OUTER JOIN vCompanyAccountGroup ag on (a.CompanyAccountGroupId = ag.CompanyAccountGroupId)
    LEFT OUTER JOIN Region r on (a.RegionId = r.RegionId)

    UNION 

    SELECT
         cv.[CompanyVendorAccountId]
        ,CAST(1 as bit) as IsVendorAccount
        ,cv.[VendorAccountReference]
        ,a.[CompanyId] as VendorCompanyId
        ,a.[AccountId]
        ,cv.[CompanyId]
        ,cv.[CompanyAccountTypeId]
        ,ag.[CompanyAccountGroupId]
        ,ag.[Name] as CompanyAccountGroupName
        ,a.[RegionId]
        ,a.[Name]
        ,CONCAT(c.ShortName + ': ', a.[Name], ' [' + cv.[VendorAccountReference] +']') AS DisplayName
        ,cv.[VendorAccountReference] as [Code]
        ,a.[Address]
        ,a.[Email]
        ,a.[IncludeEscalationEmail]
        ,a.[GPSLat]
        ,a.[GPSLong]
        ,a.[Telephone]
        ,a.[VATNumber]
        ,a.[AutoReceive]
        ,a.[AutoIssue]
        ,a.[IsBillableToAccount]
        ,a.[BillingStart]
        ,a.[IsEquipmentDepot]
        ,a.[IsShiftAttendanceEnabled]
        ,a.[ShiftMinHoursForLunchDeduction]
        ,a.[NightShiftStart]
        ,a.[NightShiftEnd]
        ,a.[ShiftStartDayOfMonth]
        ,a.[OperatingHoursStart]
        ,a.[OperatingHoursEnd]
        ,a.[LoadBays]
        ,a.[LoadInterval]
        ,a.[ArrivalInterval]
        ,a.[OverrideStockTakeCloseBalanceTime]
        --,a.[RFEquipment]
        ,a.[temp_IgnoreVendorIssueViaSAP]
        ,cv.[Archived]
        ,cv.[CreatedDate]
        ,cv.[CreatedBy_PersonId]
        ,cv.[UpdatedDate]
        ,cv.[UpdatedBy_PersonId]
        ,cat.Name as CompanyAccountTypeName
        ,at.Name as AccountTypeName
        ,at.AccountTypeId
        ,at.EnumAccountType
        ,r.Name as Region
        ,c.Name as Company
        ,CONCAT(c.Code, ': ', a.Name, ' ',a.Code, ' ', c.Name, ' ', r.Name, ' ', at.Name, ' ', r.Code, ' ') as ViewSearchColumn
    FROM
        [CompanyVendorAccount] cv
    JOIN Company c on (cv.CompanyId = c.CompanyId)
    JOIN CompanyAccountType cat on (cv.CompanyAccountTypeId = cat.CompanyAccountTypeId)
    JOIN AccountType at on (cat.AccountTypeId = at.AccountTypeId)
    JOIN Account a on (cv.VendorAccountId = a.AccountId)
    LEFT OUTER JOIN Region r on (a.RegionId = r.RegionId)
    LEFT OUTER JOIN vCompanyAccountGroup ag on (cv.CompanyAccountGroupId = ag.CompanyAccountGroupId)
    WHERE
        cv.CompanyId != a.CompanyId
)
,cte_ranking_order as 
(
    SELECT ROW_NUMBER() over (ORDER BY AccountId, CompanyId) as rankNumber,* FROM cte_accounts_data
)
SELECT  [CompanyVendorAccountId] 
       ,IsVendorAccount
       ,[VendorAccountReference]
       ,[VendorCompanyId]
       ,[AccountId]
       ,[CompanyId]
       ,[CompanyAccountTypeId]
       ,[CompanyAccountGroupId]
       ,[CompanyAccountGroupName]
       ,[RegionId]
       ,[Name]
       ,[DisplayName]
       ,[Code]
       ,[Address]
       ,[Email]
       ,[IncludeEscalationEmail]
       ,[GPSLat]
       ,[GPSLong]
       ,[Telephone]
       ,[VATNumber]
       ,[AutoReceive]
       ,[AutoIssue]
       ,[IsBillableToAccount]
       ,[BillingStart]
       ,[IsEquipmentDepot]
       ,[IsShiftAttendanceEnabled]
       ,[ShiftMinHoursForLunchDeduction]
       ,[NightShiftStart]
       ,[NightShiftEnd]
       ,[ShiftStartDayOfMonth]
       ,[OperatingHoursStart]
       ,[OperatingHoursEnd]
       ,[LoadBays]
       ,[LoadInterval]
       ,[ArrivalInterval]
       ,[OverrideStockTakeCloseBalanceTime]
       ,[temp_IgnoreVendorIssueViaSAP]
       ,[Archived]
       ,[CreatedDate]
       ,[CreatedBy_PersonId]
       ,[UpdatedDate]
       ,[UpdatedBy_PersonId]
       ,[CompanyAccountTypeName]
       ,[AccountTypeName]
       ,[AccountTypeId]
       ,[EnumAccountType]
       ,[Region]
       ,[Company]
       ,[ViewSearchColumn]
    FROM cte_ranking_order where rankNumber = 1
GO

我将其修改为如上所示。

以下是原始视图：

    ALTER VIEW [dbo].[vAccount]
AS

with cte_accounts_data AS 
(
SELECT
         null as CompanyVendorAccountId
        ,CAST(0 as bit) as IsVendorAccount
        ,null as VendorAccountReference
        ,null as VendorCompanyId
        ,a.[AccountId]
        ,a.[CompanyId]
        ,a.[CompanyAccountTypeId]
        ,ag.[CompanyAccountGroupId]
        ,ag.[Name] as CompanyAccountGroupName
        ,a.[RegionId]
        ,a.[Name]
        ,CONCAT(c.ShortName + ': ', a.[Name], ' [' + a.[Code] +']') AS DisplayName
        ,a.[Code]
        ,a.[Address]
        ,a.[Email]
        ,a.[IncludeEscalationEmail]
        ,a.[GPSLat]
        ,a.[GPSLong]
        ,a.[Telephone]
        ,a.[VATNumber]
        ,a.[AutoReceive]
        ,a.[AutoIssue]
        ,a.[IsBillableToAccount]
        ,a.[BillingStart]
        ,a.[IsEquipmentDepot]
        ,a.[IsShiftAttendanceEnabled]
        ,a.[ShiftMinHoursForLunchDeduction]
        ,a.[NightShiftStart]
        ,a.[NightShiftEnd]
        ,a.[ShiftStartDayOfMonth]
        ,a.[OperatingHoursStart]
        ,a.[OperatingHoursEnd]
        ,a.[LoadBays]
        ,a.[LoadInterval]
        ,a.[ArrivalInterval]
        ,a.[OverrideStockTakeCloseBalanceTime]
        --,a.[RFEquipment]
        ,a.[temp_IgnoreVendorIssueViaSAP]
        ,a.[Archived]
        ,a.[CreatedDate]
        ,a.[CreatedBy_PersonId]
        ,a.[UpdatedDate]
        ,a.[UpdatedBy_PersonId]
        ,cat.Name as CompanyAccountTypeName
        ,at.Name as AccountTypeName
        ,at.AccountTypeId
        ,at.EnumAccountType
        ,r.Name as Region
        ,c.Name as Company
        ,CONCAT(c.Code, ': ', a.Name, ' ',a.Code, ' ', c.Name, ' ', r.Name, ' ', at.Name, ' ', r.Code, ' ') as ViewSearchColumn
    FROM
        [Account] a
    JOIN Company c on (a.CompanyId = c.CompanyId)
    JOIN CompanyAccountType cat on (a.CompanyAccountTypeId = cat.CompanyAccountTypeId)
    JOIN AccountType at on (cat.AccountTypeId = at.AccountTypeId)
    LEFT OUTER JOIN vCompanyAccountGroup ag on (a.CompanyAccountGroupId = ag.CompanyAccountGroupId)
    LEFT OUTER JOIN Region r on (a.RegionId = r.RegionId)

    UNION 

    SELECT
         cv.[CompanyVendorAccountId]
        ,CAST(1 as bit) as IsVendorAccount
        ,cv.[VendorAccountReference]
        ,a.[CompanyId] as VendorCompanyId
        ,a.[AccountId]
        ,cv.[CompanyId]
        ,cv.[CompanyAccountTypeId]
        ,ag.[CompanyAccountGroupId]
        ,ag.[Name] as CompanyAccountGroupName
        ,a.[RegionId]
        ,a.[Name]
        ,CONCAT(c.ShortName + ': ', a.[Name], ' [' + cv.[VendorAccountReference] +']') AS DisplayName
        ,cv.[VendorAccountReference] as [Code]
        ,a.[Address]
        ,a.[Email]
        ,a.[IncludeEscalationEmail]
        ,a.[GPSLat]
        ,a.[GPSLong]
        ,a.[Telephone]
        ,a.[VATNumber]
        ,a.[AutoReceive]
        ,a.[AutoIssue]
        ,a.[IsBillableToAccount]
        ,a.[BillingStart]
        ,a.[IsEquipmentDepot]
        ,a.[IsShiftAttendanceEnabled]
        ,a.[ShiftMinHoursForLunchDeduction]
        ,a.[NightShiftStart]
        ,a.[NightShiftEnd]
        ,a.[ShiftStartDayOfMonth]
        ,a.[OperatingHoursStart]
        ,a.[OperatingHoursEnd]
        ,a.[LoadBays]
        ,a.[LoadInterval]
        ,a.[ArrivalInterval]
        ,a.[OverrideStockTakeCloseBalanceTime]
        --,a.[RFEquipment]
        ,a.[temp_IgnoreVendorIssueViaSAP]
        ,cv.[Archived]
        ,cv.[CreatedDate]
        ,cv.[CreatedBy_PersonId]
        ,cv.[UpdatedDate]
        ,cv.[UpdatedBy_PersonId]
        ,cat.Name as CompanyAccountTypeName
        ,at.Name as AccountTypeName
        ,at.AccountTypeId
        ,at.EnumAccountType
        ,r.Name as Region
        ,c.Name as Company
        ,CONCAT(c.Code, ': ', a.Name, ' ',a.Code, ' ', c.Name, ' ', r.Name, ' ', at.Name, ' ', r.Code, ' ') as ViewSearchColumn
    FROM
        [CompanyVendorAccount] cv
    JOIN Company c on (cv.CompanyId = c.CompanyId)
    JOIN CompanyAccountType cat on (cv.CompanyAccountTypeId = cat.CompanyAccountTypeId)
    JOIN AccountType at on (cat.AccountTypeId = at.AccountTypeId)
    JOIN Account a on (cv.VendorAccountId = a.AccountId)
    LEFT OUTER JOIN Region r on (a.RegionId = r.RegionId)
    LEFT OUTER JOIN vCompanyAccountGroup ag on (cv.CompanyAccountGroupId = ag.CompanyAccountGroupId)
    WHERE
        cv.CompanyId != a.CompanyId

我唯一添加的想法是这个cte：

 ,cte_ranking_order as 
(
    SELECT ROW_NUMBER() over (PARTITION BY AccountId, CompanyID ORDER BY AccountId, CompanyId) as rankNumber,* FROM cte_accounts_data
)

这样做的目的是仅从原始选择 lsits 中选择唯一帐户，方法是给它一个 aROW_NUMBER()并将数据分区为over (PARTITION BY AccountId, CompanyID ORDER BY AccountId, CompanyId).

当存储过程尝试将此视图与其他一些视图连接时，传入一个特定@AccountId的 ，存储过程执行大约 5 分钟。

我真的不确定是什么原因造成的？

标签： sqlsql-serverssmspartitioning

Answer 1

我打算将此添加为评论，但我真的无法给你一个很好的例子来说明我想说什么......

您是否消除了 SQL 的参数嗅探作为罪魁祸首？我已经发生过很多次了，在查询窗口中运行查询很快，但 sp 需要永远。这可能在参数嗅探开始时发生。您可以通过将传递的变量分配给 SP 中的新变量然后引用这些变量来避免这种情况。

因此，例如：

CREATE PROCEDURE dbo.MyProcedureName(
    @AccountID INT
)
BEGIN

    -- Prevent parameter sniffing.
    DECLARE @MyAccountID INT = @AccountID;

    SELECT
        ...
    FROM dbo.MyView
    WHERE
        MyView.AccountID = @MyAccountID;

   ...

END

只是一个想法，但这曾经发生在我身上，我用头撞墙试图弄清楚。

我知道还有其他方法（也许更好）来处理这个特定问题，但这个解决方案一直对我有用。

如果有兴趣，可以对这个主题进行一些额外的阅读： https ://www.red-gate.com/simple-talk/sql/t-sql-programming/parameter-sniffing/