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












1















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










share|improve this question

























  • Is that query correct (specifically the join criteria for t3)?

    – ZLK
    Nov 22 '18 at 3:30











  • @ZLK Thank you for pointing out. Obviously the original second join was wrong. I've already corrected it

    – tete
    Nov 22 '18 at 3:40











  • How many rows do POSITION_QUERY and DAILY_PRICE have?

    – e_i_pi
    Nov 22 '18 at 3:45











  • @e_i_pi , the POSITION_QUERY is a query which I already filtered with TRADE_DATE so the result is 50 rows. And on DAILY_PRICE, the total count on the day 20181121 is something around 15 thousounds rows. And as I mentioned I created indexes on almost all joined columns on the table so in teh actual execution plan there is actually no full table scan

    – tete
    Nov 22 '18 at 3:54


















1















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










share|improve this question

























  • Is that query correct (specifically the join criteria for t3)?

    – ZLK
    Nov 22 '18 at 3:30











  • @ZLK Thank you for pointing out. Obviously the original second join was wrong. I've already corrected it

    – tete
    Nov 22 '18 at 3:40











  • How many rows do POSITION_QUERY and DAILY_PRICE have?

    – e_i_pi
    Nov 22 '18 at 3:45











  • @e_i_pi , the POSITION_QUERY is a query which I already filtered with TRADE_DATE so the result is 50 rows. And on DAILY_PRICE, the total count on the day 20181121 is something around 15 thousounds rows. And as I mentioned I created indexes on almost all joined columns on the table so in teh actual execution plan there is actually no full table scan

    – tete
    Nov 22 '18 at 3:54
















1












1








1








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










share|improve this 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







sql-server sql-execution-plan sql-optimization






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 5:55







tete

















asked Nov 22 '18 at 3:21









tetetete

2,00293862




2,00293862













  • Is that query correct (specifically the join criteria for t3)?

    – ZLK
    Nov 22 '18 at 3:30











  • @ZLK Thank you for pointing out. Obviously the original second join was wrong. I've already corrected it

    – tete
    Nov 22 '18 at 3:40











  • How many rows do POSITION_QUERY and DAILY_PRICE have?

    – e_i_pi
    Nov 22 '18 at 3:45











  • @e_i_pi , the POSITION_QUERY is a query which I already filtered with TRADE_DATE so the result is 50 rows. And on DAILY_PRICE, the total count on the day 20181121 is something around 15 thousounds rows. And as I mentioned I created indexes on almost all joined columns on the table so in teh actual execution plan there is actually no full table scan

    – tete
    Nov 22 '18 at 3:54





















  • Is that query correct (specifically the join criteria for t3)?

    – ZLK
    Nov 22 '18 at 3:30











  • @ZLK Thank you for pointing out. Obviously the original second join was wrong. I've already corrected it

    – tete
    Nov 22 '18 at 3:40











  • How many rows do POSITION_QUERY and DAILY_PRICE have?

    – e_i_pi
    Nov 22 '18 at 3:45











  • @e_i_pi , the POSITION_QUERY is a query which I already filtered with TRADE_DATE so the result is 50 rows. And on DAILY_PRICE, the total count on the day 20181121 is something around 15 thousounds rows. And as I mentioned I created indexes on almost all joined columns on the table so in teh actual execution plan there is actually no full table scan

    – tete
    Nov 22 '18 at 3:54



















Is that query correct (specifically the join criteria for t3)?

– ZLK
Nov 22 '18 at 3:30





Is that query correct (specifically the join criteria for t3)?

– ZLK
Nov 22 '18 at 3:30













@ZLK Thank you for pointing out. Obviously the original second join was wrong. I've already corrected it

– tete
Nov 22 '18 at 3:40





@ZLK Thank you for pointing out. Obviously the original second join was wrong. I've already corrected it

– tete
Nov 22 '18 at 3:40













How many rows do POSITION_QUERY and DAILY_PRICE have?

– e_i_pi
Nov 22 '18 at 3:45





How many rows do POSITION_QUERY and DAILY_PRICE have?

– e_i_pi
Nov 22 '18 at 3:45













@e_i_pi , the POSITION_QUERY is a query which I already filtered with TRADE_DATE so the result is 50 rows. And on DAILY_PRICE, the total count on the day 20181121 is something around 15 thousounds rows. And as I mentioned I created indexes on almost all joined columns on the table so in teh actual execution plan there is actually no full table scan

– tete
Nov 22 '18 at 3:54







@e_i_pi , the POSITION_QUERY is a query which I already filtered with TRADE_DATE so the result is 50 rows. And on DAILY_PRICE, the total count on the day 20181121 is something around 15 thousounds rows. And as I mentioned I created indexes on almost all joined columns on the table so in teh actual execution plan there is actually no full table scan

– tete
Nov 22 '18 at 3:54














3 Answers
3






active

