Suggest to change TenantProject view

We have a lot projects (~4800) and this view is realy very long exec (about 17-20 seconds).
I suggest to update view:

ALTER VIEW [dbo].[TenantProject]
AS

WITH tmp(Id, Ids, ProjectIds) AS
(
    SELECT
        Tenant.Id,
		
        LEFT(RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 ), CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 )) - 1 ),
        STUFF(RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 ), 1, CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 )), '')
    FROM Tenant
    UNION all
	
    SELECT
       Id,
       LEFT(RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 ), CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 )) - 1 ),
        STUFF(RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 ), 1, CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 )), '')
    FROM tmp
    WHERE
        ProjectIds > '' 
)

select Id as TenantId, Ids as ProjectId  from tmp
GO

Hi,

Thanks for getting in touch and the suggestion. How many rows are there in that table for you?

Which API or operation is slow due to this table (so that I ran reproduce and test a fix).

Rob

Hello.
select count(*) from tenant
71

select count(*) from Project
4963

select count(*) from TenantProject
2171

We found that some of /api/variables/variableset-XXX api requests have very long duration 20-24 seconds.

I did little mistake. Now I change it:

USE [octopus_db]
GO

/****** Object:  View [dbo].[TenantProject]    Script Date: 8/28/2018 2:14:45 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

------------------------------------------------
-- Add TenantProject view
------------------------------------------------

ALTER VIEW [dbo].[TenantProject]
AS
WITH tmp(Id, Ids, ProjectIds) AS
(
    SELECT
        Tenant.Id,
		
        LEFT(RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 ), CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 )) - 1 ),
        STUFF(RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 ), 1, CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 )), '')
    FROM Tenant
    UNION all
	
    SELECT
       Id,
       LEFT(RIGHT(rtrim(ProjectIds), LEN(ProjectIds) ), CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds) )) - 1 ),
        STUFF(RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 ), 1, CHARINDEX('|', RIGHT(rtrim(ProjectIds), LEN(ProjectIds)-1 )), '')
    FROM tmp
    WHERE
        ProjectIds > ''
)

select Id as TenantId, Ids as ProjectId  from tmp
GO

But the best solution for many to many relation it create new table for link.

Hi,

Thanks for that info. I’ve raised an issue for that and put it onto our backlog.

Rob