title: Need advice. The following changes causes my stored procedure to EXECUTE for 5mins
body: <p>I have the following view:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which I modified to look like the above.  </p>
  
  <p>Below is the original View:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>The only think I added, was this cte:</p>
  
  <pre><code> ,cte_ranking_order as 
  (
      SELECT ROW_NUMBER() over (PARTITION BY AccountId, CompanyID ORDER BY AccountId, CompanyId) as rankNumber,* FROM cte_accounts_data
  )
  </code></pre>
  
  <p>The point of this was to only select unique Accounts from the original select lsits, by giving it a <code>ROW_NUMBER()</code> and partitioning the data with <code>over (PARTITION BY AccountId, CompanyID ORDER BY AccountId, CompanyId)</code>.</p>
  
  <p>When a stored procedure tries to join this View with some other views, passed in a specific <code>@AccountId</code>, the stored procedure executes for about 5min.</p>
  
  <p>I really am not sure what could posiible cause this?</p>