Server优化查询

Question

我需要使用现有的SQL Server存储过程，但我认为它根本没有优化，因为没有where子句，它需要45秒才能完成，而不是大量的数据。

我知道要对任何试图帮助我的人进行测试是非常困难的，但至少给我一个改进的想法或起点。

下一个查询是：

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 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)

当然，因为NOT IN (从@RETURN_TABLE选择PK_IDS )为每一行做了一个选择，但是我没有找到避免该行的方法，因为注释会在结果临时表中创建重复的记录。

这只是对231市场的一个例子过滤，并且评论了我所说的“冒犯”行，所以您对返回的数据有一个最小的概念。

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

因此，正如您所看到的，有两个记录(AD_HOC 1和AD_HOC 0)使用相同的PK_IDS重复记录，但我不能肯定值为1的AD_HOC或值为0的记录可以跳过。

不知道你是否理解，但我希望任何人都能帮上忙。

尝试1：

在结果tmp表select中执行下一个操作可以在某种程度上解决我的问题，我移除“违犯”行(这使时间是可接受的)，然后在结果中只保留重复值中的第一个值，但是tsql在声明不正确的地方是基础"seqnum“。怎么解决这个问题？

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

Answer 1

对于seqnum问题，需要使用派生表或cte封装查询，例如

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