I have already read SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes but none of the suggestions posted there seems to work in my case. I have the next 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
And it takes more than 20 seconds to execute, but the weird thing is that if I just run the same exact select in a new query window the results are almost instant.
(The original stored is a bit longer, as it has three select, but one is enough to reproduce the issue).
I tried using tmp variables instead of the parameters directly, I've also tried
WITH RECOMPILE AS SET ARITHABORT ON;
to no avail and at first tried CTRL-L to check the execution plan that showed a bottleneck I've already fixed, but now I cannot try execution plan anymore as I'm storing a select into a #tmp variable.
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.
CodePudding user response：
Thanks for updating.
You can always try the index suggestions - it should be very simple to benchmark if it helps or not. My immediate thought is that neither will help, but practising how to benchmark in a controlled manner would be beneficial. Reading this question, we can tell that you've already tried numerous different things, and it's not really clear which changes helped the procedure behave more like the standalone, which changes improved both the stored procedure and the standalone and which made no difference but were kept anyway.
You haven't provided the actual execution plan that you want this to behave like, but let's see how much we can improve it.
Your time is mainly going towards the nested loops of the index seek of USER_ACCESS_MARKETS. This takes so long because it runs 539K times when it thought it would only execute 55 times. This bad estimate may have started with the join between ORDERS_WINDOW_CATALOGUES and CATALOGUES, it believes this will result in 3 rows but really returns 119 rows (this difference snowballs as joins are added). There's quite a few filters at play for this join, two are in the main join conditions that look to be the clustered index (which should be okay), then there's the c.IS_HISTORIC <> 1 and c.FK_SITE = @FK_SITE_2 filters, the latter two are probably what's responsible for the low cardinality. My guess would be c.FK_SITE = @FK_SITE_2 is the most important here, I would suggest you look at what this join looks like in your standalone version - where sniffing the FK_SITE_2 variable will be different.
To address this cardinality, you could create a temp table for CATALOGUES, like what you've already done with markets_catalogues. This would work if there is an efficient way of finding the rows that match these other filters. This will give SQL Server a chance to get estimates after populating this temp table - correcting it's estimation of the FK_SITE_2 filter.
SELECT * INTO #CATALOGUES_tmp FROM CATALOGUES WHERE FK_SITE = @FK_SITE_2 and IS_HISTORIC <> 1
Instead of addressing the cardinality estimates (there look to be plenty of sources of issues here and you will need to address plenty), you could just target the nested loops of USER_ACCESS_MARKETS by using a temp table so that it has to be hash joined:
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)
Or hint the join option - tell SQL Server it should just hash join to USER_ACCESS_MARKETS
inner hash join USER_ACCESS_MARKETS uam on
m.PFK_ENTERPRISE = uam.PFK_ENTERPRISE AND m.PK_MARKET = uam.PFK_MARKET
Those temp tables might cause other cardinality misestimates to start influencing the plan more so you'll need to take stock with the actual execution plan with each modification - see where the time is now going, see where the estimates are coming from.
Further optimization could come from noticing that it spends about a second aggregating 540K rows back down to 119 - this suggests that changing the joins to semi-joins or forcing a distinct earlier to reduce the amount of unnecessary results coming through could be possible. If you could do this so that the slow nested loop join to USER_ACCESS_MARKETS is only done a fraction of the time then that would also see huge benefits (and could still be a nested loop).
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.