Tukukkk

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

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

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.

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.