Suggest to change TenantProject view

Legacy Performance
1

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
2

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

3

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.

4

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.

5

Hi,

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

Rob