Mixing
Query slow with two (almost the same) join but faster with either one
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 asked Nov 22 '18 at 3:21
tetetete
2,00793862
add a comment |
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 asked Nov 22 '18 at 3:21
tetetete
2,00793862
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 doPOSITION_QUERYandDAILY_PRICEhave?
– e_i_pi
Nov 22 '18 at 3:45
@e_i_pi , thePOSITION_QUERYis a query which I already filtered withTRADE_DATEso the result is 50 rows. And onDAILY_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
add a comment |
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 asked Nov 22 '18 at 3:21
tetetete
2,00793862
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 sql-server sql-execution-plan sql-optimization asked Nov 22 '18 at 3:21
tetetete
2,00793862
asked Nov 22 '18 at 3:21
tetetete
2,00793862
edited Nov 22 '18 at 5:55
tete
asked Nov 22 '18 at 3:21
tetetete
2,00793862
asked Nov 22 '18 at 3:21
tetetete
2,00793862
asked Nov 22 '18 at 3:21
tetetete
2,00793862
2,00793862
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 doPOSITION_QUERYandDAILY_PRICEhave?
– e_i_pi
Nov 22 '18 at 3:45
@e_i_pi , thePOSITION_QUERYis a query which I already filtered withTRADE_DATEso the result is 50 rows. And onDAILY_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
add a comment |
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 doPOSITION_QUERYandDAILY_PRICEhave?
– e_i_pi
Nov 22 '18 at 3:45
@e_i_pi , thePOSITION_QUERYis a query which I already filtered withTRADE_DATEso the result is 50 rows. And onDAILY_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
add a comment |
3 Answers
3
active
oldest
votes
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
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
add a comment |
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.
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
Thank you for your post. As I mentioned thePOSITION_QUERYis not an existing table, it is actually a CTE with a somehow complex query. So it is more likeWITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I querySELECT * FROM POSITION_QEURYorSELECT * 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 thatPOSITION_QUERYis the cause. Anyway, I've already pasted the actual execution plan and modified the original question
– tete
Nov 22 '18 at 5:52
add a comment |
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.
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
add a comment |
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
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
add a comment |
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
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
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
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
answered Nov 22 '18 at 4:01
tuanv2ttuanv2t
997
997
add a comment |
add a comment |
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.
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
Thank you for your post. As I mentioned thePOSITION_QUERYis not an existing table, it is actually a CTE with a somehow complex query. So it is more likeWITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I querySELECT * FROM POSITION_QEURYorSELECT * 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 thatPOSITION_QUERYis the cause. Anyway, I've already pasted the actual execution plan and modified the original question
– tete
Nov 22 '18 at 5:52
add a comment |
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.
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
Thank you for your post. As I mentioned thePOSITION_QUERYis not an existing table, it is actually a CTE with a somehow complex query. So it is more likeWITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I querySELECT * FROM POSITION_QEURYorSELECT * 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 thatPOSITION_QUERYis the cause. Anyway, I've already pasted the actual execution plan and modified the original question
– tete
Nov 22 '18 at 5:52
add a comment |
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.
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
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.
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
edited Nov 22 '18 at 5:23
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
answered Nov 22 '18 at 5:17
Max SzczurekMax Szczurek
3,11511224
3,11511224
Thank you for your post. As I mentioned thePOSITION_QUERYis not an existing table, it is actually a CTE with a somehow complex query. So it is more likeWITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I querySELECT * FROM POSITION_QEURYorSELECT * 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 thatPOSITION_QUERYis the cause. Anyway, I've already pasted the actual execution plan and modified the original question
– tete
Nov 22 '18 at 5:52
add a comment |
Thank you for your post. As I mentioned thePOSITION_QUERYis not an existing table, it is actually a CTE with a somehow complex query. So it is more likeWITH POSITION_QUERY AS (SELECT * FROM ...)I didn't think of it as a problem because as I mentioned if I querySELECT * FROM POSITION_QEURYorSELECT * 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 thatPOSITION_QUERYis 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
add a comment |
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.
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
add a comment |
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.
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
add a comment |
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.
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
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.
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
answered Nov 22 '18 at 4:01
e_i_pie_i_pi
2,67921732
2,67921732
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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_QUERYandDAILY_PRICEhave?– e_i_pi
Nov 22 '18 at 3:45
@e_i_pi , the
POSITION_QUERYis a query which I already filtered withTRADE_DATEso the result is 50 rows. And onDAILY_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