Query slow with two (almost the same) join but faster with either one

Question

I have a query now running very slow now. This query has a combined query of our stock position (I call it POSITION_QUERY, there is one row for one stock code trading in one exchange at one given date), then join (I call it the FIRST JOIN) the stock price table to get price, the join condition is then on three columns: stock code, exchange, and trade date. Then I need a SECOND JOIN, because each stock belongs to a composite index (in the POSITION_QUERY, each row has columns indicating the index code and the exchange where the index is traded).

So my query looks liks this:

SELECT * FROM 
POSITION_QUERY t1 
 JOIN DAILY_PRICE t2
    on t1.STOCK_CODE = t2.STOCK_CODE
       and t1.STOCK_EXCHANGE = t2.EXCHANGE
       and t2.TRADE_DATE = 20181121
 JOIN DAILY_PRICE t3
    on t1.INDEX_CODE = t3.STOCK_CODE
       and t1.INDEX_EXCHANGE = t3.EXCHANGE
       and t3.TRADE_DATE = 20181121

And now the query is really really slow: about 3 minutes to return 50 rows of result. As I mentioned the POSITION_QUERY is actually a query not an existing table. But if I run SELECT * FROM POSITION_QUERY it is fast anyway (I only get the position on 20181121 inside the POSITION_QUERY so the amount of this query is already 50 as I mentioned before). DAILY_PRICE is a view but almost mapped to one existing table and I have indexes on the joined columns of this table.

The strange thing to me is, if I only execute POSITION_QUERY, OR POSITION_QUERY with FIRST JOIN (that is, join DAILY_PRICE with the first set of condition), OR POSITION_QUERY with SECOND JOIN (join DAILY_PRICE with the second set of condition), ALL THREE queries run really fast (less than one second).

I have checked the actual execution plans, the plan with two joins, and plans with one join are quite similar, but in the two-joins plan, there is a table spool (lazy spool) whose cost is 49%. The output list of the table spool operator is the POSOTION_QUERY so I am guessting it is storing the 'POSITION_QUERY' result (but why it is not a consecutive join?). I am bad at interpretting execution plans so I don't know whether that's the problem and how I can fix it.

Update: I've pasted my execution plan, with the real data table structure and query. The link is: Execution plan

Answer 1

What are the data types? After generating 520,000 rows of sample data with implicit data types, it only takes 3 seconds to run your query:

CREATE TABLE POSITION_QUERY (STOCK_CODE INT, STOCK_EXCHANGE INT, INDEX_CODE INT, INDEX_EXCHANGE INT, TRADE_DATE INT)
CREATE TABLE DAILY_PRICE (STOCK_CODE INT, EXCHANGE INT, TRADE_DATE INT)

-- Put 520,000 rows of sample data in POSITION_QUERY.
;WITH CTE AS (
    SELECT 1 AS A
    UNION ALL
    SELECT A + 1
    FROM CTE
    WHERE A < 10
),
CTE_DATE AS (
    SELECT CAST(GETDATE() AS DATE) AS D
    UNION ALL
    SELECT DATEADD(DAY, -1, D)
    FROM CTE_DATE
    WHERE D > '10/1/2018'
)
INSERT INTO POSITION_QUERY
SELECT C1.A, C2.A, C3.A, C4.A, FORMAT(C5.D, 'yyyyMMdd')
FROM CTE C1, CTE C2, CTE C3, CTE C4, CTE_DATE C5
OPTION (MAXRECURSION 0)

-- Put 5,200 rows of sample data in DAILY_PRICE that match all POSITION_QUERY records
;WITH CTE AS (
    SELECT 1 AS A
    UNION ALL
    SELECT A + 1
    FROM CTE
    WHERE A < 10
),
CTE_DATE AS (
    SELECT CAST(GETDATE() AS DATE) AS D
    UNION ALL
    SELECT DATEADD(DAY, -1, D)
    FROM CTE_DATE
    WHERE D > '10/1/2018'
)
INSERT INTO DAILY_PRICE 
SELECT C1.A, C2.A, FORMAT(C3.D, 'yyyyMMdd')
FROM CTE C1, CTE C2, CTE_DATE C3
OPTION (MAXRECURSION 0)

-- Create nonclustered indexes on both tables' pertinent columns.
CREATE NONCLUSTERED INDEX IDX_POSITION_QUERY
ON [dbo].[POSITION_QUERY] ([STOCK_CODE],[STOCK_EXCHANGE])
INCLUDE ([INDEX_CODE],[INDEX_EXCHANGE],[TRADE_DATE])
GO

CREATE NONCLUSTERED INDEX IDX_DAILY_PRICE
ON DAILY_PRICE (STOCK_CODE, EXCHANGE, TRADE_DATE)
GO

-- Finally, run the query. It takes 3 seconds to return 520k records.
SELECT * FROM 
POSITION_QUERY t1 
 JOIN DAILY_PRICE t2
    on t1.STOCK_CODE = t2.STOCK_CODE
       and t1.STOCK_EXCHANGE = t2.EXCHANGE
       and t2.TRADE_DATE = 20181121
 JOIN DAILY_PRICE t3
    on t1.INDEX_CODE = t3.STOCK_CODE
       and t1.INDEX_EXCHANGE = t3.EXCHANGE
       and t3.TRADE_DATE = 20181121

And here's the execution plan:

https://www.brentozar.com/pastetheplan/?id=BkSgin7C7

Can you paste your execution plan? There's probably a bad type conversion somewhere. Even without the indexes I created, it only takes 14 seconds.

Answer 2

Without being able to test it myself, I can offer a strategy that I like to adopt which often leads to faster query results. That is, store what you can in temporary tables and index them up precisely to meet your master query's needs. In this case, it looks like you can split out the data you need from DAILY_PRICE and then index that up on STOCK_CODE and EXCHANGE, like so:

DROP TABLE IF EXISTS #temp;
SELECT *
INTO #temp
FROM DAILY_PRICE
WHERE TRADE_DATE = 20181121;
CREATE INDEX [IX1] ON #temp(STOCK_CODE, EXCHANGE);

SELECT *
FROM POSITION_QUERY t1 
 JOIN #temp t2
    on t1.STOCK_CODE = t2.STOCK_CODE
       and t1.STOCK_EXCHANGE = t2.EXCHANGE
 JOIN #temp t3
    on t1.INDEX_CODE = t3.STOCK_CODE
       and t1.INDEX_EXCHANGE = t3.EXCHANGE

This may lead to faster results, as it gives the execution planner little option but to use what you've provided, rather than attempt to work with the principal tables which may lead to sometimes expensive operations like spooling, hashing, or parallelisation.

Answer 3

Try this:

WITH DAILY_PRICE_TODAY (STOCK_CODE, EXCHANGE)  
AS  
-- Define the CTE query.  
(  
   SELECT STOCK_CODE, EXCHANGE 

   FROM DAILY_PRICE

   WHERE TRADE_DATE = 20181121
)  

SELECT * FROM 
POSITION_QUERY t1 
 JOIN DAILY_PRICE_TODAY t2
    on t1.STOCK_CODE = t2.STOCK_CODE
       and t1.STOCK_EXCHANGE = t2.EXCHANGE

 JOIN DAILY_PRICE_TODAY t3
    on t1.INDEX_CODE = t3.STOCK_CODE
       and t1.INDEX_EXCHANGE = t3.EXCHANGE