我需要使用現有的 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 (select PK_IDS from @RETURN_TABLE)對每一行進行選擇，但我沒有找到避免該行的方法，因為注釋它會在結果臨時表中創建重復的記錄。
這只是市場 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
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
uj5u.com熱心網友回復：
對于您的seqnum問題，您需要使用派生表或cte來封裝查詢，例如
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY PK_IDS ORDER BY market) seqnum
FROM @RETURN_TABLE
)t
WHERE seqnum = 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422050.html
標籤：
上一篇：如何在子查詢中參考系統版本表
下一篇：加入所有記錄甚至不匹配