Dynamic column - how to improve performance

Question

OK, second attempt at the question (first is How to build virtual columns?)

Apologies in advance if this kind of question isn't suitable fo StackOverflow. Feel free to take it down if needed.

The basic question is "what's the best way to have a column whose content is built dynamically".

The code revolves around four tables.

First three (equipment, accessory, association) can be seen as two colums each, an ID and a name.

The goal is to replace the association name with a name built dynamically based on the name of the association components.

The fourth table describes the associations. The association should be seen as a tree, and each "branch" of the tree is represented as a line in this table. Columns are:

branchID (primary key)
association ID (int)
parent node kind (association = 1, equipement = 2, accessory = 3) (int)
parent node ID (ID in one of the three other tables) (int)
kid node kind
kid node ID

I do have something that works, using a view and a function (the function code follows). However, performance isn't satisfactory.

I see three improvement path:

minor adjustments through primary keys and indexes (code is significantly faster if there is NO primary key on the 4th table - I haven't been able to explain that)
fully reviewing the design behind the 4th table (I'm open to ideas)
replacing the custom function below by... something else! But what could that be?

Sorry for the French names... I chose not to edit the code before posting, assuming that copy/paste errors are worse than translation

Type = kind
Enfant = kid
Jumelage = association
Numero = name (oops...)
liens = branches

Thanks.

USE [testDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[testjmrFN] 
(
    @JumelageID int
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @Result varchar(max)

    DECLARE @TypeParent int
    DECLARE @ParentID int
    DECLARE @TypeEnfant int
    DECLARE @EnfantID int
    DECLARE @NumeroEquipement varchar(max)
    DECLARE @NumeroAccessoire varchar(max)

    SET @Result = ''

    DECLARE liens CURSOR LOCAL FOR 
        SELECT l.TypeParent, l.ParentID, l.TypeEnfant, l.EnfantID, e.Numero, a.Numero
        FROM ges_Jumelages_Liens l
        LEFT JOIN ges_Equipements e ON l.EnfantID = e.EquipementID
        LEFT JOIN ges_Accessoires a ON l.EnfantID = a.AccessoireID
        WHERE l.JumelageID = @JumelageID
        ORDER BY LienID

    OPEN liens

    FETCH NEXT FROM liens INTO @TypeParent, @ParentID, @TypeEnfant, @EnfantID, @NumeroEquipement, @NumeroAccessoire
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @TypeParent = 1 AND @TypeEnfant = 2
        BEGIN
            IF @Result <> ''
            BEGIN
                SET @Result = @Result + '§'
            END
            SET @Result = @Result + IsNull(@NumeroEquipement,'')
        END

        IF @TypeParent = 2 AND @TypeEnfant = 3
        BEGIN
            IF @Result <> ''
            BEGIN
                SET @Result = @Result + '~'
            END
            SET @Result = @Result + IsNull(@NumeroAccessoire,'')
        END

        FETCH NEXT FROM liens INTO @TypeParent, @ParentID, @TypeEnfant, @EnfantID, @NumeroEquipement, @NumeroAccessoire
    END

    CLOSE liens
    DEALLOCATE liens

    RETURN @Result

END

http://msdn.microsoft.com/en-us/library/bb677290%28v=sql.100%29.aspx — Anon, Jul 30 '13 at 21:11
Are you trying to concatonate the values into a long string, also? What is your desired output? — tommy_o, Jul 30 '13 at 21:58

score 1 · Accepted Answer · edited May 23 '17 at 10:25

This gives you the list that you need. If you want to concatonate the values into a long string with delimiters based on the cte's Delimiter field, see here: Concatenate many rows into a single text string?

use master;
go
with cte (TypeParent,ParentID,TypeEnfant,EnfantID,Numero,Delimiter)
as 
(   select      l.TypeParent
                , l.ParentID
                , l.TypeEnfant
                , l.EnfantID
                , e.Numero
                , '§'           as Delimiter
    from        dbo.ges_Jumelages_Liens as l
    join        dbo.ges_Equipements as e 
    on          l.EnfantID = e.EquipmentID
    where       l.TypeParent = 1
    and         l.TypeEnfant = 2
    union all
    select      l.TypeParent
                , l.ParentID
                , l.TypeEnfant
                , l.EnfantID
                , a.Numero
                ,'~'            as Delimiter
    from        dbo.ges_Jumelages_Liens as l
    join        dbo.ges_Accessoires as a 
    on          l.EnfantID = a.EquipmentID 
    where       l.TypeParent = 2
    and         l.TypeEnfant = 3 
)
select          *
from            cte

If you need further help, please clarify your question.

Thank you for taking the time. I actually fixed the slowness by disabling "Use actual execution plan" option in MgmtConsole (I was still debugging it) - but I only found this after implementing your approach. — jmr, Jul 31 '13 at 19:20
Another excellent read on how to combine multiple rows into a single one: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ — jmr, Jul 31 '13 at 19:23

Dynamic column - how to improve performance

1 Answers1