SQL Server - Optimize query

Question

I need to use an existing SQL Server stored procedure, but in my opinion it's not optimized at all, as with no where clause it takes 45 seconds to complete with not a huge amount of data.

I know it will very difficult to test it for anyone trying to help me, but at least give me an idea or start point to improve it.

The query is the next:

DECLARE @PFK_ENTERPRISE int = 7,
        @PFK_USER int = 14118,
        @ID_REGION varchar(200) = '',
        @FK_SITE int = 0,
        @FK_MARKET int = null;

DECLARE @RETURN_TABLE TABLE
                      (
                          MARKET varchar(200),
                          NAMES varchar(200),
                          PK_IDS VARCHAR (25),
                          PFK_MARKET INT,
                          IS_ADHOC BIT
                      )

INSERT INTO @RETURN_TABLE (NAMES, PK_IDS, PFK_MARKET, IS_ADHOC, MARKET )
    SELECT DISTINCT
        CASE 
            WHEN CLIENT_LEGAL.LEGAL_NAME = CLIENT_ORIGIN.LEGAL_NAME 
                THEN CLIENT_LEGAL.LEGAL_NAME 
            ELSE CLIENT_ORIGIN.LEGAL_NAME + ' (' + CLIENT_LEGAL.LEGAL_NAME + ')' 
        END AS 'NAMES',
        CONVERT(VARCHAR(10), MARKETS_CATALOGUES.PFK_CLIENT_LEGAL) + '/' + CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN) + '/'  + CONVERT(VARCHAR(10), MARKETS_CATALOGUES.PFK_COMPANY) AS 'PK_IDS',
        USER_ACCESS_CLIENTS.PFK_MARKET, 1 as "IS_ADHOC", 
        MARKET.NAME_DESCRIPTION
    FROM        
        USER_ACCESS_CLIENTS
    INNER JOIN 
        MARKETS_CATALOGUES ON USER_ACCESS_CLIENTS.PFK_ENTERPRISE = MARKETS_CATALOGUES.PFK_ENTERPRISE
                           AND USER_ACCESS_CLIENTS.PFK_MARKET = MARKETS_CATALOGUES.PFK_MARKET 
                           AND USER_ACCESS_CLIENTS.PFK_CLIENT_LEGAL = MARKETS_CATALOGUES.PFK_CLIENT_LEGAL 
                           AND USER_ACCESS_CLIENTS.PFK_CLIENT_ORIGIN = MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN  
                           AND USER_ACCESS_CLIENTS.PFK_COMPANY = MARKETS_CATALOGUES.PFK_COMPANY
    INNER JOIN 
        CLIENTS AS CLIENT_LEGAL ON MARKETS_CATALOGUES.PFK_ENTERPRISE = CLIENT_LEGAL.PFK_ENTERPRISE 
                                AND MARKETS_CATALOGUES.PFK_COMPANY = CLIENT_LEGAL.PFK_COMPANY 
                                AND MARKETS_CATALOGUES.PFK_CLIENT_LEGAL = CLIENT_LEGAL.PK_CLIENT

    INNER JOIN 
        CLIENTS AS CLIENT_ORIGIN ON MARKETS_CATALOGUES.PFK_ENTERPRISE = CLIENT_ORIGIN.PFK_ENTERPRISE 
                                 AND MARKETS_CATALOGUES.PFK_COMPANY = CLIENT_ORIGIN.PFK_COMPANY 
                                 AND MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN = CLIENT_ORIGIN.PK_CLIENT
    INNER JOIN
        MARKET ON MARKETS_CATALOGUES.PFK_ENTERPRISE = market.PFK_ENTERPRISE
               AND MARKETS_CATALOGUES.PFK_MARKET = market.PK_MARKET
    INNER JOIN 
        CATALOGUES cat ON cat.PFK_ENTERPRISE = MARKETS_CATALOGUES.PFK_ENTERPRISE 
                       AND cat.PK_CATALOGUE = MARKETS_CATALOGUES.PFK_CATALOGUE
                       AND market.FK_SITE = cat.FK_SITE
    WHERE       
        USER_ACCESS_CLIENTS.PFK_ENTERPRISE = @PFK_ENTERPRISE
        AND USER_ACCESS_CLIENTS.PFK_USER = @PFK_USER 
        AND MARKETS_CATALOGUES.FK_CATALOGUE_SETUP > 0 
        AND MARKETS_CATALOGUES.IS_ACTIVE = 1
        AND (@FK_MARKET IS NULL OR @FK_MARKET = USER_ACCESS_CLIENTS.PFK_MARKET)
        AND FK_TYPE_CATALOGUE = 6

INSERT INTO @RETURN_TABLE (NAMES, PK_IDS, PFK_MARKET, IS_ADHOC, MARKET)
    SELECT DISTINCT
        CASE WHEN CLIENT_LEGAL.LEGAL_NAME = CLIENT_ORIGIN.LEGAL_NAME THEN CLIENT_LEGAL.LEGAL_NAME ELSE CLIENT_ORIGIN.LEGAL_NAME + ' (' + CLIENT_LEGAL.LEGAL_NAME + ')' END AS 'NAMES',
        CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_CLIENT_LEGAL) + '/' + CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN) + '/'  + CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_COMPANY) AS 'PK_IDS',
        USER_ACCESS_CLIENTS.PFK_MARKET, 0 as "IS_ADHOC", 
        MARKET.NAME_DESCRIPTION AS MARKET

