SQL: Stored procedure takes 20/25 seconds to run while the exact same select is almost instant running in a query window

Question

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.

You’ll want to share the actual execution plan (not the estimate) of an execution of the procedure with https://www.brentozar.com/pastetheplan/ . What was the bottleneck you believed you fixed before? If it still exists in the actual execution plan then it hasn’t been fixed — Andrew Sayer, Mar 25 '22 at 08:41
You can try to move all the conditions that are in the WHERE clause to the ON clauses of the inner joins. Plus, as suggested the group by has no aggregation functions... Just wondering, when you run your code in a select separately, does it return the full query or just a limited subset? — CarlosSR, Mar 25 '22 at 09:19
@DiegoPerez please share the actual plan (see the link), it will be much simpler to provide concrete advise that will give you real results. — Andrew Sayer, Mar 25 '22 at 10:00
Don't do CREATE INDEX in your SP itself. You are correct that SQL Server will try to create it every time you use the SP. You think it's slow now? — O. Jones, Mar 27 '22 at 21:01

score 0 · Accepted Answer · answered Mar 25 '22 at 23:59

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.

Thanks for such a detailed answer and for your time @Andrew Sayer, I appreciate it very much. I'll read it more carefully later, and I'll let you know how it ended, because I suspect not all joins are necessary, but need to check that with my team. — Diego Perez, Mar 28 '22 at 07:18

SQL: Stored procedure takes 20/25 seconds to run while the exact same select is almost instant running in a query window

1 Answers1