oldest

votes


















1














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





share|improve this 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.






    share|improve this answer


























    • Thank you for your post. As I mentioned the POSITION_QUERY is not an existing table, it is actually a CTE with a somehow complex query. So it is more like WITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I query SELECT * FROM POSITION_QEURY or SELECT * FROM POSITION_QUERY JOIN DAILY_PRICE ... (only one join), the queries run fast. But then again, I am really not an expert on query optimization so it is possible that POSITION_QUERY is the cause. Anyway, I've already pasted the actual execution plan and modified the original question

      – tete
      Nov 22 '18 at 5:52



















    0














    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.






    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423404%2fquery-slow-with-two-almost-the-same-join-but-faster-with-either-one%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      1














      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





      share|improve this answer




























        1














        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





        share|improve this answer


























          1












          1








          1







          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





          share|improve this answer













          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






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 22 '18 at 4:01









          tuanv2ttuanv2t

          997




          997

























              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.






              share|improve this answer


























              • Thank you for your post. As I mentioned the POSITION_QUERY is not an existing table, it is actually a CTE with a somehow complex query. So it is more like WITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I query SELECT * FROM POSITION_QEURY or SELECT * FROM POSITION_QUERY JOIN DAILY_PRICE ... (only one join), the queries run fast. But then again, I am really not an expert on query optimization so it is possible that POSITION_QUERY is the cause. Anyway, I've already pasted the actual execution plan and modified the original question

                – tete
                Nov 22 '18 at 5:52
















              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.






              share|improve this answer


























              • Thank you for your post. As I mentioned the POSITION_QUERY is not an existing table, it is actually a CTE with a somehow complex query. So it is more like WITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I query SELECT * FROM POSITION_QEURY or SELECT * FROM POSITION_QUERY JOIN DAILY_PRICE ... (only one join), the queries run fast. But then again, I am really not an expert on query optimization so it is possible that POSITION_QUERY is the cause. Anyway, I've already pasted the actual execution plan and modified the original question

                – tete
                Nov 22 '18 at 5:52














              1












              1








              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.






              share|improve this answer















              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.







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Nov 22 '18 at 5:23

























              answered Nov 22 '18 at 5:17









              Max SzczurekMax Szczurek

              3,06011123




              3,06011123













              • Thank you for your post. As I mentioned the POSITION_QUERY is not an existing table, it is actually a CTE with a somehow complex query. So it is more like WITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I query SELECT * FROM POSITION_QEURY or SELECT * FROM POSITION_QUERY JOIN DAILY_PRICE ... (only one join), the queries run fast. But then again, I am really not an expert on query optimization so it is possible that POSITION_QUERY is the cause. Anyway, I've already pasted the actual execution plan and modified the original question

                – tete
                Nov 22 '18 at 5:52



















              • Thank you for your post. As I mentioned the POSITION_QUERY is not an existing table, it is actually a CTE with a somehow complex query. So it is more like WITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I query SELECT * FROM POSITION_QEURY or SELECT * FROM POSITION_QUERY JOIN DAILY_PRICE ... (only one join), the queries run fast. But then again, I am really not an expert on query optimization so it is possible that POSITION_QUERY is the cause. Anyway, I've already pasted the actual execution plan and modified the original question

                – tete
                Nov 22 '18 at 5:52

















              Thank you for your post. As I mentioned the POSITION_QUERY is not an existing table, it is actually a CTE with a somehow complex query. So it is more like WITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I query SELECT * FROM POSITION_QEURY or SELECT * FROM POSITION_QUERY JOIN DAILY_PRICE ... (only one join), the queries run fast. But then again, I am really not an expert on query optimization so it is possible that POSITION_QUERY is the cause. Anyway, I've already pasted the actual execution plan and modified the original question

              – tete
              Nov 22 '18 at 5:52





              Thank you for your post. As I mentioned the POSITION_QUERY is not an existing table, it is actually a CTE with a somehow complex query. So it is more like WITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I query SELECT * FROM POSITION_QEURY or SELECT * FROM POSITION_QUERY JOIN DAILY_PRICE ... (only one join), the queries run fast. But then again, I am really not an expert on query optimization so it is possible that POSITION_QUERY is the cause. Anyway, I've already pasted the actual execution plan and modified the original question

              – tete
              Nov 22 '18 at 5:52











              0














              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.






              share|improve this answer




























                0














                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.






                share|improve this answer


























                  0












                  0








                  0







                  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.






                  share|improve this answer













                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 22 '18 at 4:01









                  e_i_pie_i_pi

                  2,56021632




                  2,56021632






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423404%2fquery-slow-with-two-almost-the-same-join-but-faster-with-either-one%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      404 Error Contact Form 7 ajax form submitting

                      How to know if a Active Directory user can login interactively

                      TypeError: fit_transform() missing 1 required positional argument: 'X'