我已經閱讀了SP 需要 15 分鐘，但是執行相同的查詢會在 1-2 分鐘內回傳結果，但那里發布的建議似乎都不適用于我的情況。我有下一個 SQL SP：
ALTER PROCEDURE [dbo].[BL_GET_OW_AND_CATALOGUES_BY_SITE_FOR_ACTUAL_POSITION]
@PFK_ENTERPRISE int,
@FK_SITE int,
@PK_USER int
WITH RECOMPILE
AS
SET ARITHABORT ON;
DECLARE @PFK_ENTERPRISE_2 int = @PFK_ENTERPRISE
DECLARE @FK_SITE_2 int = @FK_SITE
DECLARE @PK_USER_2 int = @PK_USER
SELECT * INTO #markets_catalogues_tmp1 FROM markets_catalogues WHERE pfk_enterprise = @PFK_ENTERPRISE_2 and is_Active = 1 and FK_CATALOGUE_SETUP > 0
SELECT ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
FROM ORDERS_WINDOW ow
inner join ORDERS_WINDOW_CATALOGUES owc on ow.PFK_ENTERPRISE = owc.PFK_ENTERPRISE
and ow.PK_ORDER_WINDOW = owc.PFK_ORDER_WINDOW
inner join CATALOGUES c on owc.PFK_ENTERPRISE = c.PFK_ENTERPRISE
and owc.PFK_CATALOGUE = c.PK_CATALOGUE
inner join #markets_catalogues_tmp1 mc on
owc.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and owc.PFK_CATALOGUE = mc.PFK_CATALOGUE
inner join MARKET m on
m.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and m.PK_MARKET = mc.PFK_MARKET
inner join USER_ACCESS_MARKETS uam on
m.PFK_ENTERPRISE = uam.PFK_ENTERPRISE AND m.PK_MARKET = uam.PFK_MARKET
inner join USERS u ON
uam.PFK_ENTERPRISE = u.PFK_ENTERPRISE AND uam.PFK_USER = u.PK_USER
WHERE (ow.PFK_ENTERPRISE = @PFK_ENTERPRISE_2) AND (ow.FK_ORDER_WINDOW_STATUS IN (1,2,3,5,6))
AND (IS_MAIN_CATALOG_RELATED = 1 OR FK_CATALOG_RELATED = 0)
AND (uam.PFK_USER = @PK_USER_2 OR @PK_USER_2 IS NULL)
AND c.FK_SITE = @FK_SITE_2
AND (c.IS_HISTORIC <> 1)
group by ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
ORDER BY ow.OW_DESCRIPTION ASC
執行需要 20 多秒，但奇怪的是，如果我只是在新的查詢視窗中運行相同的精確選擇，結果幾乎是即時的。
（原來存盤的有點長，因為它有三個選擇，但一個足以重現問題）。
我嘗試使用 tmp 變數而不是直接使用引數，我也嘗試過
WITH RECOMPILE AS SET ARITHABORT ON;
無濟于事，起初嘗試使用 CTRL-L 檢查顯示我已經修復的瓶頸的執行計劃，但現在我無法再嘗試執行計劃，因為我將選擇存盤到 #tmp 變數中。
I need to find out why this query takes so much longer executing the SP with exec than running the select itself separately, in other words, optimize this query.
Edit 1:
If I launch the next query in a separately window it works instantly and it returns the exact same result as running it from the SP:
SELECT * INTO #markets_catalogues_tmp1 FROM markets_catalogues WHERE pfk_enterprise = 41 and is_Active = 1 and FK_CATALOGUE_SETUP > 0
SELECT ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
FROM ORDERS_WINDOW ow
inner join ORDERS_WINDOW_CATALOGUES owc on ow.PFK_ENTERPRISE = owc.PFK_ENTERPRISE
and ow.PK_ORDER_WINDOW = owc.PFK_ORDER_WINDOW
inner join CATALOGUES c on owc.PFK_ENTERPRISE = c.PFK_ENTERPRISE
and owc.PFK_CATALOGUE = c.PK_CATALOGUE
inner join #markets_catalogues_tmp1 mc on
owc.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and owc.PFK_CATALOGUE = mc.PFK_CATALOGUE
inner join MARKET m on
m.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and m.PK_MARKET = mc.PFK_MARKET
inner join USER_ACCESS_MARKETS uam on
m.PFK_ENTERPRISE = uam.PFK_ENTERPRISE AND m.PK_MARKET = uam.PFK_MARKET
inner join USERS u ON
uam.PFK_ENTERPRISE = u.PFK_ENTERPRISE AND uam.PFK_USER = u.PK_USER
WHERE (ow.PFK_ENTERPRISE = 41) AND (ow.FK_ORDER_WINDOW_STATUS IN (1,2,3,5,6))
--and MARKETS_CATALOGUES.IS_ACTIVE = 1
--and MARKETS_CATALOGUES.FK_CATALOGUE_SETUP > 0
AND (IS_MAIN_CATALOG_RELATED = 1 OR FK_CATALOG_RELATED = 0)
AND (uam.PFK_USER = 14118 OR 14118 IS NULL)
AND c.FK_SITE = 1
AND (c.IS_HISTORIC <> 1)
group by ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
ORDER BY ow.OW_DESCRIPTION ASC
Edit 2:
I've moved all elements in where clause to the corresponding ONs in joins - as suggested by @CarlosSR - so the query now looks like this:
SELECT ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
FROM ORDERS_WINDOW ow
inner join ORDERS_WINDOW_CATALOGUES owc on ow.PFK_ENTERPRISE = owc.PFK_ENTERPRISE
and ow.PFK_ENTERPRISE = @PFK_ENTERPRISE_2
and ow.FK_ORDER_WINDOW_STATUS IN (1,2,3,5,6)
and ow.PK_ORDER_WINDOW = owc.PFK_ORDER_WINDOW
inner join CATALOGUES c on owc.PFK_ENTERPRISE = c.PFK_ENTERPRISE
and owc.PFK_CATALOGUE = c.PK_CATALOGUE
and c.FK_SITE = @FK_SITE_2
and (c.IS_MAIN_CATALOG_RELATED = 1 or c.FK_CATALOG_RELATED = 0)
and c.IS_HISTORIC <> 1
inner join #markets_catalogues_tmp1 mc on
owc.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and owc.PFK_CATALOGUE = mc.PFK_CATALOGUE
inner join MARKET m on
m.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and m.PK_MARKET = mc.PFK_MARKET
inner join USER_ACCESS_MARKETS uam on
m.PFK_ENTERPRISE = uam.PFK_ENTERPRISE and m.PK_MARKET = uam.PFK_MARKET
and (uam.PFK_USER = @PK_USER_2 OR @PK_USER_2 IS NULL)
inner join USERS u ON
uam.PFK_ENTERPRISE = u.PFK_ENTERPRISE AND uam.PFK_USER = u.PK_USER
group by ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
ORDER BY ow.OW_DESCRIPTION ASC
But the results are exactly the same, 20 sec to run the query.
Edit 3: Answering @Andrew Sayer
The execution plan was -at first- showing a bottleneck in a join with an inner select
inner join (SELECT * FROM markets_catalogues.....
(the query was looking like the next).
SELECT ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
FROM ORDERS_WINDOW ow
inner join ORDERS_WINDOW_CATALOGUES owc on ow.PFK_ENTERPRISE = owc.PFK_ENTERPRISE
and ow.PK_ORDER_WINDOW = owc.PFK_ORDER_WINDOW
inner join CATALOGUES c on owc.PFK_ENTERPRISE = c.PFK_ENTERPRISE
and owc.PFK_CATALOGUE = c.PK_CATALOGUE
inner join (SELECT * FROM markets_catalogues WHERE pfk_enterprise = @PFK_ENTERPRISE_2 and is_Active = 1 and FK_CATALOGUE_SETUP > 0) mc on
owc.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and owc.PFK_CATALOGUE = mc.PFK_CATALOGUE
inner join MARKET m on
m.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and m.PK_MARKET = mc.PFK_MARKET
inner join USER_ACCESS_MARKETS uam on
m.PFK_ENTERPRISE = uam.PFK_ENTERPRISE AND m.PK_MARKET = uam.PFK_MARKET
inner join USERS u ON
uam.PFK_ENTERPRISE = u.PFK_ENTERPRISE AND uam.PFK_USER = u.PK_USER
WHERE (ow.PFK_ENTERPRISE = @PFK_ENTERPRISE_2) AND (ow.FK_ORDER_WINDOW_STATUS IN (1,2,3,5,6))
AND (IS_MAIN_CATALOG_RELATED = 1 OR FK_CATALOG_RELATED = 0)
AND (uam.PFK_USER = @PK_USER_2 OR @PK_USER_2 IS NULL)
AND c.FK_SITE = @FK_SITE_2
AND (c.IS_HISTORIC <> 1)
group by ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION
ORDER BY ow.OW_DESCRIPTION ASC
And what I've done was to move the SELECT in the join to a separate #tmp variable and indeed now its running faster (because this same select was being used in one select more I've ommited for simplicity). At first the whole query with three selects was 1:20 min and now the full query is 40 sec, but it is still very slow comparing with the instant results when running the selects in a query window.
Edit 4: The estimated execution plan.
https://www.brentozar.com/pastetheplan/?id=HJGXXfsG9
Edit 5: The actual execution plan.
https://www.brentozar.com/pastetheplan/?id=SkF2Nzszc
Edit 6: The actual execution plan is suggesting me to create an index for the #tmp table. I could create the index like this:
SELECT * INTO #markets_catalogues_tmp1 FROM markets_catalogues WHERE pfk_enterprise = @PFK_ENTERPRISE_2 and is_Active = 1 and FK_CATALOGUE_SETUP > 0
CREATE NONCLUSTERED INDEX markets_catalogues_tmp1 ON [dbo].[#markets_catalogues_tmp1] ([pfk_enterprise]) INCLUDE ([is_active], [FK_CATALOGUE_SETUP])
But as I'm not very experienced with this, I'm not sure if the index should be included in the SP itself or outside, because if I put it inside it will create index every time the SP is launched? But outside cannot be, because it is a tmp table, so I'm a bit confused.
uj5u.com熱心網友回復：
感謝更新。
您可以隨時嘗試索引建議 - 如果有幫助，基準測驗應該非常簡單。我的直接想法是兩者都無濟于事，但練習如何以受控方式進行基準測驗將是有益的。閱讀這個問題，我們可以看出您已經嘗試了許多不同的事情，并且不清楚哪些更改有助于該程序更像獨立程式，哪些更改改進了存盤程序和獨立程式，并且沒有任何區別，但是無論如何保留。
您還沒有提供您希望它表現得像的實際執行計劃，但讓我們看看我們可以改進多少。
您的時間主要用于索引查找的嵌套回圈USER_ACCESS_MARKETS。這需要很長時間，因為它運行了 539K 次，而它認為它只會執行 55 次。這個錯誤的估計可能是從ORDERS_WINDOW_CATALOGUESand之間的連接開始的CATALOGUES，它認為這將導致 3 行但實際上回傳 119 行（添加連接時這種差異會滾雪球）。這個連接有很多過濾器，兩個在主要連接條件中，看起來是聚集索引（應該沒問題），然后是c.IS_HISTORIC <> 1andc.FK_SITE = @FK_SITE_2過濾器，后兩個可能是低基數的原因。我的猜測c.FK_SITE = @FK_SITE_2是這里最重要，我建議你看看這個連接在你的獨立版本中是什么樣子的——在哪里嗅探FK_SITE_2變數會有所不同。
為了解決這個基數，您可以為 創建一個臨時表CATALOGUES，就像您已經完成的那樣markets_catalogues。如果有一種有效的方法可以找到與這些其他過濾器匹配的行，這將起作用。這將使 SQL Server 有機會在填充此臨時表后獲得估計值 - 更正它對FK_SITE_2過濾器的估計值。
SELECT * INTO #CATALOGUES_tmp FROM CATALOGUES WHERE FK_SITE = @FK_SITE_2 and IS_HISTORIC <> 1
而不是解決基數估計（這里看起來有很多問題來源，您需要解決很多問題），您可以USER_ACCESS_MARKETS通過使用臨時表來定位嵌套回圈，以便它必須進行哈希連接：
SELECT * INTO #USER_ACCESS_MARKETS_tmp FROM USER_ACCESS_MARKETS WHERE PFK_ENTERPRISE = @PFK_ENTERPRISE_2 and (PFK_USER = @PK_USER_2 OR @PK_USER_2 IS NULL)
或者提示加入選項 - 告訴 SQL Server 它應該只是散列加入USER_ACCESS_MARKETS
inner hash join USER_ACCESS_MARKETS uam on
m.PFK_ENTERPRISE = uam.PFK_ENTERPRISE AND m.PK_MARKET = uam.PFK_MARKET
這些臨時表可能會導致其他基數錯誤估計開始更多地影響計劃，因此您需要對每次修改的實際執行計劃進行評估——看看現在時間在哪里，看看估計來自哪里。
進一步的優化可能來自注意到它花費了大約一秒鐘的時間將 540K 行聚合回 119 - 這表明將連接更改為半連接或強制提前執行不同的操作以減少通過的不必要結果的數量是可能的。如果您可以這樣做，以便慢速嵌套回圈連接USER_ACCESS_MARKETS只完成一小部分時間，那么這也會帶來巨大的好處（并且仍然可能是嵌套回圈）。
I've subqueried up part of the statement and placed a distinct in just before the join to USER_ACCESS_MARKETS, it's hard to predict how much impact this could have as I don't know anything about the PFK_MARKET column in market_catalogues (this column is the only additional column you care about from this join) but there's potential
ALTER PROCEDURE [dbo].[BL_GET_OW_AND_CATALOGUES_BY_SITE_FOR_ACTUAL_POSITION]
@PFK_ENTERPRISE int,
@FK_SITE int,
@PK_USER int
WITH RECOMPILE
AS
SET ARITHABORT ON;
DECLARE @PFK_ENTERPRISE_2 int = @PFK_ENTERPRISE
DECLARE @FK_SITE_2 int = @FK_SITE
DECLARE @PK_USER_2 int = @PK_USER
SELECT sq.PK_ORDER_WINDOW, sq.OW_DESCRIPTION
FROM (
SELECT DISTINCT ow.PK_ORDER_WINDOW, ow.OW_DESCRIPTION, m.PFK_ENTERPRISE, m.PK_MARKET
FROM ORDERS_WINDOW ow
inner join ORDERS_WINDOW_CATALOGUES owc on ow.PFK_ENTERPRISE = owc.PFK_ENTERPRISE
and ow.PK_ORDER_WINDOW = owc.PFK_ORDER_WINDOW
inner join CATALOGUES c on owc.PFK_ENTERPRISE = c.PFK_ENTERPRISE
and owc.PFK_CATALOGUE = c.PK_CATALOGUE
inner join (SELECT * FROM markets_catalogues WHERE pfk_enterprise = @PFK_ENTERPRISE_2 and is_Active = 1 and FK_CATALOGUE_SETUP > 0) mc on
owc.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and owc.PFK_CATALOGUE = mc.PFK_CATALOGUE
inner join MARKET m on
m.PFK_ENTERPRISE = mc.PFK_ENTERPRISE and m.PK_MARKET = mc.PFK_MARKET
WHERE (ow.PFK_ENTERPRISE = @PFK_ENTERPRISE_2) AND (ow.FK_ORDER_WINDOW_STATUS IN (1,2,3,5,6))
AND (c.IS_MAIN_CATALOG_RELATED = 1 OR c.FK_CATALOG_RELATED = 0)
AND c.FK_SITE = @FK_SITE_2
AND (c.IS_HISTORIC <> 1)
) sq
inner join USER_ACCESS_MARKETS uam on
sq.PFK_ENTERPRISE = uam.PFK_ENTERPRISE AND sq.PK_MARKET = uam.PFK_MARKET
AND (uam.PFK_USER = @PK_USER_2 OR @PK_USER_2 IS NULL)
inner join USERS u ON
uam.PFK_ENTERPRISE = u.PFK_ENTERPRISE AND uam.PFK_USER = u.PK_USER
group by sq.PK_ORDER_WINDOW, sq.OW_DESCRIPTION
ORDER BY sq.OW_DESCRIPTION ASC
I've also gone back to the version of the query without the temp table for markets_catalogues, you should try with and without putting the data in the temp table first.
I wonder if any of these plans look like what you are getting with the standalone statement.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451046.html
標籤：sql sql-server stored-procedures query-optimization
上一篇：解決“Latin1_General_CI_AS”和“SQL_Latin1_General_CP1_CI_AS”之間的排序規則沖突