FROM        USER_ACCESS_CLIENTS
            inner JOIN MARKETS_CATALOGUES
                        ON USER_ACCESS_CLIENTS.PFK_ENTERPRISE = MARKETS_CATALOGUES.PFK_ENTERPRISE AND
                        USER_ACCESS_CLIENTS.PFK_MARKET = MARKETS_CATALOGUES.PFK_MARKET AND
                        USER_ACCESS_CLIENTS.PFK_CLIENT_LEGAL = MARKETS_CATALOGUES.PFK_CLIENT_LEGAL AND
                        USER_ACCESS_CLIENTS.PFK_CLIENT_ORIGIN = MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN AND
                        USER_ACCESS_CLIENTS.PFK_COMPANY = MARKETS_CATALOGUES.PFK_COMPANY
            INNER JOIN CLIENTS AS CLIENT_LEGAL 
                        ON MARKETS_CATALOGUES.PFK_ENTERPRISE = CLIENT_LEGAL.PFK_ENTERPRISE AND
                        MARKETS_CATALOGUES.PFK_COMPANY = CLIENT_LEGAL.PFK_COMPANY AND
                        MARKETS_CATALOGUES.PFK_CLIENT_LEGAL = CLIENT_LEGAL.PK_CLIENT

            INNER JOIN CLIENTS AS CLIENT_ORIGIN ON  MARKETS_CATALOGUES.PFK_ENTERPRISE = CLIENT_ORIGIN.PFK_ENTERPRISE AND

                                            MARKETS_CATALOGUES.PFK_COMPANY = CLIENT_ORIGIN.PFK_COMPANY AND

                                            MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN = CLIENT_ORIGIN.PK_CLIENT
            
            inner join MARKET on MARKETS_CATALOGUES.PFK_ENTERPRISE = market.PFK_ENTERPRISE
                                        and MARKETS_CATALOGUES.PFK_MARKET = market.PK_MARKET

            INNER JOIN CATALOGUES cat on cat.PFK_ENTERPRISE = MARKETS_CATALOGUES.PFK_ENTERPRISE and cat.PK_CATALOGUE = MARKETS_CATALOGUES.PFK_CATALOGUE
            and market.FK_SITE = cat.FK_SITE

WHERE       USER_ACCESS_CLIENTS.PFK_ENTERPRISE = @PFK_ENTERPRISE
            AND USER_ACCESS_CLIENTS.PFK_USER = @PFK_USER 
            AND MARKETS_CATALOGUES.FK_CATALOGUE_SETUP > 0 
            AND MARKETS_CATALOGUES.IS_ACTIVE = 1
            AND (@FK_MARKET IS NULL OR @FK_MARKET = USER_ACCESS_CLIENTS.PFK_MARKET)
            AND FK_TYPE_CATALOGUE <> 6
            and CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_CLIENT_LEGAL) + '/' + CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN) + '/'  + CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_COMPANY) NOT IN (select PK_IDS from @RETURN_TABLE)

            select *
            from @RETURN_TABLE
            group by PK_IDS
            order by MARKET

and the line slowing down significantly the query is:

and CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_CLIENT_LEGAL) + '/' + CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_CLIENT_ORIGIN) + '/'  + CONVERT(VARCHAR(10),MARKETS_CATALOGUES.PFK_COMPANY) NOT IN (select PK_IDS from @RETURN_TABLE)

of course because of the NOT IN (select PK_IDS from @RETURN_TABLE) which does a select for each row, but I'm not finding a way to avoid that line, because commenting it creates duplicated records in the result temp table.

This is just an example filtering for market 231 and having commented the "offending" line i was talking, so you have a minimal idea of data returned.

adm Promotions  ADM Promotions UK Limited                           75/75/39    231 1
adm Promotions  Branding Your POS Mexico, S.A. de C.V. / BYP (MX )  107/107/39  231 1
adm Promotions  Imagen Publicitaria Punto de Venta SA de CV (MX )   111/111/39  231 1
adm Promotions  ADM Promotions UK Limited                           75/75/39    231 0
adm Promotions  Branding Your POS Mexico, S.A. de C.V. / BYP (MX )  107/107/39  231 0
adm Promotions  Imagen Publicitaria Punto de Venta SA de CV (MX )   111/111/39  231 0

So, as you see, there are two records (AD_HOC 1 and AD_HOC 0) repeated records with the same PK_IDS, but I cannot affirm the AD_HOC with value 1 or the one with value 0 can be skipped.

Not sure if you understand, but I hope anyone can help.

Attempt 1:

Doing the next in the result tmp table select could resolve in a way my issue, I remove the "offending" line (which makes an acceptable timing) and then in the result I keep only the first value from repeated ones, but tsql is underlying "seqnum" in the where stating that is incorrect. How to fix that?

SELECT 
    *, 
    ROW_NUMBER() OVER (PARTITION BY PK_IDS ORDER BY market) seqnum
FROM  
    @RETURN_TABLE 
WHERE
    seqnum = 1